SOLVED
Home

Power Query Source from Relative Paths

%3CLINGO-SUB%20id%3D%22lingo-sub-206150%22%20slang%3D%22en-US%22%3EPower%20Query%20Source%20from%20Relative%20Paths%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-206150%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20All%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%26nbsp%3Bmaking%20an%20analysis%20tool%20that%20uses%20two%20Excel%20file%20sources%20via%20Power%20Query.%20The%20tool%20work%20well%2C%20but%20as%20soon%20as%20the%20file%20and%20source%20files%20are%20moved%20to%20another%20location%20it%20stops%20working%20as%20the%20Queries%20sources%20from%20aboslute%20paths.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20I%20modify%20my%20Sourcing%20so%20that%26nbsp%3Bas%20long%20as%20the%20Analysis%20tool%20file%20and%20the%20source%20files%20are%20in%20the%20same%20folder%20the%20Query%20will%20find%20and%20use%20the%20correct%20files%3F%20So%20that%20the%20Query%20sources%20from%20a%20path%20relative%20to%20the%20Analysis%20Tool%20file.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks!%3C%2FP%3E%3CP%3EMagnus%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-206150%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EGet%20%26amp%3B%20Transform%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20Query%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-357606%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20Source%20from%20Relative%20Paths%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-357606%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Sergei.%3C%2FP%3E%3CP%3EThx%20this%20works%20for%20me%20aswell...%3C%2FP%3E%3CP%3EAn%20amendment%20would%20be%20to%20get%20files%20from%20a%20relative%20folder%20location%20instead%20of%20a%20specific%20file.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20how%20I%20did%20this%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Elet%3CBR%20%2F%3EWBPath%20%3D%20Excel.CurrentWorkbook()%7B%5BName%3D%22WBPath%22%5D%7D%5BContent%5D%7B0%7D%5BColumn1%5D%2C%3CBR%20%2F%3EFullPathToFile1%20%3D%20WBPath%20%26amp%3B%20%22subfolder%5Csubfolder2%5C%22%2C%3CBR%20%2F%3ESource%20%3D%20Folder.Files(FullPathToFile1)%2C%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-233741%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20Source%20from%20Relative%20Paths%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-233741%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Magnus%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECorrect%2C%20M%20script%20(PQ%20language)%20is%20case%20sensitive.%20%22Let%22%20and%20%22let%22%20are%20different%20objects%20(first%20one%20doesn't%20exist)%2C%20as%20well%20as%20any%20other%20ones.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-233731%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20Source%20from%20Relative%20Paths%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-233731%22%20slang%3D%22en-US%22%3E%3CP%3EGAAAAAAAAA!!!!%20You're%20right%2C%20Magnus.%20It%20worked!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20copied%20your%20code%20the%20first%20time%20and%20edited%20it%20without%20noticing%20the%20upper%20case%20%22Let%22.%20Can%20you%20still%20edit%20your%20post%20to%20fix%20it%20after%20all%20that%20time%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20guys!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-233540%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20Source%20from%20Relative%20Paths%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-233540%22%20slang%3D%22en-US%22%3E%3CP%3EI%20ahd%20some%20of%20the%20same%20problem%2C%20for%20me%2C%20the%20error%20was%20the%20%22Let%22%20in%20the%20start%20of%20the%20code%20was%20with%20a%20Capital%20letter.%20When%20I%20switched%20to%20all%20lowercase%20letters%20it%20worked%20fine.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20it%20should%20maybe%20be%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3E%3CSTRONG%3El%3C%2FSTRONG%3Eet%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EWBPath%20%3D%20Excel.CurrentWorkbook()%7B%5BName%3D%22WBPath%22%5D%7D%5BContent%5D%7B0%7D%5BColumn1%5D%2C%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EFullPathToFile1%20%3D%20WBPath%20%26amp%3B%20%22file.xlsx%22%2C%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3ESource%20%3D%20Excel.Workbook(File.Contents(FullPathToFile1)%2C%20null%2C%20true)%2C%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3E%3CNEXT%20step%3D%22%22%3E%3C%2FNEXT%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-230941%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20Source%20from%20Relative%20Paths%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-230941%22%20slang%3D%22en-US%22%3E%3CP%3EI%20don't%20think%20something%20was%20significantly%20changed%20during%20past%20months%2C%20but%20in%20any%20case%20better%20to%20keep%20PQ%20updated.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-230742%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20Source%20from%20Relative%20Paths%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-230742%22%20slang%3D%22en-US%22%3E%3CP%3EThat's%20really%20weird!%20Could%20it%20be%20that%20mine%20is%20Excel%202013%20and%20Power%20Query%20hasn't%20been%20updated%20since%20March%202018%3F%20Or%20should%20this%20work%20regardless%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnd%20thanks%20a%20lot%20for%20responding%2C%20Sergei!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-230392%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20Source%20from%20Relative%20Paths%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-230392%22%20slang%3D%22en-US%22%3E%3CP%3EI%20copy%2Fpasted%20your%20code%20into%20PQ%3C%2FP%3E%0A%3CPRE%3Elet%0A%20%20%20%20WBPath%20%3D%20Excel.CurrentWorkbook()%7B%5BName%3D%22WBPath%22%5D%7D%5BContent%5D%7B0%7D%5BColumn1%5D%2C%0A%20%20%20%20FullPathToFile1%20%3D%20WBPath%20%26amp%3B%20%22file.xlsx%22%2C%0A%20%20%20%20Source%20%3D%20Excel.Workbook(File.Contents(FullPathToFile1)%2C%20null%2C%20true)%0Ain%0A%20%20%20%20Source%3C%2FPRE%3E%0A%3CP%3EThere%20are%20no%20errors%20at%20all%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-229538%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20Source%20from%20Relative%20Paths%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-229538%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20All%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20the%20Power%20Query%20add-in%20on%20Excel%202013%20and%20Windows%207.%20I%20tried%20the%20following%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20named%20cell%26nbsp%3BA1%20of%20the%20workbook%20%22WBPath%22%20and%20entered%20this%20formula%20in%20it%3A%3CBR%20%2F%3EG%3A%5CIbrahim%20Omar%5CFlooring%5CSales%20Analysis%5C%3CBR%20%2F%3EAnd%20with%20this%2C%20the%20cell%20A1%20now%20shows%20the%20path%20of%20the%20workbook%20correctly.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThen%20I%20started%20my%20query%20with%20the%20following%20code%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELet%3CBR%20%2F%3EWBPath%20%3D%20Excel.CurrentWorkbook()%7B%5BName%3D%22WBPath%22%5D%7D%5BContent%5D%7B0%7D%5BColumn1%5D%2C%3CBR%20%2F%3EFullPathToFile1%20%3D%20WBPath%20%26amp%3B%20%22file.xlsx%22%2C%3CBR%20%2F%3ESource%20%3D%20Excel.Workbook(File.Contents(FullPathToFile1)%2C%20null%2C%20true)%2C%3CBR%20%2F%3E%3CNEXT%20step%3D%22%22%3E%3C%2FNEXT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20immediately%20and%20without%20even%20closing%20the%20editor%20got%20this%26nbsp%3B%26nbsp%3Berror%20below%3A%3CBR%20%2F%3E%22Token%20Eof%20expected.%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20I%20clicked%20show%20error%2C%20it%20highlighted%20the%20first%20instance%20of%20%22WBPath%22%20in%20my%20code.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%2C%20what%20is%20this%20error%3F%20And%20how%20can%20I%20fix%20it%3F%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EThanks%20a%20lot!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-206503%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20Source%20from%20Relative%20Paths%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-206503%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20really%20helped.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20other%20beginners%20like%20me%2C%20this%20was%20my%20end%20result%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Elet%3C%2FP%3E%3CP%3EFilePath%20%3D%20Excel.CurrentWorkbook()%7B%5BName%3D%22FilePath%22%5D%7D%5BContent%5D%7B0%7D%5BColumn1%5D%2C%3CBR%20%2F%3EFullPathToFile1%20%3D%20FilePath%20%26amp%3B%20%22Name1.xlsx%22%2C%3CBR%20%2F%3ESource%20%3D%20Excel.Workbook(File.Contents(FullPathToFile1)%2C%20null%2C%20true)%3C%2FP%3E%3CP%3Ein%3C%2FP%3E%3CP%3ESource%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-206267%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20Source%20from%20Relative%20Paths%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-206267%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Magnus%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20may%20find%20the%20current%20file%20path%20putting%20in%20any%20cell%20of%20your%20workbook%20the%20formula%3C%2FP%3E%0A%3CPRE%3E%3DLEFT(CELL(%22filename%22%2C%24A%241)%2CFIND(%22%5B%22%2CCELL(%22filename%22%2C%24A%241)%2C1)-1)%3C%2FPRE%3E%0A%3CP%3Ename%20this%20cell%20somehow%2C%20let%20say%20%22FilePath%22%20and%20generate%20absolute%20path%20in%20query%20like%3C%2FP%3E%0A%3CPRE%3E%26lt%3Bprev%20step%26gt%3B%0A%20%20%20%20FilePath%20%3D%20Excel.CurrentWorkbook()%7B%5BName%3D%22FilePath%22%5D%7D%5BContent%5D%7B0%7D%5BColumn1%5D%2C%0A%20%20%20%20FullPathToFile1%20%3D%20FilePath%20%26amp%3B%20%22Name1.xlsx%22%0A%26lt%3Bnext%20step%26gt%3B%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1404287%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20Source%20from%20Relative%20Paths%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1404287%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20All%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20source%20doesn't%20end%20with%20null%20and%20true.%20Rather%20it%20looks%20like%20the%20below.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3ESource%20%3D%20Csv.Document(File.Contents(%22E%3A%5CPQ%5Ctesting%5Chome.txt%22)%2C%5BDelimiter%3D%22%20%22%2C%20Columns%3D36%2C%20Encoding%3D1252%2C%20QuoteStyle%3DQuoteStyle.None%5D)%2C%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20I%20do%20all%20the%20steps%20mentioned%20above%2C%20I%20get%20an%20error%20%224%20arguments%20were%20passed%20to%20a%20function%20which%20expects%20between%201%20and%203.%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWould%20appreciate%20the%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1410052%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20Source%20from%20Relative%20Paths%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1410052%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F673448%22%20target%3D%22_blank%22%3E%40saadiriaz%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWhich%20exactly%20steps%20do%20you%20do%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hi All,

 

I am making an analysis tool that uses two Excel file sources via Power Query. The tool work well, but as soon as the file and source files are moved to another location it stops working as the Queries sources from aboslute paths.

 

Can I modify my Sourcing so that as long as the Analysis tool file and the source files are in the same folder the Query will find and use the correct files? So that the Query sources from a path relative to the Analysis Tool file.

 

Thanks!

Magnus

12 Replies
Highlighted
Solution

Hi Magnus,

 

You may find the current file path putting in any cell of your workbook the formula

=LEFT(CELL("filename",$A$1),FIND("[",CELL("filename",$A$1),1)-1)

name this cell somehow, let say "FilePath" and generate absolute path in query like

<prev step>
    FilePath = Excel.CurrentWorkbook(){[Name="FilePath"]}[Content]{0}[Column1],
    FullPathToFile1 = FilePath & "Name1.xlsx"
<next step>

 

Highlighted

Thanks,

 

This really helped.

 

For other beginners like me, this was my end result:

 

let

FilePath = Excel.CurrentWorkbook(){[Name="FilePath"]}[Content]{0}[Column1],
FullPathToFile1 = FilePath & "Name1.xlsx",
Source = Excel.Workbook(File.Contents(FullPathToFile1), null, true)

in

Source

Highlighted

Hi All,

 

I have the Power Query add-in on Excel 2013 and Windows 7. I tried the following:

 

I named cell A1 of the workbook "WBPath" and entered this formula in it:
G:\Ibrahim Omar\Flooring\Sales Analysis\
And with this, the cell A1 now shows the path of the workbook correctly.

 

Then I started my query with the following code:

 

Let
WBPath = Excel.CurrentWorkbook(){[Name="WBPath"]}[Content]{0}[Column1],
FullPathToFile1 = WBPath & "file.xlsx",
Source = Excel.Workbook(File.Contents(FullPathToFile1), null, true),
<next step>

 

I immediately and without even closing the editor got this  error below:
"Token Eof expected."

 

When I clicked show error, it highlighted the first instance of "WBPath" in my code.

 

So, what is this error? And how can I fix it?


Thanks a lot!

Highlighted

I copy/pasted your code into PQ

let
    WBPath = Excel.CurrentWorkbook(){[Name="WBPath"]}[Content]{0}[Column1],
    FullPathToFile1 = WBPath & "file.xlsx",
    Source = Excel.Workbook(File.Contents(FullPathToFile1), null, true)
in
    Source

There are no errors at all

 

Highlighted

That's really weird! Could it be that mine is Excel 2013 and Power Query hasn't been updated since March 2018? Or should this work regardless?

 

And thanks a lot for responding, Sergei!

Highlighted

I don't think something was significantly changed during past months, but in any case better to keep PQ updated.

Highlighted

I ahd some of the same problem, for me, the error was the "Let" in the start of the code was with a Capital letter. When I switched to all lowercase letters it worked fine.

 

So it should maybe be:

 

let
WBPath = Excel.CurrentWorkbook(){[Name="WBPath"]}[Content]{0}[Column1],
FullPathToFile1 = WBPath & "file.xlsx",
Source = Excel.Workbook(File.Contents(FullPathToFile1), null, true),
<next step>

Highlighted

GAAAAAAAAA!!!! You're right, Magnus. It worked!

 

I copied your code the first time and edited it without noticing the upper case "Let". Can you still edit your post to fix it after all that time?

 

Thanks guys!

Highlighted

Hi Magnus,

 

Correct, M script (PQ language) is case sensitive. "Let" and "let" are different objects (first one doesn't exist), as well as any other ones.

Highlighted

Hi Sergei.

Thx this works for me aswell...

An amendment would be to get files from a relative folder location instead of a specific file.

 

This is how I did this

 

let
WBPath = Excel.CurrentWorkbook(){[Name="WBPath"]}[Content]{0}[Column1],
FullPathToFile1 = WBPath & "subfolder\subfolder2\",
Source = Folder.Files(FullPathToFile1),

Highlighted

Hi All

 

My source doesn't end with null and true. Rather it looks like the below.

 

Source = Csv.Document(File.Contents("E:\PQ\testing\home.txt"),[Delimiter="	", Columns=36, Encoding=1252, QuoteStyle=QuoteStyle.None]),

 

When I do all the steps mentioned above, I get an error "4 arguments were passed to a function which expects between 1 and 3."

 

Would appreciate the help.

 

Thanks!

 

Highlighted

@saadiriaz 

Which exactly steps do you do?