Skip to content
FR

Transforming and Loading Data with Power Query

Step 5 of 24

Transforming and Loading Data with Power Query

Introduction

This lesson completes the domain Prepare the data (25–30% of the PL-300 skills measured, outline dated April 20, 2026). The previous lessons showed how to connect to sources and how to profile and clean them. Now you shape the data: set correct types, create and transform columns, aggregate, reshape wide tables into tall ones, tame semi-structured data, build clean fact and dimension tables, combine queries and decide what is loaded into the model. This is the largest set of skills in the domain, and exam questions usually describe a messy source and ask which transformation, in which order, gives a model-ready result. Microsoft does not publish the question count or the mix of question types; OnLevelUp mock exams use a 50-question practice length and an 80% practice target, neither of which is official or a conversion of the 700/1000 passing score.

After this lesson you will be able to choose the right transformation for a reshaping problem, split a flat file into a star-schema-ready set of queries, combine data with merge and append, and configure what loads and what stays in the Power Query layer only.

Learning objectives

  • Set and verify column data types, including types that affect later aggregation and relationships.
  • Create and transform columns (split, extract, conditional, custom, index, duplicate, format).
  • Group and aggregate rows, and pivot, unpivot and transpose tables.
  • Expand semi-structured data (JSON, XML, nested records and lists).
  • Identify fact and dimension tables and build them from a flat source.
  • Choose between reference and duplicate queries, and between merge and append.
  • Choose keys that support relationships and configure query load (enable load, include in refresh).

1. Column data types

Every column in Power Query has a data type, and the type drives sorting, aggregation, formatting and relationship matching. The main types are Whole Number, Decimal Number, Fixed Decimal Number (currency, four decimals, stored as an integer scaled by 10,000), Percentage, Date/Time, Date, Time, Date/Time/Timezone, Duration, Text, True/False and Binary.

Key points:

  • Detect Data Type and the automatic Changed Type step run on load for structured sources. They are convenient but base their guess on the preview rows, so a column whose first rows are numbers can be typed as a number and fail later. Review them.
  • Fixed Decimal avoids floating-point rounding for money; Decimal Number is a double and may show tiny binary errors (0.1 + 0.2). Prefer Fixed Decimal for currency amounts.
  • Identifiers such as ProductCode = "00123" must stay Text. Converting to Whole Number removes leading zeros and breaks matches against other systems.
  • Numbers that are really categories (a StatusCode of 1, 2, 3) should be Text so they are never summed.
  • Locale matters. Use Change Type → Using Locale when the source uses a different decimal separator or date order (covered in the previous lesson).
  • A relationship requires both columns to have the same data type. A Text key on one side and a Whole Number key on the other will not relate until one is converted.

Example. Fabrikam Retail loads Sales.csv. OrderDate arrives as Text "2025-03-14". The analyst changes it to Date, otherwise the Date table relationship (which uses a Date column) cannot be created and time intelligence will fail. ProductCode stays Text because it contains values such as "007-A".

2. Creating and transforming columns

Transform versus Add Column

The Transform tab changes the selected column in place; the Add Column tab creates a new column and keeps the original. Choose Add Column when you may need the raw value again, and Transform when the original has no further use.

Common column operations

Need Where Result
Split "Smith, John" into two columns Transform or Home → Split Column → By Delimiter Two text columns
Take the first 3 characters of a code Transform → Extract → First Characters Region prefix
Combine first name and last name Add Column → Custom Column or select columns → Merge Columns FullName
Label rows by rule Add Column → Conditional Column Size = if Qty > 100 then "Bulk" else "Standard"
Number rows Add Column → Index Column (from 0 or 1) Surrogate key
Copy a column Add Column → Duplicate Column Starting point for another transformation
Date parts Transform → Date → Year, Month, Day of Week Year, Month Name
Column from examples Add Column → Column From Examples Power Query infers the M formula from sample outputs

A Custom Column uses M:

= Table.AddColumn(#"Changed Type", "Margin", each [Revenue] - [Cost], type number)

Important detail: M is case-sensitive (Text.Upper, not text.upper), and each [Revenue] refers to the field in the current row.

Where should a calculation live?

A frequent exam theme is Power Query column or DAX? Rules of thumb:

  • If the value is a property of the row and does not depend on user filters (a margin per line, a cleaned category), computing it in Power Query (or the source) is best. It is calculated once at refresh and compresses well.
  • If the value must react to slicers and filters (a ratio of totals, a year-to-date amount), use a DAX measure.
  • Prefer a column added in Power Query over a DAX calculated column when the source supports query folding, because folded steps run in the source system.

3. Grouping and aggregating

Group By (Home or Transform tab) collapses rows into groups and aggregates them: Sum, Average, Median, Min, Max, Count Rows, Count Distinct Rows and All Rows (returns a nested table for each group).

Example. A logistics company has 40 million scan events but reports only need one row per parcel per day. Grouping by ParcelID and ScanDate with a Count Rows aggregation reduces the table to about 6 million rows. The model is smaller, refreshes faster and every report still works at the daily grain. The trade-off: after grouping, detail below that grain can no longer be analysed, so confirm the required granularity first.

Details to remember:

  • The Advanced option allows several group columns and several aggregations.
  • Count Distinct Rows counts distinct values of a selected column, whereas Count Rows counts all rows in the group.
  • Grouping early (and on foldable sources) can be pushed to the source as a GROUP BY query.
  • Grouping on a column with nulls produces a group whose key is null; clean first.

4. Pivot, unpivot and transpose

Reporting tools prefer tall, narrow tables (one value per row), whereas spreadsheets are often wide (months in columns). Reshaping is a core skill.

Operation Starting shape Result Typical use
Unpivot Columns Columns Jan, Feb, Mar with sales Two columns: Attribute (month) and Value (sales) Convert a wide Excel budget into a fact table
Unpivot Other Columns Select the identifier columns, unpivot everything else Same, but robust when new month columns appear Preferred for sources that grow
Unpivot Only Selected Columns Unpivot only the chosen columns Fixed list of columns Source with stable columns
Pivot Column Rows with Metric = "Sales", "Cost" One column per metric Turn attributes into columns
Transpose Rows become columns and the reverse Flipped table Fixing a sheet turned on its side; then Use First Row as Headers

Exam tip: if the question mentions that new columns (for example a new month) will be added to the source regularly, choose Unpivot Other Columns, because it unpivots everything except the columns you keep, including future ones. Unpivot Columns with a fixed selection would miss the new column.

For Pivot Column, you choose the column whose values become headers and the values column, plus an aggregation (Sum, Count, Don't Aggregate). Without "Don't Aggregate", duplicate combinations are summed; with it, duplicates cause an error.

Worked example 1: unpivoting a budget workbook. Scenario. Contoso Foods keeps Budget.xlsx with columns Region, Category, Jan, Feb, ..., Dec. A new workbook version each year may add a Q4 Adjustment column. The model needs Region, Category, Month, Amount.

Reasoning.

  1. Promote the headers, set Region and Category as Text.
  2. Select Region and Category, then choose Unpivot Other Columns. All remaining columns become two columns, Attribute and Value.
  3. Rename Attribute to Month, Value to Amount, and set Amount to Fixed Decimal Number.
  4. Optionally filter out rows where Month is not a real month name (such as Q4 Adjustment) or keep them and classify them in a dimension.
  5. Load the table as a fact table.

Why the other choices are worse. Selecting all twelve month columns and using Unpivot Columns breaks when the layout changes. Transpose swaps rows and columns but does not produce a tall table with a repeated attribute. Pivot does the opposite of what is needed. Writing twelve separate measures in DAX instead of reshaping creates maintenance work and a poor model.

5. Semi-structured data

Sources such as JSON, XML and some web APIs return records (a row of named fields), lists (an ordered set of values) and nested tables. In the editor you see clickable Record, List or Table cells, and the expand icon at the top right of the column.

Steps to flatten:

  1. Convert a list to a table when needed (To Table).
  2. Expand the record column to pick the fields you want. Untick Use original column name as prefix if the names are unique.
  3. If there is a nested table or list, expand again with Expand to New Rows (a list with three elements becomes three rows) or Extract Values (joins list elements into one text value with a delimiter).
  4. Set data types on the final columns.

The M functions behind this are Json.Document, Xml.Tables, Table.ExpandRecordColumn and Table.ExpandListColumn. Expanding a record column hard-codes the field names, so if the API sometimes omits a field the step can fail; the previous lesson showed try ... otherwise as a defence.

Example. An e-commerce API returns orders as JSON; each order has a lines list with product and quantity. Expanding lines to new rows yields one row per order line, which is exactly the grain of a sales fact table. If you used Extract Values instead, you would get one row per order with a text list of products, which is useless for aggregation.

6. Fact and dimension tables

The recommended shape for Power BI is a star schema: central fact tables surrounded by dimension tables.

Fact table Dimension table
Rows represent Events or measurements (a sale, a click, a shipment) Things you filter and group by (a product, customer, date)
Typical columns Keys to dimensions, numeric measures (quantity, amount) A unique key, descriptive attributes (name, category, city)
Size Many rows Few rows relative to facts
Key property Foreign keys repeat The key is unique

Source systems often deliver a flat, denormalised table, such as one Sales export with OrderID, OrderDate, CustomerName, CustomerCity, ProductName, Category, Quantity, Amount. To get a star schema:

  1. Create a reference to the flat query for each dimension (see the next section).
  2. In the customer reference, keep CustomerName and CustomerCity, then Remove Duplicates. Add an Index Column for a surrogate CustomerKey if no stable business key exists.
  3. Repeat for products.
  4. In the fact query, merge with each dimension to bring in the key, then remove the descriptive text columns.
  5. Load the three tables and relate on the keys.

Benefits: smaller model (descriptive text stored once), faster filtering, simpler DAX, and relationships that behave predictably.

7. Reference versus duplicate queries

Both create a new query from an existing one, but they behave differently.

Reference Duplicate
What it creates A new query whose first step reads the output of the original A full independent copy of all steps
Source is read Once for the chain (the original query is evaluated and reused in the editor) Separately for each copy
If you change the original The reference inherits the change The copy does not change
Typical use Build several dimension tables from one cleaned staging query Start a variant of a query that will diverge

Reference is the right choice for the staging pattern: one query connects and cleans the file (Sales_Raw, load disabled), and several queries reference it to produce DimCustomer, DimProduct and FactSales. A fix to a data-type step in the staging query then reaches all of them. Use duplicate only when you intend the copies to evolve independently. Note that referenced queries can re-evaluate the source for each downstream query if the source does not support caching, which is a performance consideration for slow sources.

8. Merge and append

Append: stack rows

Append Queries (Home → Combine) stacks tables with the same purpose, for example monthly files Sales_2025_01 ... Sales_2025_12, or sales from two regional systems. Columns are matched by name; columns present in only one table are kept and filled with null in the other. Choose Append Queries as New to keep the originals. When files live in a folder, Combine Files applies the same transformation to each file and appends automatically, which is preferable to appending dozens of queries by hand.

Merge: join columns

Merge Queries is a join on one or more matching columns. Choose the join kind:

Join kind Rows returned
Left Outer All rows from the first table, matching rows from the second
Right Outer All rows from the second table, matching from the first
Full Outer All rows from both
Inner Only matching rows
Left Anti Rows in the first table with no match in the second
Right Anti Rows in the second with no match in the first

Notes:

  • The merge result adds a column of nested tables; expand it to choose which columns to bring in.
  • Select columns in the same order in both tables for multi-column merges.
  • Matching is exact: case, trailing spaces and data type must agree. A fuzzy merge option exists for approximate text matching with a similarity threshold.
  • A Left Anti merge is the classic way to find unmatched rows, such as orders with no matching customer (an integrity check) or products that never sold.

Worked example 2: enriching sales and finding orphans. Scenario. Sales has ProductID and Amount; Products has ProductID, Name and Category. The business wants a sales table with Category, and a list of sales whose product is missing from the product list.

Reasoning.

  1. Merge Sales (first) with Products (second) on ProductID, join kind Left Outer, so every sale is kept.
  2. Expand the new column, selecting only Category.
  3. Check column quality for Category. Empty values are sales with an unknown product.
  4. For a dedicated orphan list, merge Sales with Products using Left Anti; the result contains only the unmatched sales.

Why alternatives fail. Inner would silently drop sales for unknown products and understate revenue. Append adds rows, not columns. A DAX LOOKUPVALUE calculated column would work but repeats logic in the model and is slower to refresh than a merge that folds in the source.

If both sources are in the same database, a merge can fold into a SQL join; a merge between different sources (SQL and Excel) runs in the mashup engine and can use a lot of memory for big tables.

9. Keys for relationships

Relationships in Power BI link a column in one table to a column in another. Good keys are:

  • Unique and non-null on the one side (a dimension). Duplicates on the "one" side make Power BI create a many-to-many relationship, which you usually want to avoid.
  • Same type and format on both sides.
  • Simple: a single column. Power BI relationships use one column on each side, so for a composite key create a concatenated column or an index in Power Query (for example [Region] & "-" & [StoreID]).
  • Stable: business keys that never change are better than names.

Use surrogate keys (index columns) when the business key is composite, unstable or missing. When you merge to bring a surrogate key into the fact table, handle non-matching rows (a placeholder "Unknown" row in the dimension keeps the fact rows connected).

10. Configuring query load

After transformation, each query decides what reaches the model.

  • Enable load (right-click a query). When unchecked, the query exists only in the Power Query layer and is italic in the Queries pane. Staging queries and parameter-like helper queries should not load.
  • Include in report refresh controls whether the query is refreshed with the semantic model refresh in Desktop and the service. Disable it for rarely changing reference data you refresh manually.
  • Close & Apply loads the results to the model; the Apply Changes banner appears when queries changed but are not yet loaded.
  • The load destination for Desktop is the in-memory model; in the service, a dataflow can hold the output instead (covered in later lessons).
  • Query folding: when a step can be translated into the source's language (SQL), it runs at the source. View Native Query (right-click a step) is enabled when the step folds. Steps that break folding, such as adding an index column or merging two different sources, run in Power Query; place folding-friendly steps (filter rows, remove columns, group) first.

Example. The staging query Orders_Raw reads a 120-million-row SQL table. The analyst filters to the last three years and removes 15 unused columns before any non-foldable step. The folded filter makes the source return 18 million rows instead of 120 million. Orders_Raw has load disabled; FactOrders, DimCustomer and DimProduct reference it.

11. Pitfalls and exam tips

  • Do not unpivot with a fixed list of columns if the source grows; use Unpivot Other Columns.
  • A relationship needs matching types; fix types in Power Query rather than in DAX.
  • Keys with leading zeros must stay Text.
  • Inner joins can hide data; use Left Outer for enrichment and Left Anti for diagnostics.
  • Choose reference when queries should share upstream changes, duplicate when they should diverge.
  • Disable load for staging queries to save memory and keep the model tidy.
  • Filter rows and remove columns as early as possible and before non-folding steps.
  • Do not group away detail that reports still need.
  • Exam tip: when options include a DAX calculated column and a Power Query step for the same row-level result, prefer Power Query unless the question states that the logic must react to filters or needs the model.
  • Exam tip: when the stem says "reduce model size", think: remove columns and rows, change types, group to a coarser grain, disable load for helpers, replace high-cardinality text with keys.

12. Mini case study

Case. Northwind Outfitters receives a single Orders_Export.csv with columns OrderID, OrderDate (text yyyymmdd), CustomerName, CustomerEmail, Country, ProductCode (text with leading zeros), ProductName, Category, Qty, UnitPrice. A second file, Budget.xlsx, has Country, Category, Q1, Q2, Q3, Q4; a new Q5 Adjustment column appears in some years. Reports must show actual versus budget by country and category with a Date table. The model must be small and refresh quickly.

Tasks. Describe the Power Query design.

Solution.

  1. Create a staging query Orders_Raw from the CSV: promote headers, set OrderDate to Date using the yyyymmdd format (Using Locale or split and recombine), keep ProductCode as Text, Qty as Whole Number, UnitPrice as Fixed Decimal. Trim text columns. Disable load.
  2. Reference Orders_Raw to create DimCustomer: keep CustomerName, CustomerEmail, Country, remove duplicates, add an index CustomerKey.
  3. Reference Orders_Raw to create DimProduct: keep ProductCode, ProductName, Category, remove duplicates on ProductCode (unique key, Text).
  4. Reference Orders_Raw to create FactSales: add LineAmount = Qty * UnitPrice, merge with DimCustomer (on name and email) to bring CustomerKey, remove the descriptive columns and keep OrderID, OrderDate, CustomerKey, ProductCode, Qty, LineAmount.
  5. Load Budget.xlsx, Unpivot Other Columns on Country and Category, rename to Period and BudgetAmount. Because the layout may grow, unpivot other columns handles Q5 Adjustment too. Budget is its own fact table at country-category-period grain.
  6. Relate both facts to DimCountry and DimCategory (or to DimCustomer and DimProduct if the grain allows) and to the Date table.
  7. Enable load only for the facts and dimensions; keep Orders_Raw as a staging query.

Commentary. The flat file becomes a star schema; the budget lives at a coarser grain than sales, so it relates to shared dimensions (country, category) rather than to individual customers. If the budget were forced into the sales fact, amounts would multiply by the number of order lines. Choosing Unpivot Columns with four fixed quarters would lose the Q5 Adjustment column, so Unpivot Other Columns is the correct operation.

Key terms

  • Fixed Decimal Number: exact currency-style type with four decimals.
  • Group By: aggregates rows by one or more columns.
  • Unpivot / Pivot / Transpose: reshape operations between wide and tall layouts.
  • Record / List: structured values in semi-structured data; expand them to columns or rows.
  • Fact / dimension table: events with measures versus descriptive lookup entities.
  • Reference / duplicate: a query that builds on another's output versus an independent copy.
  • Merge / append: join columns versus stack rows.
  • Left Anti: join returning first-table rows with no match.
  • Surrogate key: artificial unique identifier such as an index.
  • Query folding: translation of steps into source queries.
  • Enable load: option controlling whether a query reaches the model.

Summary checklist

  • I can pick the right data type (text for codes, fixed decimal for money, date for dates).
  • I can create columns in Power Query and decide when DAX is more appropriate.
  • I can group, pivot, unpivot (including other columns) and transpose.
  • I can expand records and lists, choosing new rows versus extract values.
  • I can split a flat table into fact and dimension tables with unique keys.
  • I can choose reference or duplicate, merge join kinds, and append.
  • I can disable load for staging queries and keep foldable steps first.

Official sources