Excel power query folder path

Question

I’m really new to excel power queries, but finding it really useful.

My question is this, when I have created a table to pull data from 20 or so worksheets in a folder. My source is

Source = Folder.Files{“\\myserver\folders\stuff\data WC 06.07.2020”)

I have no control over naming this data folder and the name keeps getting changed, so obviously it throws up an error each week and I have to manually go in an change the query.

Is there a way for the date part to be ignored and it still pull from the correct folder?

Thanks if you can help

horseyride horseyride · Accepted Answer · 2020-07-09T17:20:31

I'd just use a variable, and put the path name there

Give cell a range name, like DirVariable in excel and put your location in there, like

\\myserver\folders\stuff\data WC 06.07.2020\

Then change the powerquery code to use that rangename

Source = Folder.Files(Excel.CurrentWorkbook(){[Name="DirVariable"]}[Content]{0}[Column1])

alternately, put just the date in the variable

06.07.2020

and have the code pre-append the path

Source = Folder.Files("\\myserver\folders\stuff\data WC "&Excel.CurrentWorkbook(){[Name="DirVariable"]}[Content]{0}[Column1])

If you want, you can attempt to look for most recently modified excel file within any path starting with \\myserver\folders\stuff, and then grab the directory associated with that file, and use that. It would be something like

let directory = "\\myserver\folders\stuff\",
Source=Folder.Files(directory),
#"Filtered Rows" = Table.SelectRows(Source, each [Folder Path] <> directory and Text.Start([Extension],4)=".xls"),
#"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Date modified", Order.Descending}}),
MostRecent=  #"Sorted Rows"{0}[#"Folder Path"],
Source2 =Folder.Files(MostRecent)
in Source2

Excel power query folder path

1 Answers