Ravindra BagaleCourses & study guides मराठी Track your progress

10. Parameters in Power Query (and Other Kinds of Parameters)

10.3 Parameterising a File or Folder Path

By Last updated:

Start with a problem

Ruhi built the bank-statement report on her laptop (C:\Users\Ruhi\Desktop\BankStatements). On Shraddha's laptop the refresh fails: path not found.

Explain simply

Put the path in a Text parameter FolderPath and write Folder.Files(FolderPath). When the files move, you change one value.

Show the data

Before: hard-coded paths

Query Source step
Bank Transactions Folder.Files("C:\Users\Ruhi\Desktop\BankStatements")
Orders File.Contents("C:\Users\Ruhi\Desktop\visuals_orders_apr_may_2026.csv")

About this data: This table uses columns: Query, Source step. This table uses columns: Parameter, Ruhi's value, Shraddha's value, Rows after refresh. Values shown are the lesson examples already on this page (not a new dataset).

Fictional sample data. Practice file: bank_statements/Jan.xlsx, bank_statements/Feb.xlsx, bank_statements/Mar.xlsx, visuals_orders_apr_may_2026.csv (source: src/practice/).

Demonstrate

  1. Manage Parameters › New: Name FolderPath, Type Text, Current value C:\Users\Ruhi\Desktop\BankStatements. Create OrdersFile the same way for the CSV path.
  2. Select Bank Transactions › click the Source step › look at the formula bar.

  3. Replace the text path with the parameter name: = Folder.Files(FolderPath) › Enter.

    Your result should look like this:

    Bank Transactions preview
    Same 9 transaction rows (spend ₹15,996)

  4. In Orders, change the Source step to File.Contents(OrdersFile).

    Your result should look like this:

    Orders preview
    Same 8 orders (₹2,100)

  5. Shraddha: Home › Transform data › Edit parameters › FolderPath D:\Finance\BankStatements › OK › Apply changes.

Show the result

Your result should look like this (fictional sample):

Parameter Ruhi's value Shraddha's value Rows after refresh
FolderPath C:\Users\Ruhi\Desktop\BankStatements D:\Finance\BankStatements 9 transactions
OrdersFile C:\Users\Ruhi\Desktop\visuals_orders_apr_may_2026.csv D:\Finance\visuals_orders_apr_may_2026.csv 8 orders

Explain what happened

The query text no longer contains a path. At refresh, Power Query reads the parameter value and builds the source from it.

Explore further

  • If you join a folder and a file name (FolderPath & "\" & "Jan.xlsx"), decide whether the folder value ends with a backslash.
  • In the Service, parameters appear under the semantic model's Settings › Parameters.

Detailed reference for this lesson:

Parameter for a file path in Power Query with example — in 30 seconds

What: A parameter is a named value, such as a folder path, that many queries can use. Change it once and every query follows.

Why: A path like C:\Users\Ruhi\Desktop works only on Ruhi's laptop. With a parameter, Shraddha changes one value on her PC.

Tiny example: FolderPath = D:\Data. The files move to E:\Reports. Change FolderPath once and press Refresh.

First look at the data (before → after):

Parameter for a file path in Power Query with example: before and after tables, fictional Blinkit Maharashtra data

Same before / after tables as text

Before — Hard-coded paths inside two queries

Query Source step
BankStatements Folder.Files("C:\Users\Ruhi\Desktop\Bank")
Targets Excel.Workbook(File.Contents("C:\Users\Ruhi\Desktop\Targets.xlsx"))

Formula / click path

Home › Manage Parameters › New: FolderPath (Text) = D:\Data
Source = Folder.Files(FolderPath & "\Bank")

After — Both queries now use one parameter

Query Source step
BankStatements Folder.Files(FolderPath & "\Bank")
Targets Excel.Workbook(File.Contents(FolderPath & "\Targets.xlsx"))

Do it step by step:

  1. Click Home › Manage Parameters › New Parameter.
  2. Name: FolderPath. Type: Text. Current Value: D:\Data. Click OK.
  3. Select the BankStatements query. Click its Source step.
  4. In the formula bar, replace the typed path with FolderPath & "\Bank".
  5. Do the same in Targets with FolderPath & "\Targets.xlsx".
  6. Test it: change FolderPath to a wrong folder. You see an error. Change it back and refresh.

What appears on screen after these steps — sample layout, fictional data

What you should see on screen after the steps (sample layout — fictional data, not a Microsoft screenshot).

Steps in Power BI

  1. Create FolderPath (Text) with current value C:\QuickCommerce\Blinkit\.
  2. Select the Source step of the query built from a Folder (or Excel/CSV) and click the ⚙ gear icon next to it.
  3. In the Folder path box, click the ABC drop-down › Parameter › choose FolderPath › OK. Many connector dialogs support this directly.
  4. For a single file, concatenate in M: File.Contents(FolderPath & "Products.xlsx").
Source   = Folder.Files(FolderPath),
Products = Excel.Workbook(File.Contents(FolderPath & "Products.xlsx"), null, true)

Ravindra Bagale's Tip

Friends, many students parameterise the folder path but leave a trailing backslash in one value and not the other, so the combined path breaks. Decide on one convention (always end with "\") and test with both your development path and the shared path. Don't worry – after doing it two or three times, it becomes a habit.

Fix common mistakes

Wrong result Reason Correction
Expression.Error: cannot convert Text to Binary Used a folder path inside File.Contents Folder.Files for folders, File.Contents for one file
File not found: …BankStatementsJan.xlsx Missing backslash when joining text Add "\" between folder and file name
Works in Desktop, fails in the Service A local path needs a gateway Use a gateway, or a SharePoint folder

Practise independently

Practice task

Shraddha moves the folder to E:\Reports\Bank. What exactly does she click, and how many queries must she edit?

Hints
  • Only the parameter value changes.
Solution

Home › Transform data › Edit parameters › FolderPath = E:\Reports\Bank › OK › Apply changes. She edits zero queries; the 9 rows (spend ₹15,996) refresh as before.

Think like a professional

Never ship a report whose source path contains a personal user name. A parameter plus a shared location is the minimum for team work.

Ravi Sir's Cards for this lesson

Parameterise a file or folder path. When a folder moves, every query pointing to it breaks.Revision card

मराठी cards