excelisfun - MSPTDA 20: Query Parameter for Dynamic Folder Location / Data Source in Power BI & Excel Power Query

Short Summary

Main recommendation: Use a Power Query parameter that reads a folder path from an Excel table (or a Power BI parameter) so the folder location can be changed in one place and all dependent queries refresh automatically. The video demonstrates building this for combining multiple text files from a folder in both Excel and Power BI Desktop, which makes the workbook portable across computers with different folder paths.

Key Points

• Store the folder path in an Excel table and load it into Power Query, so updating the path in Excel and clicking Refresh All points the queries at the new folder.
• Extract the path as text and pass it through Folder.Files so the files are landed as a table, which avoids the Formula.Firewall error when the path query is referenced by another query.
• In Power BI Desktop, create a text parameter for the folder path and select it in the Folder connector, then change the path later via Edit Queries and Edit Parameters.
• Combining the text files with the Combine Binaries (double expand) feature auto-generates a sample file, a parameter, and a reusable custom function that converts each file into a proper table.
• The approach is useful when the same files sit in different folder locations on different computers.

Details Worth Noting

• Concrete instructions:
  • Excel: Data > Get & Transform > From Table/Range. In Power Query, rename the table to "folder parameter" and load it.
  • Right-click the cell containing the path and choose Drill Down to get the path as text.
  • Click the f(x) button to add a step that wraps the text in Folder.Files(...), which lands all files and attributes in a table.
  • Right-click the Content column, choose Remove Other Columns, then click the double-arrow Combine Files icon, confirm the Tab delimiter, and click OK.
  • Click Close & Load To > Table on existing sheet (e.g., E1). To change folders later, paste the new path into the Excel table and click Refresh All (or Ctrl+Alt+F5).
  • Power BI: Home > Manage Parameters > New Parameter. Name it (e.g., "Folder Path Address"), set type to Text, and paste the current path. Then Get Data > Folder, choose Parameter as the path source.
  • Uncheck "Enable load" on the path query, reference it to create the all-text-files query, remove other columns, and use the double-expand button with Tab as the delimiter.
  • Click Close & Apply. To change folders later, go to External Data > Edit Queries > Edit Parameters, paste the new path, and click OK.
• Explanatory points: Power Query uses zero-based indexing, so {0} retrieves the first row. The table index lookup and column lookup are used to extract the path value.
• The Combine Files process creates a sample file query, a parameter, and a custom function that is reused across files.
• The speaker says he personally likes this approach because he works on multiple computers with the same files stored in different folder locations.

Bottom Line

A single parameterized folder path, set in Excel or Power BI, lets you relocate the source files without rebuilding queries, and the combined table updates on refresh.