What is Power Query

Power Query is the data preparation engine built into Power BI Desktop. It sits between your raw data sources and your data model — its job is to connect to data wherever it lives, clean and reshape it, and deliver it in a form your model can work with efficiently.

Everything you do in Power Query is recorded as a series of steps. Those steps are written in a language called M-Query (formally called the Power Query Formula Language). You do not need to write M-Query by hand for most transformations — the Power Query interface generates it automatically when you click through the UI. But understanding what M-Query is doing gives you the ability to handle edge cases, build reusable logic, and debug problems that the UI cannot easily surface.

The key distinction: Power Query is the interface. M-Query is the language underneath it. Learning to read M-Query — even if you rarely write it from scratch — makes you significantly more capable as a Power BI practitioner.

The transformation pipeline

Every data transformation in Power BI follows the same four-stage pipeline: Connect, Transform, Model, Visualise. Power Query owns the first two stages entirely.

In the Connect stage, Power Query establishes a connection to one or more data sources — Excel files, SQL databases, APIs, JSON feeds, SharePoint lists, web pages, or any of the 100+ connectors Power BI supports. At this stage, the data is read but not yet modified.

In the Transform stage, Power Query applies the sequence of changes you have defined — removing unwanted columns, renaming fields, changing data types, filtering rows, merging tables, adding calculated columns, handling missing values, and unpivoting or pivoting data as needed. The result is a clean, structured table ready to load into the model.

Connecting to data sources

In a travel platform BI project, data arrives from multiple sources simultaneously: booking transaction files exported as Excel workbooks, supplier reference data, currency exchange rate tables, agent registration data, and country/region lookup tables. Each source has its own structure and naming convention — none of them are immediately ready to be loaded into the model.

Power Query connects to each source through the Get Data menu. Once connected, the data appears in the Power Query Editor as a preview. From here, every transformation step is applied interactively — you click, Power Query records the M-Query step, and the preview updates to show the result.

A typical data source list for a travel business intelligence dashboard might include: all bookings processed (the primary transaction file), supplier codes and names (a reference table), country and continent mappings (a geography lookup), historic currency exchange rates (for multi-currency conversion), and agent registration records (for agent behaviour analysis).

The Power Query Editor — Applied Steps on the right, live data preview in the centre, ribbon transformation tools at the top.

Key transformations you will use repeatedly

Change data types is almost always the first step. When Power Query reads an Excel file, it makes a best guess at data types — but guesses are often wrong. Booking amounts read as text, dates read as numbers, IDs read as decimals. Explicitly setting data types is non-negotiable before loading to the model.

Remove columns should be done aggressively. Load only the columns your reports will actually use. Every unused column consumes memory and slows refresh. In Power Query, select the columns you want to keep, right-click, and choose Remove Other Columns rather than removing them one by one.

Filter rows at the source level wherever possible. If your report only needs bookings from the last three years, filter to that range in Power Query rather than loading all historical data and filtering in DAX. The earlier you reduce data volume, the faster everything downstream runs.

Rename columns to consistent, readable names before loading. Column names become field names in your model, which become the names users see in slicers and visuals. Renaming in Power Query once is far better than remembering that BookingAmt_INR_New means booking amount in INR throughout your DAX.

Merge queries is the Power Query equivalent of a SQL JOIN. Use it to bring lookup data from a dimension table into your fact table — for example, adding country region from the Countries reference table onto a booking record that only contains a country code.

Use First Row as Headers is needed whenever your source file has a blank row or a title row above the actual column headers. Power Query cannot automatically detect this in all cases, so checking the header row is always worth doing on a new data source.

100+Data connectors available
∞Steps recorded automatically
0Manual re-work on refresh

Understanding M-Query

Every action you perform in the Power Query UI generates an M-Query expression behind the scenes. You can see the full M-Query for any query by clicking View → Advanced Editor. What you see is the complete transformation logic written as a series of let expressions.

The structure of every M-Query follows the same pattern. A let block defines each transformation step as a named variable. Each step references the previous step by name and applies one transformation. The final in statement returns the last step — the finished table that gets loaded into the model.

A simple real example: connecting to an Excel file and preparing it for the model might look like this in M-Query:

let
  Source = Excel.CurrentWorkbook(){[Name="BookingsData"]}[Content],
  ChangedType = Table.TransformColumnTypes(Source,{
    {"BookingDate", type date},
    {"BookingAmount", type number},
    {"AgentCode", type text}
  }),
  RemovedColumns = Table.RemoveColumns(ChangedType,{"InternalRef", "LegacyID"}),
  FilteredRows = Table.SelectRows(RemovedColumns, each [BookingStatus] = "Confirmed"),
  RenamedColumns = Table.RenameColumns(FilteredRows,{
    {"BookingAmt_INR", "BookingAmountINR"}
  })
in
  RenamedColumns

Each line is a step. Each step builds on the previous one. The final result — RenamedColumns — is the clean table that loads into the model. If you need to debug, you can click any intermediate step name in the Applied Steps panel and see exactly what the data looked like at that point in the transformation chain.

Applied steps and why they matter

The Applied Steps panel in Power Query Editor shows every transformation step as a named item in a list. This is one of Power Query's most powerful features — and one of the most underused.

Every step is independently clickable. Click any step and the data preview shows you what the table looked like after that step was applied. This makes debugging straightforward: if something looks wrong in the final output, you step back through the Applied Steps until you find where the problem was introduced.

Steps are also reorderable and deletable. If you realise you applied a filter too late in the chain, you can drag it earlier. If a step is no longer needed, you delete it. The M-Query regenerates automatically to reflect the change.

Practical tip: Rename your Applied Steps to meaningful names as you build the query. The default names (Changed Type, Removed Columns) are accurate but generic. Renaming them to something like "Set booking date as date type" or "Remove legacy columns not used in reports" makes the query self-documenting — invaluable when you revisit it six months later or hand it to a colleague.

Combining multiple data sources

Real-world BI projects almost always involve multiple source files that need to be combined before loading to the model. Power Query handles this through two operations: Merge (joining tables horizontally, like a SQL JOIN) and Append (stacking tables vertically, like a SQL UNION).

Append is used when you have the same structure of data split across multiple files — for example, monthly booking export files that each have the same columns. Appending them creates one unified table with all months combined.

Merge is used when you need to bring columns from one table into another based on a matching key — for example, bringing the country region from a Countries reference table into your Bookings fact table by matching on country code. In the star schema, you typically keep these as separate dimension tables and use relationships rather than merging everything into the fact table — but for lookup values that genuinely belong on the fact table, a merge is the right approach.

Best practices before loading to the model

Disable load for reference queries. When you create intermediate queries that are only used as steps in building another query — a staging table, a lookup merge — right-click the query and uncheck Enable Load. This prevents the intermediate table from appearing in your model and consuming memory unnecessarily.

Set data types explicitly on every column before loading. Power Query's automatic type detection is useful but imperfect. Any column that will be used in a relationship must have its data type set explicitly and consistently — a text key in one table joining to a whole number key in another will fail silently in ways that are hard to debug.

Do your filtering early. Row-level filtering applied in Power Query reduces the data volume before it reaches the model. DAX filtering applied at report time operates on the full model. Both are necessary tools, but reducing volume at the Power Query stage improves performance at every subsequent stage.

The interactive transformation pipeline

The diagram below illustrates how data flows through the four-stage pipeline from raw sources to final visualisations — and where Power Query sits within it. Each stage is clickable to explore what happens at that point.

Power Query is the layer that makes everything else reliable. Clean data in means correct visuals out. Spending time on your transformation logic — naming steps well, filtering early, setting types explicitly — is time that pays back every time the report refreshes.

In the next post in this series, we go into DAX — the formula language that lives inside the model and powers the measures your visuals depend on, starting with the most impactful pattern: year-over-year comparison using YTD, LYTD, and LLYTD.

`