Create Dynamic File Path in Power Query

When you walk down a few months in the Power Query lane, you’d feel if only there was a way to make the File Path (i.e. Source Step) in Power Query Dynamic, your queries would be so much better if their file source is dynamic.

The answer.. YES there is way!

In this post I’ll share, how can you create a dynamic folder or a file path, both in Excel Power Query and in Power BI.

 

Grab the Video first?

 

Dynamic Folder Path in Excel Power Query

Quick heads-up, this technique is meant for gathering data from files or folders in your computer. It’ll come handy when you share your Query (Excel / Power BI file) and the source data with others, the file path will update automatically in their computer too.

 

Consider I have a “Files” Folder on my Desktop with 3 Excel files to be combined into a Single one.

I’d like the folder path to be dynamic, so when I change the folder location my query should update the new path automatically.

 

Step 1 – I add a Blank Excel File to the Folder (you can name it anything).

Note – It is essential to keep our working file (Consolidate All Data) in the same folder location as the other files.

 

Step 2 – I write a quick formula in our working file (Consolidate All Data) to retrieve the current file path. And this formula would update automatically when the folder location is changed 😎

=LEFT(CELL('filename'),SEARCH('\[',CELL('filename')))

Confirming the above formula returns the correct folder path

 

Step 3 – Now I need to do a couple of things before I work in Power Query.

• Convert this into table, use Ctrl+T.
• Column header as “Path” and table name “DynamicPath”

Our table has only one row and one column and it should like this..

 

Step 4 – Take this “DynamicPath” Table to Power Query. From the Data Tab >> From Table Range.

• Right click on 1st Row and Select Drill Down
• Doing this will convert the path into a Text (Variable)

Once you do that, the query should look like this..

 

Step 5 – Open the Advanced Editor from the View Tab and let’s work further on this.

1. After you drilled down, the second step is named as “Path”
2. I’ll add a simple one line M-code in the advanced editor

let
  Source = Excel.CurrentWorkbook(){[Name = 'DynamicPath']}[Content],
  Path = Source{0}[Path],
  GetFolderFiles = Folder.Files(Path)
in
  GetFolderFiles

The highlighted code is one that I have added. Appears like this..

 

Result – After you commit to the above code, the results seem perfect. Just remember to filter out our working file (Consolidate All Data) from list, else you’ll have data duplication.

Now when you change the path for the “Files” Folder the query won’t break.

 

Dynamic File Path in Excel Power Query

You may have the need to create a Dynamic File path rather than a Folder path. In that case we can modify on the same query to get that done.

In the Advanced Editor I create modified code.

let
  Source = Excel.CurrentWorkbook(){[Name = 'DynamicPath']}[Content],
  Path = Source{0}[Path] ,
  GetFiles = Excel.Workbook(File.Contents(Path & 'Year - 2005.xlsx'))
in
  GetFiles

If you modify the old code with the highlighted bit, you’d rather pick up a single file from that folder i.e. Year 2005 Excel Workbook instead of all files in that folder. See the results, we now get all the sheets from the selected Excel file.

 

Dynamic File Path in Power BI

Unfortunately, in Power BI a dynamic folder / file path not possible. Because unlike Excel, you don’t get the ability to write a formula in a cell that returns a file path. 🙁 But we can still create a workaround that suffices as a temporary fix.

Assume this Power BI model with multiple queries.

Here, I am assuming that the data for all queries is coming from a single folder (but it may have sub folders).

 

Step 1 – In Power Query Editor >> Home Tab >> Manage Parameters >> Create a New Parameter

In the parameter window name the parameter as “Path” and paste the folder path in the Current Value.

The Idea is to create a “Path” Parameter and pass it though the source step of all the queries.

 

Step 2 – Now in the source step of each query add the “Path” parameter. Below picture shows how can you use the “Path” parameter when getting files from a folder.

The code will slightly change in case you’d like to get the data from an Excel file instead.

let
  Source = Excel.Workbook(File.Contents(Path & '\Data.xlsx'))
in
  Source

Note that I prefixed a back-slash “\” with the file name “Data”, that is important to get the path correct.

 

This method in Power BI although not fully automated, will involve changing the file path only once in the parameter and that would change all the queries automatically.

 

 

More Power with Power Query

1. Unstack Columns in Separate Rows
2. Change Dates from MM-DD to DD-MM Format
3. Dynamic Column in Split By Feature
4. Replace Errors in Multiple Columns at once
5. Dynamic Column Selection
6. Dynamic Column Renaming
7. Running Total in Power Query