CODEWITHSUNDEEP
excelexcel-formuladirectorypathpowerquery
Dan Py
Dan Py

Reputation: 27

Excel getting current directory in Power Query

I have been trying to get current directory into Power Query. But it somehow doesn't work. How can I get current directory, to make the path dynamic for my Query, so that in case the file moved a new directory, I will have no issue with retrieving data into Power Query. Here is the code I tried with:

let

Source = Excel.CurrentWorkbook(){[Name="pathTable"]}[Content],

#"Changed Type" = Table.TransformColumnTypes(Source,{{"Path", type text}}),

Path = Table.ReplaceValue(#"Changed Type","\[PathForSummaryFiles.xlsx]Path","\summary.xlsx",Replacer.ReplaceText,{"Path"})

GetFilesFromFolder = Folder.Files(Path)

in

GetFilesFromFolder

the code above throws an error.

Upvotes: 1

Views: 3469

Answers (1)

horseyride
horseyride

Reputation: 21318

Is this what you are trying to do?

In excel, name a cell DirectoryRangeName using formulas ... name manager

Within that cell, put in a formula to capture the path of that file

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

or enter your own path such as:

c:\directory\subdirectory\

Then, once in powerquery, read that value, and combine however you want to reference it such as

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

Or

let Directory =  Excel.CurrentWorkbook(){[Name="DirectoryRangeName"]}[Content]{0}[Column1],
GetFilesFromFolder = Folder.Files(Directory)
in GetFilesFromFolder

Instead of a formula in the range name, you could simply put a full filepath such as c:\temp\a.xlsx

and then read it within powerquery

let FilePath=  Excel.CurrentWorkbook(){[Name="DirectoryRangeName"]}[Content]{0}[Column1],
Source = Excel.Workbook(File.Contents(FilePath), null, true)
in  Source

Upvotes: 2

Related Questions