Power Query Tutorial: Transforming Data Without Code

Power Query is the data-preparation engine inside Power BI Desktop (and Excel, and several Microsoft data services). You connect to a source, shape the data through menus, and every action is recorded as a step that re-runs on each refresh (Microsoft Learn: what is Power Query?). "Without code" is only half true. Every click writes a line of M code. Knowing what those steps do is the difference between a report that refreshes cleanly next month and one that breaks the first time the source changes.

This tutorial follows one realistic job: a monthly sales export (CSV) with a date column per month, messy types and a few blank rows, shaped into a table Power BI can model.

Opening Power Query Editor

In Power BI Desktop, choose Get data to connect, then Transform data on the Home tab to open Power Query Editor instead of loading straight into the model. When the query is ready, Close & Apply loads it (Microsoft Learn: query overview in Power BI Desktop). Loading without transforming is the most common beginner mistake: it pushes the raw file, blank rows, text-typed numbers and all, into the model, where fixing it is harder.

Applied steps are a recipe, not a history

Every transformation appears in the Applied steps list on the right. On refresh, Power Query does not replay your clicks against a stored result. It runs the recipe again, from the source, top to bottom (Microsoft Learn: applied steps). Three habits follow from that:

• Rename steps that matter. Right-click a step, then Properties. "Removed blank CustomerID rows" says what "Filtered Rows 2" does not.
• Delete carefully. Later steps often refer to columns an earlier step created. Delete until end removes a step and everything after it when you need to rebuild from a point.
• Insert, don’t append, when you fix something. Right-click a step and Insert step after to put a fix where it belongs, instead of patching at the end.

Turn on profiling, for the whole column

On the View tab, enable Column quality, Column distribution and Column profile. Column quality labels each column’s values as valid, error or empty, so a text value in a number column shows up immediately (Microsoft Learn: data profiling tools).

Set data types deliberately

For files such as CSV and TXT, Power Query detects types automatically, often adding a Changed Type step right after the source (Microsoft Learn: data types). Check it rather than accept it. A postcode or product code typed as a whole number loses its leading zeros. A date read with the wrong locale swaps day and month. Type-specific filters, such as the date filters, are not even offered until a column has the right type.

Set types once, in a step you name, after you have removed header junk and blank rows, and before calculations that depend on them.

The transformations you will use most

Unpivot: pick the option that survives next month

Our sales export has one column per month: Product | Jan-2026 | Feb-2026 | …. Power BI wants one row per product per month: Product | Month | Sales. That is an unpivot, and Power Query offers three versions:

The first two produce the same result and adapt when the source gains columns (Microsoft Learn: unpivot columns). Use Unpivot other columns on the columns that identify a row. Then rename the generated Attribute and Value columns to Month and Sales, and set their types.

Query folding: why step order matters

When the source is a database, Power Query tries to translate your steps into the source’s own query language, SQL for example, so the database does the filtering and aggregating. This is query folding, and the outcome is full, partial or none (Microsoft Learn: query folding basics). Connectors such as SQL Server and OData commonly fold. Files such as CSV and Excel have no query engine and never fold, so for them every step runs in Power Query.

• Put filters and column removal early, while the query still folds. The database then sends fewer rows.
• Some steps stop folding, and every step after them runs locally. Keep such steps late.
• Right-click a step and choose View Native Query. If it is available, that step folds and you can read the SQL being sent.

Structure queries so the report can be maintained

Microsoft’s best-practice guidance favours several small queries that reference each other over one long query (Microsoft Learn: Power Query best practices). A layout that works for most reports:

1. Source queries: connect and apply only the fixes every consumer needs (headers, types, blank rows). Right-click → untick Enable load so they do not become model tables.
2. Shaping queries: right-click a source query → Reference, and build the fact and dimension tables from it.
3. Parameters for anything that changes between environments or months: file paths, server names, a cut-off date.

To see what this produces, open View → Advanced Editor. A short query for the sales file reads like this. Each line is one applied step, and each step refers to the one before it:

let
    Source = Csv.Document(File.Contents(SalesFilePath), [Delimiter = ",", Encoding = 65001, QuoteStyle = QuoteStyle.Csv]),
    PromotedHeaders = Table.PromoteHeaders(Source, [PromoteAllScalars = true]),
    RemovedBlankProducts = Table.SelectRows(PromotedHeaders, each [Product] <> null and [Product] <> ""),
    UnpivotedMonths = Table.UnpivotOtherColumns(RemovedBlankProducts, {"Product"}, "Month", "Sales"),
    TypedColumns = Table.TransformColumnTypes(UnpivotedMonths, {{"Product", type text}, {"Month", type date}, {"Sales", type number}})
in
    TypedColumns

SalesFilePath is a parameter, so pointing the report at next month’s file is a one-value change. Converting a "Jan-2026" header into a real date may need your locale, or an extra step if the header format is unusual. Reading and writing M directly is covered in Power Query M language basics. Handling cells that fail conversion is covered in error handling in Power Query.

Power Query or DAX?

A useful default: shape and clean in Power Query, calculate in DAX. Columns that describe a row and never change with the report’s filters, such as a cleaned product name or a month key, belong in Power Query, so they are computed once at refresh. Totals, ratios and time comparisons that must respond to slicers belong in DAX measures; see DAX: measures, calculated columns and context.

Where to go next

Data transformation with Power Query is a core module of our Power BI course, followed by data modelling and DAX.

Sources

• What is Power Query? — Microsoft Learn
• Query overview in Power BI Desktop — Microsoft Learn
• Using the Applied steps list — Microsoft Learn
• Using the data profiling tools — Microsoft Learn
• Data types in Power Query — Microsoft Learn
• Unpivot columns — Microsoft Learn
• Replace values and errors — Microsoft Learn
• Query folding basics — Microsoft Learn
• Best practices when working with Power Query — Microsoft Learn