Power QueryExercises

Use an Excel cell as a Power Query parameter

How-to · By Tahir Nas · Examples run in our simulator · Updated

10 min read

To use an Excel cell as a Power Query parameter, give the cell a name, read it in a query with Excel.CurrentWorkbook, convert it to the right type, and use that value in a filter or a source path. Anyone who can type in the cell can then change what the query returns, without opening the Power Query Editor.

Step 1: name the cell

Type the value in a cell, for example a start date in B2 of a sheet called Settings. Select the cell, type a name such as StartDate in the Name Box to the left of the formula bar, and press Enter.

The name matters because of what Power Query can see in the current workbook. Microsoft's reference for Excel.CurrentWorkbook says it returns the tables, named ranges and dynamic arrays in the workbook and, unlike Excel.Workbook, does not return sheets. A value in an unnamed cell has no entry to pick.

Step 2: read the value into Power Query

Create a blank query (Data > Get Data > From Other Sources > Blank Query), open the Advanced Editor and enter:

let
    Source = Excel.CurrentWorkbook(),
    StartCell = Source{[Name = "StartDate"]}[Content]{0}[Column1],
    StartDate = Date.From(StartCell)
in
    StartDate
Shown for Excel and Power BI Desktop only: this code needs a data source or language feature the simulator doesn't run, so no output is shown.

Each step does one thing:

  • Excel.CurrentWorkbook() returns a table with a Name column and a Content column holding each item's data.
  • Source{[Name = "StartDate"]}[Content] picks the row for the named cell and takes its content, a table with one row.
  • {0}[Column1] takes the first row and the column Power Query gives the cell. A named range has no header row, so the column is usually Column1; open the Content table in the editor to confirm the name in your workbook.
  • Date.From turns the value into a date. A date cell often arrives as a datetime, and M will not compare a date column with a datetime value.

You can rebuild the same structure with #table to see each step work:

let
    CurrentWorkbook = #table({"Name", "Content"}, {{"StartDate", #table({"Column1"}, {{#datetime(2026, 10, 1, 0, 0, 0)}})}, {"Orders", #table({"OrderID"}, {{"O-1"}})}}),
    StartCell = CurrentWorkbook{[Name = "StartDate"]}[Content]{0}[Column1],
    StartDate = Date.From(StartCell)
in
    StartDate

Result

2026-10-01

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

The result is the single date 2026-10-01. Name this query StartDate and set it to load as connection only, because nothing needs to see it on a sheet.

Step 3: use the value in another query

Any query in the workbook can now refer to StartDate by name. In the query you want to filter, apply a normal filter from the column drop-down, then open the formula bar and replace the typed date with the query name. The finished step looks like this:

let
    StartDate = #date(2026, 10, 1),
    Orders = #table(type table [OrderID = text, OrderDate = date, Amount = number], {{"O-1001", #date(2026, 9, 28), 120}, {"O-1002", #date(2026, 10, 2), 80}, {"O-1003", #date(2026, 10, 15), 45}}),
    FromStart = Table.SelectRows(Orders, each [OrderDate] >= StartDate)
in
    FromStart

Result

OrderIDOrderDateAmount
O-10022026-10-0280
O-10032026-10-1545

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

In your workbook, StartDate would be the separate query rather than a step; here it is a step so the example runs on its own. The filter keeps orders O-1002 and O-1003 and drops the September order. Change the cell, refresh, and the result follows. Table.SelectRows is the function behind every row filter.

Several settings in one table

When a report needs a start date, a region and a minimum amount, three named cells work, but a small Excel table is tidier. Create a table called Settings with two columns, Setting and Value, and one row per setting. Read it with Excel.CurrentWorkbook(){[Name = "Settings"]}[Content], then look each value up by its name:

let
    Settings = #table(type table [Setting = text, Value = any], {{"StartDate", #datetime(2026, 10, 1, 0, 0, 0)}, {"Region", "North"}, {"MinAmount", 50}}),
    StartDate = Date.From(Settings{[Setting = "StartDate"]}[Value]),
    Region = Settings{[Setting = "Region"]}[Value],
    MinAmount = Number.From(Settings{[Setting = "MinAmount"]}[Value]),
    Orders = #table(type table [OrderID = text, Region = text, OrderDate = date, Amount = number], {{"O-1001", "North", #date(2026, 9, 28), 120}, {"O-1002", "North", #date(2026, 10, 2), 80}, {"O-1003", "South", #date(2026, 10, 5), 200}, {"O-1004", "North", #date(2026, 10, 15), 45}}),
    Filtered = Table.SelectRows(Orders, each [Region] = Region and [Amount] >= MinAmount and [OrderDate] >= StartDate)
in
    Filtered

Result

OrderIDRegionOrderDateAmount
O-1002North2026-10-0280

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

Only O-1002 passes all three tests: O-1001 is before the start date, O-1003 is in the South, and O-1004 is under the minimum amount. Adding a setting later is one new row in the table, and the table's Setting column documents what each value is for. The challenge filter orders to a date window from a parameter table practises exactly this pattern.

Guard against empty or wrong cells

A cell is easy to clear or mistype, and the query should say so plainly. If the cell is empty, the value arrives as null; check for it and stop with a message the reader understands:

let
    CurrentWorkbook = #table({"Name", "Content"}, {{"StartDate", #table({"Column1"}, {{null}})}}),
    StartCell = CurrentWorkbook{[Name = "StartDate"]}[Content]{0}[Column1],
    StartDate = if StartCell = null then error "Enter a start date in the cell named StartDate" else Date.From(StartCell)
in
    StartDate

Result: an error

Expression.Error: Enter a start date in the cell named StartDate

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

The refresh fails with Enter a start date in the cell named StartDate, which tells the person exactly what to fix. Without the check, a text entry such as next Monday would fail inside Date.From with a DataFormat.Error about converting or parsing the date, which is accurate but less helpful. The error page for couldn't parse the input provided as a Date value covers that family of messages. Excel's own data validation on the cell (allow dates only) stops most of these mistakes before they reach Power Query.

If the cell is renamed or deleted, the lookup fails with Expression.Error: The key didn't match any rows in the table. The key didn't match any rows error page explains the message.

Use a cell for a file or folder path

The most useful version of this technique puts the source path in a cell, so the same workbook can point at this month's file or a colleague's copy of the folder:

let
    Settings = Excel.CurrentWorkbook(){[Name = "Settings"]}[Content],
    FolderPath = Settings{[Setting = "FolderPath"]}[Value],
    Files = Folder.Files(FolderPath)
in
    Files
Shown for Excel and Power BI Desktop only: this code needs a data source or language feature the simulator doesn't run, so no output is shown.

This is where many people meet the Formula.Firewall error. Microsoft's article behind the scenes of the Data Privacy Firewall explains the rule: Power Query splits queries into partitions, and a partition may either access compatible data sources or reference other partitions, but not both. A step that references a FolderPath query (which itself reads the workbook) and also calls Folder.Files breaks that rule, and the message reads "references other queries or steps, so it may not directly access a data source".

The fixes, in the order to try them:

  1. Read the cell and the source in the same query, as in the block above. There is then nothing to reference, and the workbook and the folder only need compatible privacy levels.
  2. Set compatible privacy levels. Microsoft's privacy levels article explains that in Excel you set them under Data > Get Data > Data Source Settings, Edit Permissions. The firewall article adds that two sources can share data both ways only when both are Public or both are Organizational, so set the workbook and the folder to the same one of those if your data allows it.
  3. Ignore privacy levels for the workbook, under Data > Get Data > Query Options, then Privacy in the Current Workbook section. Microsoft warns that this can expose sensitive data, so use it only when you are sure the sources hold nothing confidential.

Microsoft documents a relaxed version of the rule for Power BI Desktop from its July 2026 version and for the Power BI service, in its page on firewall partitions that reference other partitions. That page does not mention Excel, so in Excel plan for the classic rule. The same privacy article also notes that Excel for Mac does not currently support privacy levels. The lesson on privacy levels and the formula firewall explains the idea with diagrams.

A cell or a Power Query parameter?

Power Query also has its own parameters, created under Home > Manage Parameters, which Microsoft describes in its parameters article. Both store one value that many queries share. The difference is who changes it and where. The guide to managing queries in Excel shows where parameters live in Excel and how to change one.

Named Excel cellPower Query parameter
Who changes itAnyone using the workbook, in the gridSomeone who opens the Power Query Editor
Type checkingNone unless you add data validation and convert in MSet a type when you create it
Suggested valuesExcel data validation listList of values or a list query
Firewall risk when used in a source pathYes, it is a workbook data sourceLower: Microsoft's firewall article says simple parameter queries are trimmed into the partitions that use them
Works in Power BINoYes

Use a cell when the person running the report is not the person who built it. Use a Power Query parameter when only the builder changes the value, or the query will move to Power BI. In Power BI, parameters can switch between test and production sources. The lesson on parameters: one value, many queries covers the parameter side, and the post on getting data from another workbook shows the source steps a path parameter usually feeds.

To practise switching a source with a value rather than editing the query, try the challenge switch the source query with a parameter on Power Query Exercises.

Frequently asked questions

Does the query refresh when I change the cell?

No. Changing the cell does not trigger a refresh. Select Data > Refresh All after you change it, or set the queries to refresh on open; the post on refreshing Power Query automatically covers the options.

Can I use a cell with a drop-down list?

Yes. Add Excel data validation with a list to the named cell. Power Query reads whatever value is selected, so the list controls what users can choose without any change to the query.

Why does my date cell come through with a time?

A date cell often arrives in Power Query as a datetime at midnight. M does not compare dates with datetimes, so a filter that mixes them fails:

let
    StartDate = #datetime(2026, 10, 1, 0, 0, 0),
    Orders = #table(type table [OrderID = text, OrderDate = date], {{"O-1002", #date(2026, 10, 2)}}),
    FromStart = Table.SelectRows(Orders, each [OrderDate] >= StartDate)
in
    FromStart

Result: an error

Expression.Error: We cannot apply operator >= to types Date and DateTime.

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

The simulator raises Expression.Error: We cannot apply operator >= to types Date and DateTime. Wrap the value in Date.From, as in the examples above, before comparing it with a date column.

Sources

About the author

Tahir Nas, software engineer, writes and reviews the lessons, guides and exercises on Power Query Exercises. Tahir has 8 years of experience in tech, working across system administration, cloud architecture, QA, scripting and automation, and software engineering in many languages and mainstream databases. GitHub profile. Every M example shown with a result was run in the site's simulator when the page was built, and the output is that run's result; examples marked for Excel and Power BI only are shown without output. How content is tested.

Keep reading

Practise Power Query in the browser with graded exercises checked against fresh data.

Power Query Exercises is an independent educational platform and is not affiliated with, endorsed by, sponsored by, or approved by Microsoft Corporation. All third-party trademarks belong to their respective owners.