Power QueryExercises

Naming queries and steps in Power Query: a convention that scales

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

10 min read

A naming convention that scales gives every query a name that says its role and its content, such as stg_Orders or fn_CleanCode, and every step a name that says what it changed, such as Kept completed orders. The point is that anyone can read the Queries pane and the Applied Steps list and know what each item does without opening it.

Why names are worth a convention

Power Query names everything for you. Steps become Filtered Rows, Filtered Rows1, Changed Type2; queries become Table1, Sheet1 (2) or the file name. Those names describe the command or the source, never the purpose, so a workbook with fifteen queries and two hundred steps becomes unreadable quickly.

Microsoft's best-practice guidance recommends documenting work by giving steps, queries and groups meaningful names and descriptions, because "these details make the purpose of each transformation easier to understand and maintain" (Best practices when working with Power Query, Microsoft Learn). A convention turns that advice into something a team can apply consistently.

Names also matter to the language. In M, each step is a variable in a let expression and each query is a name other queries can refer to. A regular name starts with a letter or underscore and contains only letters, digits, underscores and dots; anything else, such as a space, a hyphen, a leading digit or a keyword, has to be written as a quoted identifier, #"Like this", which the M specification describes as a way to allow "any sequence of zero or more Unicode characters to be used as an identifier" (M language lexical structure, Microsoft Learn). The lesson on identifiers, quoted names, escapes and comments covers the rules.

Query names: role plus content

Give each query a short prefix for its role, then a name for its content. Output queries, the ones people load to a sheet or a report, get a plain readable name because readers see them.

RolePatternExampleLoads?
Raw source, one per file or tablesrc_ + sourcesrc_SalesExportNo (connection only in Excel, load disabled in Power BI)
Staging, cleaned and typedstg_ + entitystg_Orders, stg_CustomersNo, connection only
Lookup or mapping tablelkp_ + what it mapslkp_ChannelCodesNo, connection only
Custom functionfn_ + verb phrasefn_CleanCodeNot applicable
Parameterp_ + valuep_ReportMonth, p_SourceFolderNot applicable
OutputPlain descriptionSalesByRegionYes

Three reasons this works:

  • The Queries pane sorts itself. Queries with the same prefix sit together, and you can see at a glance which ones load and which are building blocks.
  • References read naturally. An output step that says Table.NestedJoin(stg_Orders, …, lkp_ChannelCodes, …) explains itself. Because names like these use only letters, digits and underscores, the references need no #"…" quoting.
  • It matches how queries are split. Splitting into staging and output queries is a common way to keep long queries manageable, and Microsoft's best-practice guidance recommends splitting large queries into referenced ones; the lesson on referencing other queries for staging and reuse shows how to build it, and the glossary entry on the staging query defines the term.

Back the prefixes with groups. The Queries pane lets you create groups, which Microsoft describes as folders for your queries, and move queries into them (Using the Queries pane, Microsoft Learn). A group per role, such as Sources, Staging, Lookups, Functions and Outputs, mirrors the prefixes.

The exact prefixes matter less than using one set consistently. If your team already says stg and dim, keep those. Parameters that switch a source, as in test and production sources with Power BI parameters, are where a clear p_ name pays off most.

Step names: describe the result

Step names should say what the data looks like after the step, in a short phrase. A past-tense verb at the start works well: Kept completed orders, Removed banner rows, Added net margin, Set column types. Compare the same query with generated names and with descriptive ones.

With the names the editor generates:

let
    Source = #table({"order_id", "status", "region", "net_amount"}, {{"0041", "Complete", "North", 120.5}, {"0042", "Cancelled", "South", 80}, {"0043", "Complete", "South", 45}}),
    #"Filtered Rows" = Table.SelectRows(Source, each ([status] = "Complete")),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"region"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"Sum", each List.Sum([net_amount]), type number}}),
    #"Sorted Rows" = Table.Sort(#"Grouped Rows", {{"region", Order.Ascending}})
in
    #"Sorted Rows"

Result

regionCountSum
North1120.5
South145

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

With descriptive names, for both steps and output columns:

let
    Source = #table({"order_id", "status", "region", "net_amount"}, {{"0041", "Complete", "North", 120.5}, {"0042", "Cancelled", "South", 80}, {"0043", "Complete", "South", 45}}),
    CompletedOrders = Table.SelectRows(Source, each [status] = "Complete"),
    NetByRegion = Table.Group(CompletedOrders, {"region"}, {{"Orders", each Table.RowCount(_), Int64.Type}, {"Net amount", each List.Sum([net_amount]), type number}}),
    SortedByRegion = Table.Sort(NetByRegion, {{"region", Order.Ascending}})
in
    SortedByRegion

Result

regionOrdersNet amount
North1120.5
South145

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

Both return North with one order of 120.5 and South with one order of 45. Only the second tells you that the filter keeps completed orders, that the sum is a net amount, and what the output is. The generated version says Sum, which could be anything.

Spaces or no spaces?

There are two reasonable styles for step names:

  • Sentence style with spaces, such as Kept completed orders. It reads best in the Applied Steps list, which is where most people look. In the M it appears as #"Kept completed orders".
  • Compact style without spaces, such as CompletedOrders. It reads best in the Advanced Editor and needs no quoting, which suits teams that write M by hand.

Pick one for a workbook and stick to it. Mixing both in one query is harder to read than either on its own.

Rename safely

Rename steps from the Applied Steps list (right-click, Rename) and queries from the Queries pane, rather than by retyping names in the Advanced Editor (Applied steps, Microsoft Learn). Editing a name by hand in one place and not the others leaves a reference to a name that no longer exists:

let
    Source = #table({"order_id", "status", "net_amount"}, {{"0041", "Complete", 120.5}, {"0042", "Cancelled", 80}}),
    #"Kept completed orders" = Table.SelectRows(Source, each [status] = "Complete")
in
    #"Kept complete orders"

Result: an error

Expression.Error: The name 'Kept complete orders' wasn't recognized. Make sure it's spelled correctly.

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

The step is called Kept completed orders but the final line asks for Kept complete orders, so the query fails with an Expression.Error saying the name wasn't recognised. The error page for the name wasn't recognized lists the usual causes, and the exercise fix a query whose steps point at the wrong step practises tracing step references.

What makes a good step name

  • Short. As a house rule, aim for under about forty characters, so names stay readable in the Applied Steps pane.
  • About the result. Removed cancelled orders says what changed; Filtered status doesn't say how.
  • Free of changing values. Kept orders after 1 Jan goes stale the day the date moves to a parameter. Kept orders in report period doesn't.
  • Unique in the query. If you need Kept rows twice, the two steps are doing different things, so name the difference.

Column names belong to the reader

Output column names end up in PivotTables, charts and report visuals, so they are for the people reading the report, not for the database. Rename columns in the output query to plain words, and leave source names as they are in staging queries so they stay easy to match with the source system.

When a source uses a consistent style such as snake case, one step can tidy every column, including columns added later:

let
    Source = #table({"order_id", "status", "net_amount"}, {{"0041", "Complete", 120.5}}),
    ReadableNames = Table.TransformColumnNames(Source, each Text.Proper(Text.Replace(_, "_", " ")))
in
    ReadableNames

Result

Order IdStatusNet Amount
0041Complete120.5

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

The columns become Order Id, Status and Net Amount. Note Order Id rather than Order ID: a general rule handles most names, and a short explicit rename can fix the few exceptions afterwards. Table.TransformColumnNames has more examples, the post on handling changing column headers uses it to absorb renames, and the exercise clean every column name, including future ones puts it into practice.

A one-page convention you can adopt

  1. Queries: role prefix plus content (src_, stg_, lkp_, fn_, p_), using only letters, digits and underscores. Outputs: a plain description.
  2. One group in the Queries pane per role.
  3. Only output queries load; the others are connection only.
  4. Steps: a short phrase describing the result, in one style per workbook.
  5. A comment or step description for any decision someone might question: right-click a step and choose Properties to add a description, or write a // comment in the Advanced Editor.
  6. Output columns in plain words; source names left alone in staging.
  7. Rename in the Applied Steps list and the Queries pane, never by retyping names in the Advanced Editor.

Naming is one of the habits in 10 Power Query habits that keep queries easy to maintain, and the guide to reviewing a query before you share it turns it into a checklist item. Many exercises on Power Query Exercises show their starting query with generated names, so you can practise renaming steps as you read them.

Frequently asked questions

Does renaming a step change what the query does?

No. A step name is only a label for the value the step produces. As long as every reference to it uses the new name, the result is identical. If you rename a loaded query in Power BI, check the report afterwards, because the query name is also the name of the table that visuals and measures use.

Should step names be in English if my team works in another language?

Use the language your team reads. M accepts any Unicode characters in quoted identifiers, so names in any language work. What matters is that everyone who maintains the query understands them.

Is it worth renaming steps in a query only I use?

Usually, yes. The person most likely to debug your query in six months is you, and by then the reason for Filtered Rows3 will be forgotten. Renaming takes seconds per step.

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.