How Power Query works: from a raw file to a repeatable process

If you clean a similar file every month, you probably already have a recipe in your head: remove spaces, interpret dates, standardise channels and check the total. Power Query lets you save that recipe. At the next refresh, the same logic runs against the new data. A sequence of manual corrections becomes something you can inspect, name and verify.

I find that particularly useful for recurring reports. It does not mean the tool understands the business or recognises every possible file change. A renamed column or a different amount unit can make the old logic inappropriate. Automation repeats incorrect assumptions too. That is why checking the output belongs inside the recipe.

I will use four synthetic records. You can paste the M example into a blank query in Excel for Windows or Power BI Desktop. It has been executed in Excel’s Power Query engine. No company file, database connection or credentials are required, so you can learn the transformations before introducing your own source.

Where Power Query fits into analytical work

Power Query retrieves and transforms data. It is available in Excel and Power BI products, among other environments, with differences between hosts. It is not the engine behind every analytical tool. Python and Tableau can prepare data through their own mechanisms. Microsoft’s overview identifies products using Power Query and distinguishes its desktop and browser experiences.

I separate the workflow into source, transformations and destination. The source may be a file or a supported system connector. Transformations describe the preparation rules. The result goes where the hosting application permits, such as a worksheet table or a data model. Connector availability, authentication and loading options need checking for the specific product and version.

Preparing tables is only part of modelling. Relationships between model tables and DAX measures require separate decisions. The Power BI relationship documentation describes how relationships propagate filters. Merging tables in Power Query changes the prepared output, while a model relationship connects how loaded tables are analysed. Those operations can have different consequences.

Begin with the meaning of the columns

Each row in our example represents one fictional transaction. ID is a text identifier, Channel is a category, Date is a date and Amount is a value. I preserve the leading zeros in ID. The identifier 001 exists to identify a record, not to participate in arithmetic. Converting it to a number merely because it looks numeric would discard information unnecessarily.

Channels contain inconsistent letter case and extra spaces. Dates follow Polish conventions, and amounts use decimal commas. One amount is unknown, one is zero and one is negative. The negative amount represents an adjustment in this exercise, so it stays in the total. I do not automatically delete it as an error. Its meaning would need confirmation in a real source.

Before transforming anything, I record the expected result: four rows, four preserved identifiers, three known amounts and one missing amount. The normalised channels should be WEB and PARTNER. Available amounts should sum to 100.50. These expectations provide a check independent of whether the editor displays a neat table and a reassuring status message.

Run the complete M example

Create a blank query, open the Advanced Editor and replace its contents with the following code. Name the query OrdersPrepared, because the next example refers to it. Interface labels vary by language and version, but the M code is identical in the Polish and English articles. Its source is self-contained within the query.

let
    Source = #table(
        type table [ID = text, Channel = text, Date = text, Amount = nullable text],
        {
            {"001", " Web ", "31.01.2026", "120,50"},
            {"002", "web", "01.02.2026", "0,00"},
            {"003", "Partner", "02.02.2026", null},
            {"004", "partner", "03.02.2026", "-20,00"}
        }
    ),
    CleanChannel = Table.TransformColumns(Source, {{"Channel", each Text.Upper(Text.Trim(_)), type text}}),
    Typed = Table.TransformColumnTypes(CleanChannel, {{"Date", type date}, {"Amount", Currency.Type}}, "pl-PL")
in
    Typed

Source creates the input table. CleanChannel trims surrounding spaces and standardises channel case. Typed converts the date and amount, explicitly selecting the pl-PL culture. The expression after in identifies the query result. Step names are names I chose, not a compulsory command sequence. I can rename them if I keep their references consistent.

The editor can create many such steps through its interface. Reading the code exposes the exact rule and parameters. I follow the dependencies from source to result without assuming that the visible step list is a literal account of physical execution inside the engine. For this exercise, the useful question is what each transformation passes to the next one.

Types and culture determine interpretation

The text 120,50 should become one hundred and twenty and a half. The date 31.01.2026 should become the last day of January. The culture argument in Table.TransformColumnTypes makes that interpretation explicit. I do not rely on the author’s computer and the recipient’s computer sharing regional settings. Assumptions at the text-to-value boundary can be expensive.

Currency.Type is a fixed-decimal type suited to the sample amounts. It does not assign a particular currency or perform exchange-rate conversion. The unit remains an assumption of the dataset. Likewise, changing a cell’s display format in Excel will not repair an incorrect interpretation that already happened in the query. Display formatting and value types solve different problems.

I treat automatic type detection as a proposal to review, especially for identifiers, dates and separators. The Power Query data type documentation explains the role of locale. My sample includes a day greater than twelve so that testing does not rely exclusively on dates that could be interpreted in two different orders.

Check the result through a separate query

After preparing the table, create another blank query. This block references OrdersPrepared, so the first query must have that name. The control result has one row and three columns. There is no need to duplicate the transformation logic to summarise it: a reference lets the check use the same prepared dataset.

let
    Source = OrdersPrepared,
    Rows = Table.RowCount(Source),
    KnownAmounts = List.NonNullCount(Source[Amount]),
    KnownTotal = List.Sum(Source[Amount])
in
    #table({"Rows", "KnownAmounts", "KnownTotal"}, {{Rows, KnownAmounts, KnownTotal}})

The values are 4, 3 and 100.5. List.NonNullCount counts available values, while List.Sum adds the non-null amounts. The missing amount has not become zero. If no known values exist, the sum returns null. I keep the observation count next to the result so the reader can see what the calculation covers.

I also inspect individual rows. ID 001 has WEB and 120.5; 002 has WEB and zero; 003 has PARTNER and null; 004 has PARTNER and minus twenty. Dates should contain the expected year, month and day. A matching total would not reveal swapped identifiers or shifted dates. Different checks cover different kinds of mistakes.

A conversion error is useful information

In a copy of the exercise, replace the text 120,50 with oops. Converting it to a numeric type should produce a cell error. I do not immediately replace that error with zero. First, I want to know whether the source contains a typo, a new format or a value with a different meaning. The error-handling documentation distinguishes step-level and cell-level problems.

A third query can count rows with amount errors. It returns zero for the original sample and one after the deliberate change. This is a diagnostic result that does not remove the problematic record. Table.SelectRowsWithErrors limits inspection to the specified columns, making the scope of this counter clear rather than suggesting a complete assessment of data quality.

let
    ErrorRows = Table.SelectRowsWithErrors(OrdersPrepared, {"Amount"})
in
    #table({"AmountErrorRows"}, {{Table.RowCount(ErrorRows)}})

Removing error rows may be justified, but it changes the analysed population. I record the rejection count and reason. Silently dropping a record with an invalid amount can reduce a total. A visible exception requiring a decision is more useful than an apparently complete report that cannot be reconciled to its source.

Refresh repeats the rule against the current source

Our input is embedded in the code, so refreshing it alone reproduces the same four records. To observe a change, add a Source row or edit an existing value. With an external file, the query rereads the source defined in its logic. You still need to know whether it reads one named file, every matching file or a system dataset.

I do not use manual edits in the loaded result table to repair the process. A later load can replace them. Instead, I correct the rule, fix the source or create a separate controlled adjustment table. That makes the output reproducible and its changes explainable. A manual correction in the wrong place can quickly become knowledge held by only one person.

If someone renames Amount to Value, the step referring to Amount needs adjustment. Adding rows and changing the schema are different events. I would not promise that a query written once will handle every future file. I establish the expected columns and the signal that should stop the process for investigation.

Appending and merging solve different problems

With transaction files from two successive months, I usually want to add rows. That is an append operation. The append documentation explains that columns match by name. If one file uses Amount and another uses Value, the output can contain two columns with gaps instead of one coherent amount field. I establish the common structure first.

To add a channel description from a separate lookup table, I need a merge on a key. The merge overview describes available join types. I check whether the lookup key is unique. Two matching descriptions can increase the row count when expanded, potentially increasing the amount total too. Correct execution does not establish that the relationship itself is appropriate.

For a folder of monthly files, I also inspect names, extensions and covered periods. A copy of the same file can duplicate transactions. A temporary file may have another structure. Sharing a folder does not make every file suitable for combination. I record the selection rule and the files used by a particular refresh.

Preview is not a complete dataset check

Column profiling helps identify missing values, errors and unusual entries. I check whether it covers a sample or the full dataset. The profiling tools can default to the first thousand rows. A clean beginning does not prove that another date format or invalid amount never appears later. The scope of inspection should be a deliberate choice.

On larger datasets, I record row counts before and after filters, missing-key counts and known-amount totals. I do not expect every transformation to preserve the row count. A filter should reduce it, while grouping changes the level of detail. I do expect to explain each change. If I cannot, I return to the first step where output diverged from the expectation.

Duplicate removal needs a definition too. Identical rows might represent a repeated import or two genuine events. I do not apply deduplication as universal housekeeping. I establish the key, scope and record-selection rule when other fields differ. The data cleaning guide explores those decisions in more detail.

Performance and loading have limits

For some sources, Power Query can pass part of a transformation to the source through query folding. This depends on the connector and operations. I assume neither that every step executes locally nor that every step is pushed into a database. Our small in-code table checks logic without measuring large-process performance.

Loading into a data model avoids placing every record on a worksheet, but it does not create unlimited capacity. Memory, the model, environment and source still impose constraints. I choose a destination according to how the output will be used and what the product supports. Copying a million records onto a sheet is unnecessary if the intended result is a summary of a few categories.

Refresh scheduling and source access depend on the host and environment. A workbook working on its author’s computer may need additional configuration elsewhere or in a service. During handover, I document the source, owner and refresh requirements. Secrets do not belong in query descriptions or public examples.

Move the exercise to your own file

Once you understand the example, replace the embedded table with a source connection. Inspect the Source step on its own before adding cleaning. A file may contain an extra title row, several worksheets or a comment beneath the data. I select the relevant object and check its headers and record count instead of assuming the first visible cell begins the dataset.

Then I apply rules individually. After channel normalisation, I inspect the value list. After type conversion, I inspect errors. After filtering, I check counts. Different column names need deliberate changes to references. Code pasted into an arbitrary table cannot identify the intended fields merely from their appearance. Names, types and meanings form the agreement between source and query.

For the first run, I use a small representative copy containing exceptions as well as tidy rows. I retain a control result for comparison with later refreshes. A move to a new source needs another check even when its headers look identical. Matching structure does not prove that the fields still describe the same events and units.

What to verify before using the output in a report

Try two experiments. First, add ID 005, channel web, date 04.02.2026 and amount 30,00. The summary should show five rows, four known amounts and a total of 130.50. Then restore the original and replace its first amount with oops. The error report should show one row. These are different changes with different expected responses.

Also inspect an empty result: zero rows, zero known amounts and a null sum. Decide how the report should describe that state. An empty delivered file might mean no events or a process failure, so I avoid assigning business meaning without further information. Similarly, a zero amount remains a number and does not join the missing-value category.

Finally, describe each step in ordinary language and save the expected checks. If AI helps you write M, execute its output against the same sample and edge cases. A working preview begins the verification. A repeatable process should be refreshable, comparable with expectations and understandable to another person without reconstructing your mouse movements from memory.