Use a Cell Value as a Parameter in Power Query

by

Power Query normally hard-codes values such as a year or a row count into its steps. Point those steps at a cell instead, and anyone can change the cell and click Refresh All to get new results, without opening the editor.

TechniqueTable → Drill Down → use in M code
ExampleTop N films of any year from a website
Works inExcel 2016 and later, Microsoft 365

What we're building

A sheet that fetches the highest-grossing films of any year from Box Office Mojo. Type a year and a number in two cells, click Refresh All, and the list updates.

Changing the year and number and refreshing the list

Step 1: Build the query with fixed values

  1. Data → From Web, paste the page address for one year, such as https://www.boxofficemojo.com/year/2010/, and click OK, then Connect.
  2. In the Navigator, choose Table 0 → Transform Data.
  3. Select Rank, Release, Gross and Distributor (hold Ctrl), right-click → Remove Other Columns.
  4. Home → Keep Rows → Keep Top Rows → 15.
From Web dialog with the page address
Navigator with Table 0 selected
Remove Other Columns
Keep Top Rows set to 15

This works, but 2010 and 15 are now fixed inside the query.

Step 2: Turn a cell into a parameter

  1. Type Year in B2 and 2015 in B3. Click in it → Data → From Table/Range → OK.
  2. In the editor, right-click the value 2015 → Drill Down. The query now returns just the number.
  3. Rename the query pYear (double-click it in the Queries list).
Year table loaded into Power Query
Drill Down on the value
Query returning a single value

Repeat for a Number cell (for example 7) and call it pTopN.

Step 3: Use the parameters in the main query

Right-click the main query → Advanced Editor, and replace the fixed values:

Before After
Web.Contents("https://www.boxofficemojo.com/year/2010/") Web.Contents("https://www.boxofficemojo.com/year/" & Text.From(pYear) & "/")
Table.FirstN(#"Removed Other Columns", 15) Table.FirstN(#"Removed Other Columns", pTopN)
Advanced Editor with the year fixed in the address
Advanced Editor using the parameter

Text.From turns the number into text so it can be joined into the address. Use the step names exactly as they appear in your own query.

Step 4: Fix the privacy message

Combining a workbook cell with a web source often shows Formula.Firewall: Query … references other queries. Go to File → Options and settings → Query Options → Current Workbook → Privacy and choose Ignore the Privacy Levels.

Formula.Firewall privacy error
Ignore the privacy levels option

This is fine for public web data and your own cells. For company data from several sources, set each source's privacy level instead: Data → Get Data → Data Source Settings → Edit Permissions.

Step 5: Load and use it

Load pYear and pTopN as Only Create Connection, and the main query to a sheet. Now change the year or number and click Data → Refresh All.

Only Create Connection
Top 7 films of 2013 loaded into Excel

Things to know

  • If a website changes its layout or addresses, the query breaks; check Table 0 is still the right table.
  • The same method works for a file path, a date range or a region name: any value you'd otherwise type into a step.
  • Power Query also has Home → Manage Parameters, but those are changed inside the editor. The cell method is easier for other people.

Related