Power Query Source from Relative Paths

[Magnus Vegem Dahle]

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

[#2 · Accepted 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>

[#3]

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

[#4]

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!

[#5]

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

[#6]

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!

[#7]

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

[#8]

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>

[#9]

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!

[#10]

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.

[#11]

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

[#12]

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!

[#13]

@saadiriaz 

Which exactly steps do you do?

[#14]

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

[#15]

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

[#16]

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

[#17]

@Sergei Baklan 

I have a slightly different scenario. I have an MS Query connection in file1 to get data from file2.xls which is in a different folder to file1. I have used the code to find the relative location of file1 (i.e. =LEFT(CELL("filename",$A$1),FIND("[",CELL("filename",$A$1),1)-1)) but to access file2 I need to back up before entering folder2 etc. Any idea how I can make that? The other approach I could think of is to get the relative reference to 'folder' meaning I can point to both file1 and file2 from the same reference

folder\folder1\file1.xls
folder\folder2\folder2.1\folder2.1.1\file2.xls

[#18]

@tfmeier2235 

Looks like I'm working on something similar.  Have a look at this snippet and see if it gives you any ideas.  The gist here is I split the text, delete some bottom items, and combine it again.

let
    FilePath = Excel.CurrentWorkbook(){[Name="FilePath"]}[Content]{0}[Column1],
    #"Split Text" = Text.Split(FilePath, "\"),
    #"Removed Bottom Items" = List.RemoveLastN(#"Split Text",3),
    #"Combine Text" =  Text.Combine(#"Removed Bottom Items","\"),
    FullPath = #"Combine Text" & "\Projects.xlsm",
    Source = Excel.Workbook(File.Contents(FullPath), null, true),
    Projects_Sheet = Source{[Item="Projects",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Projects_Sheet, [PromoteAllScalars=true])
in
    #"Promoted Headers"

[#19]

@WhateverName 

So I'm really struggling with what I believe is a similar issue, but cant to get your snippet to work for me. Any pointers gratefully received!  This file will be downloaded by multiple users to a predefined location in a folder tree, but the top level file path will change for two reasons

1. The user name and

2. The Scheme name the file lives in. 

So... The Data I want to query lives in:

C:\Users\USER_Name\CDE_NAME\ORG_NAME\SCHEME_NAME\01.0 Project Standards\05.0 Information Management\96.0 System Configuration\CS614.xlsx

The file that I want to query then display the data lives in:

C:\Users\USER_Name\CDE_NAME\ORG_NAME\SCHEME_NAME\06.0 Design\03.0 The Delivery Team \15.0 Design - Civils\06.0 DOC

So in my head I need Power query identify the file path in use for itself,  truncate the file path after 

C:\Users\USER_White\CDE_NAME\ORG_NAME\SCHEME_NAME and then append that truncated text with the "\01.0 Project Standards\05.0 Information Management\96.0 System Configuration\CS614.xlsx" 

Can anyone give me any pointers as this is blowing my tiny brain!

[#20]

Hi

I am new to power query , and I want to convert my absolute path to relative path,

@Sergei Baklan I used the same code and did not get any errors but,

It was giving me the null value as output

I gave cell A1 as the name WBPath and this is the code I am using

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