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.

Folder with 3 Files

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

Blank Workbook Added to the Folder

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

Formula to get Dynamic Folder Path

Confirming the above formula returns the correct folder path

Result of the Formula is the 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..

File Path converted to a table

 

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)

Drill Down to Create a Parameter

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

Dynamic Path as Text Parameter

 

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

Advanced Editor Code

 

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 Output

 

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.

Multiple Queries in Power BI

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

Create a new parameter

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

Dynamic File Path Power Query - Setting up the Parameter

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.

Advanced Editor Power BI

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

 



Topics that I write about...






Download Smart Ebooks on
Excel and Power BI