Power QueryExercises

10 Power Query habits that keep queries easy to maintain

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

10 min read

A maintainable Power Query query is one that someone else, or you in six months, can read, trust and change without breaking it. Ten habits address the most common causes of maintenance work: name steps for what they do, set types on purpose, cut data early, refer to columns by name, keep lookups in tables, split long queries, use parameters and functions, check the output, and record the decisions behind the steps.

Why maintainability matters more than cleverness

Queries outlive the person who wrote them. A query built for one month's report is still running a year later, now fed by a slightly different export, and someone has to work out why the total is off. Microsoft's own guidance on Power Query covers the same ground from the tool's side: it recommends documenting work with meaningful step and query names, splitting large queries into referenced modules, and designing queries that keep working when the source changes (Best practices when working with Power Query, Microsoft Learn).

The ten habits below apply to analysts working in Excel and Power BI alike. None needs advanced M. Each example ran in the site's simulator, and the output is shown under each block.

1. Name every step for what it does

The editor names steps after the command: Filtered Rows, Filtered Rows1, Changed Type2. Those names say what button was clicked, not why. Rename each step to describe its effect, such as Kept completed orders or Set column types. You can rename a step from its right-click menu, and add a description through Properties (Applied steps, Microsoft Learn).

let
    Source = #table(
        {"Order ID", "Status", "Region", "Net", "Internal note"},
        {{"0041", "Complete", "north ", "120.5", "x"}, {"0042", "Cancelled", "South", "80", "y"}, {"0043", "Complete", "South", "45", "z"}}
    ),
    #"Kept report columns" = Table.SelectColumns(Source, {"Order ID", "Status", "Region", "Net"}),
    #"Kept completed orders" = Table.SelectRows(#"Kept report columns", each [Status] = "Complete"),
    #"Tidied region names" = Table.TransformColumns(#"Kept completed orders", {{"Region", each Text.Proper(Text.Trim(_)), type text}}),
    // Order ID stays text: the leading zeros are part of the identifier
    #"Set column types" = Table.TransformColumnTypes(#"Tidied region names", {{"Order ID", type text}, {"Net", type number}}, "en-GB")
in
    #"Set column types"

Result

Order IDStatusRegionNet
0041CompleteNorth120.5
0043CompleteSouth45

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

The result is two completed orders, 0041 in North and 0043 in South, with the leading zeros intact. You can read the query top to bottom without opening a single step. The companion post on naming queries and steps suggests a convention you can adopt as a team.

2. Set types deliberately, once

Automatic type detection guesses from the first rows it sees, and it adds a Changed Type step that lists every column by name. That step breaks when a column is renamed at the source and can turn identifiers like 0041 into the number 41, the problem covered in keeping leading zeros. Microsoft's guidance is to set the correct type for each column, because type-specific filters and transformations depend on it (Best practices, Microsoft Learn).

A good pattern is one deliberate typing step, placed right after choosing columns and before any step that relies on a type, with identifiers kept as text and a culture stated for dates and decimals. In the example above the filter and the trim only touch text columns, so the typing step can safely come last. The lesson choose types deliberately: identifiers are text explains the reasoning, and the exercise keep order numbers' leading zeros tests it.

3. Keep only what you need, early

Remove the columns and rows you don't need as early as you can. Microsoft recommends filtering early because later steps then process fewer rows, and with sources that support query folding the filter can be pushed back to the source (Best practices, Microsoft Learn).

For columns, prefer Choose Columns (which lists what to keep) over Remove Columns (which lists what to drop). When the export gains a new column next month, Choose Columns ignores it; Remove Columns lets it through to every later step and into your report. The step Kept report columns above is this habit in action. The lesson Choose Columns vs Remove Columns covers the trade-off.

4. Refer to columns by name, never by position

Any step that picks a column by its position breaks silently when the export inserts a column. Compare the two approaches on an export that has gained a Channel column:

let
    Source = #table({"Order", "Region", "Channel", "Amount"}, {{"A1", "North", "Web", 100}, {"A2", "South", "Store", 250}}),
    ThirdColumn = Table.ColumnNames(Source){2},
    Result = #table({"Approach", "Picks up"}, {{"Third column by position", ThirdColumn}, {"Column named Amount", Text.From(List.Sum(Source[Amount]))}})
in
    Result

Result

ApproachPicks up
Third column by positionChannel
Column named Amount350

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

The position-based step now picks up Channel instead of Amount, while the name-based step still totals 350. Position-based references usually creep in through hand-written or copied M such as Table.ColumnNames(Source){2}, or through generic names like Column1 that an import without headers or a split produces. The exercise fix a query that names columns by position is a short practice run.

5. Keep lookups in tables, not nested ifs

A nested if that maps codes to names hides business rules inside a formula. When a new code appears, someone has to find and edit the step. A mapping table puts the rule where anyone can read and update it, and a merge applies it:

let
    Orders = #table({"Order", "Channel code"}, {{"A1", "W"}, {"A2", "S"}, {"A3", "P"}, {"A4", "X"}}),
    ChannelMap = #table({"Channel code", "Channel"}, {{"W", "Web"}, {"S", "Store"}, {"P", "Phone"}}),
    Merged = Table.NestedJoin(Orders, {"Channel code"}, ChannelMap, {"Channel code"}, "Map", JoinKind.LeftOuter),
    #"Added channel name" = Table.ExpandTableColumn(Merged, "Map", {"Channel"})
in
    #"Added channel name"

Result

OrderChannel codeChannel
A1WWeb
A2SStore
A3PPhone
A4Xnull

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

Orders A1 to A3 get Web, Store and Phone. A4 has the unknown code X and gets null, which is easy to filter and report on. The mapping table's key must be unique: if W appeared twice, every web order would be duplicated by the merge, which is exactly what habit 9's check catches. In a real workbook, the mapping table would be an Excel table or a separate query that a colleague can edit without opening the Power Query editor. The guide mapping tables vs nested if goes further.

6. Split long queries into staging and output queries

A query with forty steps is hard to follow. Microsoft suggests splitting large queries into smaller referenced queries (Best practices, Microsoft Learn), and the editor has a command for it: right-click a step and choose Extract Previous to move everything before it into a new query.

A common split is a staging query that imports and cleans one source, and an output query that references the staging queries, merges them and shapes the result. Staging queries are set to load as connection only in Excel, or with Enable load cleared in Power BI, so they don't fill the workbook or model with intermediate tables. The lesson on referencing other queries for staging and reuse shows the pattern step by step.

7. Put changing values in parameters

File paths, report dates, thresholds and server names change. When they are typed inside step formulas, every change means hunting through queries. Microsoft describes a parameter as "a way to easily store and manage a value that can be reused" (Parameters, Microsoft Learn), and many dialogs let you pick a parameter instead of typing a value.

The rule of thumb: if a value would change when someone else runs the query, or next month, it belongs in a parameter or a settings table. The lesson parameters: one value, many queries walks through both. For values a report user should change, an Excel cell can act as the parameter, and for switching servers see test and production sources with parameters.

8. Turn repeated logic into a function

If you clean codes the same way in three queries, a fix has to be made three times, and one copy is always missed. A custom function keeps the logic in one place:

let
    CleanCode = (code as nullable text) as nullable text =>
        if code = null then null else Text.Upper(Text.Remove(Text.Trim(code), {"-", " "})),
    Suppliers = #table({"Supplier code"}, {{" ab-123"}, {"AB 124 "}, {null}}),
    Customers = #table({"Account"}, {{"cd-9"}, {" CD 10"}}),
    CleanSuppliers = Table.TransformColumns(Suppliers, {{"Supplier code", CleanCode, type nullable text}}),
    CleanCustomers = Table.TransformColumns(Customers, {{"Account", CleanCode, type nullable text}})
in
    Table.Combine({Table.RenameColumns(CleanSuppliers, {{"Supplier code", "Code"}}), Table.RenameColumns(CleanCustomers, {{"Account", "Code"}})})

Result

Code
AB123
AB124
null
CD9
CD10

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

Both tables come out in the same format: AB123, AB124, null, CD9 and CD10. The nullable text parameter type lets null through (a plain as text would reject it), and the if makes the intent explicit. In a workbook, CleanCode would be its own query that other queries call. The lesson write and use custom functions covers parameters, types and optional arguments.

9. Check the output and fail loudly

A query that refreshes without errors can still load wrong data: duplicate keys that double a total, or an empty file that produces an empty report. Add a step that tests what you rely on and raises an error when the test fails. Error.Record builds an error with a reason and a message (Error.Record, Microsoft Learn).

let
    Source = #table({"Order", "Amount"}, {{"A1", 100}, {"A1", 250}, {"A2", 80}}),
    DuplicateOrders = Table.RowCount(Source) - List.Count(List.Distinct(Source[Order])),
    #"Checked order keys" =
        if DuplicateOrders > 0
        then error Error.Record("Validation failed", Text.From(DuplicateOrders) & " duplicate order number(s) in the export")
        else Source
in
    #"Checked order keys"

Result: an error

Validation failed: 1 duplicate order number(s) in the export

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

With order A1 appearing twice, the refresh stops with the reason "Validation failed" and the message "1 duplicate order number(s) in the export". With unique order numbers, the same step passes the table through unchanged:

let
    Source = #table({"Order", "Amount"}, {{"A1", 100}, {"A2", 250}, {"A3", 80}}),
    DuplicateOrders = Table.RowCount(Source) - List.Count(List.Distinct(Source[Order])),
    #"Checked order keys" =
        if DuplicateOrders > 0
        then error Error.Record("Validation failed", Text.From(DuplicateOrders) & " duplicate order number(s) in the export")
        else Source
in
    #"Checked order keys"

Result

OrderAmount
A1100
A2250
A380

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

A stopped refresh is annoying; a report that quietly doubles revenue is worse. The lesson validate data and fail loudly shows other checks worth adding, such as row counts and control totals.

10. Write down why, not what

Good step names say what a step does. Comments say why, which is the part nobody can reconstruct later. In the Advanced Editor, // starts a comment, as in habit 1: "Order ID stays text: the leading zeros are part of the identifier". Step descriptions in the Applied Steps list do the same job for people who never open the Advanced Editor.

Comment the decisions a reviewer might question: why rows are excluded, why a value is hard-coded, which team agreed a rule. Then delete steps and queries you no longer use, because dead steps are where the next person gets lost. The guide to reviewing a query before you share it turns these habits into a checklist.

Where to start

You don't need to adopt all ten at once. On your next query, rename the steps and replace any Remove Columns with Choose Columns; both take minutes and pay off at the first changed export. Add a check step the first time a bad load slips through. The refresh-proof checklist is a good single page to keep beside you.

For practice, the exercises on Power Query Exercises are graded against hidden variations of the data, so a solution that relies on column positions or a hard-coded value fails, the same way it would on next month's export.

Frequently asked questions

Do these habits slow down building a query?

Slightly, at first. Renaming steps and choosing columns add seconds per step. The time comes back the first time you or a colleague has to debug or change the query, because you can see what each step does without stepping through it.

Should every query have a validation step?

Every query whose output someone relies on should have at least one: a row count, a control total or a key check. Exploratory queries you will throw away don't need them.

Is Remove Columns ever the right choice?

Yes, when you genuinely want every column except a few, including columns added in future, such as a wide monthly export where each new month column should flow through to an unpivot. Choose the option that matches what should happen when a new column arrives.

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.