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
Magnus Vegem Dahle
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

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

 

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

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies