Create a Dynamic Filepath for Power Query Connections

In this post, I am going to discuss an option for creating a dynamic filepath for use as a Source in an Excel to Power Query connection.

Why did I need this?

One of the first Excel projects that I created was a reconciliation report for our postage charge-backs. To reduce the amount of data entry that was previously done, we began collecting our invoice information electronically from the carriers. I could have chosen to Get Data from Folder for all carriers however, it only really made sense to use this method for our USPS® daily transactions.

The Goal

In the Get Data from Folder process the source is defined as Source = Folder.Files("C:\Users\username\Desktop\dynamic-filepath") as an example. The point being, this is a hard-coded value. It travels with the file, and since this needs to be a shared file, the connection becomes broken for any other user.

I will show how you how I created a dynamic Source so no one has to go into the Advanced Editor to fix the filepath.

The Scenario

We have an Excel file, 01-dynamic-filepath.xlsx, that is getting data from a folder (dynamic-filepath) which contains two simple files.

One of the first steps was a definition of the Source.

Learn how to Combine files in a folder

support.office.com documents the fundamentals at https://support.office.com/en-us/article/combine-files-in-a-folder-with-combine-binaries-power-query.

Power Query Sample File File as Parameter

The technique I used is described in the ExcelIsFun YouTube Channel. You don’t need to watch the entire video as we only need a small excerpt (from around 00:01:39 – 00:02:36).

Here is Excel formula used in the video to get the dynamic filepath.

=SUBSTITUTE(LEFT(CELL("filename",A1),SEARCH("]",CELL("filename",A1))-1),"[","")

Since we require Get Data from Folder we can modify the formula as.

=SUBSTITUTE(FilePath,RIGHT(FilePath,LEN(FilePath)-FIND("@",SUBSTITUTE(FilePath,"\","@",LEN(FilePath)-LEN(SUBSTITUTE(FilePath,"\",""))),1)),"dynamic-filepath")

Notice that we did hard-code “dynamic-filepath” as the folder. Maybe we can address that in a different post.

We are really only concerned with the Named Range = FilePathFolder at this point since this value is the Source for Get Data from Folder. We can follow the video up to 02:36:00 mark to get the last piece of code.

All that work to get = Excel.CurrentWorkbook(){[Name="FilePathFolder"]}[Content]{0}[Column1].

Replacing Source in the Query Editor

Change the code of the ‘Sample File’ with:

let
    Source = Folder.Files(Excel.CurrentWorkbook(){[Name="FilePathFolder"]}[Content]{0}[Column1]),
    Navigation1 = Source{0}[Content]
in
    Navigation1

.

Change the code of the ‘dynamic-filepath’ with:

let
    Source = Folder.Files(Excel.CurrentWorkbook(){[Name="FilePathFolder"]}[Content]{0}[Column1]),
    #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File from dynamic-filepath", each #"Transform File from dynamic-filepath"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File from dynamic-filepath"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from dynamic-filepath", Table.ColumnNames(#"Transform File from dynamic-filepath"(#"Sample File"))),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Trans", Int64.Type}, {"Amount", type number}})
in
    #"Changed Type"

The Result

While using Box Drive, whoever opens the file now has a dynamic connection. There is no need to modify the query source as long as the file and folder are in the same directory.