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.

Power Query transforms the files producing a usable query

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

The current Source of the folder

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

Explanation of the technique begins at 00:01:39

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.

You should have two cells returning something like the above
Name Manager showing the two named ranges

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.

The Query Editor code we need

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.

It’s the same output however, without the interruption of making changes in the Query Editor

One thought on “Create a Dynamic Filepath for Power Query Connections”

Leave a Reply

Your email address will not be published. Required fields are marked *