CODEWITHSUNDEEP
excelpowerquery
dbardakov
dbardakov

Reputation: 699

Relative folder/file path in PowerQuery (M)

Given: 2 excel files (A and B) that are located in the same folder.

Task: set Source for a given PQ in Workbook B to reference Workbook A in a "relative" manner - when both files are moved to another PC the reference isn't broken.

Is there anything in M-language to get current folder or file path?

Upvotes: 1

Views: 6891

Answers (2)

oleg evtukh
oleg evtukh

Reputation: 41

You need to give a name 'Path' to some cell where current path is defined

=LEFT(CELL("filename");SEARCH("[";CELL("filename");1)-1)

Then use following in the query:

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

One big disadvantage of the approach - is that you need to Calculate your workbook after localtion is changed or set Calculation mode to Automatic.

If someone know how can we do not use cell for getting current path - it will solve a problem with modifying file and Calculation mode.

Upvotes: 4

horseyride
horseyride

Reputation: 21318

Give an excel cell a range name, like LocVariable, and put some text or excel formula in that cell to evaluate to the filepath you want, such as c:\temp

Then in powerquery, in home ... advanced editor ... edit your Source statement to use that path

let Loc = Text.From(Excel.CurrentWorkbook(){[Name="LocVariable"]}[Content]{0}[Column1]),
Source = Excel.Workbook(File.Contents(Loc&"\data.xlsx"), null, true),

or in one step

let Source = Excel.Workbook(File.Contents(Text.From(Excel.CurrentWorkbook(){[Name="LocVariable"]}[Content]{0}[Column1])&"\data.xlsx"), null, true),

Upvotes: 1

Related Questions