SOLVED

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
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

15 Replies
best response confirmed by Magnus Vegem Dahle (New Contributor)
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>

 

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

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!

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

 

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!

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

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>

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!

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.

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),

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!

 

@saadiriaz 

Which exactly steps do you do?

I am doing something similar but I want to do a folder.files like action and see all the files in the subfolders of the folder this excel document is in. My files are on one drive and when I do this workaround it gives me errors. Any help would be awesome
Hello Sergei,
Thank you it's very useful for local files but how to make it work with a one drive folder?
the CELL fomula gives an html path.

Thank you very much

@Clemleb 

I guess we speak about the files synced with OneDrive. CELL("filename") returns in this case URL. To query the files you may use

= SharePoint.Files("https://tenant-my.sharepoint.com/personal/account", [ApiVersion = 15])

which returns the list of files with FilePath column. Parse what is returned by CELL() and filter on that above query.