Transforming and Cleaning Data

Analogy categoryAnalogies

Why Raw Data Needs Cleaning

Source systems rarely export data in a shape suitable for analysis: dates might arrive as text, a single column might mix currency symbols with numbers, and monthly figures are often spread across twelve separate columns instead of one long table. Power Query's transformation tools exist to convert this wide, inconsistent, human-readable layout into a clean, typed, tall table that Power BI's model and DAX engine can consume efficiently.

🏏

Cricket analogy: This is like a scorer converting a handwritten scorebook full of abbreviations and crossed-out entries into a clean digital scorecard before it's fed into a broadcast graphics system.

Unpivoting and Reshaping Columns

A common source format is a 'crosstab' layout with one row per product and one column per month (Jan, Feb, Mar...); this is easy for a human to read but unusable for a data model that expects a single Date column and a single Amount column. The Unpivot Columns transformation selects the month columns and stacks them into two new columns — an Attribute column holding the former column names and a Value column holding the corresponding numbers — turning a wide table into the tall, normalized shape Power BI relationships and DAX expect.

🏏

Cricket analogy: This is like converting a wide scorecard listing each batsman's runs in separate columns for every over into a single long ball-by-ball log with one row per delivery, which analytics tools actually need.

powerquery-m

let
    Source = Excel.Workbook(File.Contents("C:\Data\SalesByMonth.xlsx"), null, true),
    SalesTable = Source{[Item="SalesByMonth", Kind="Table"]}[Data],
    UnpivotedColumns = Table.UnpivotOtherColumns(
        SalesTable,
        {"Product"},
        "Month",
        "Sales"
    ),
    RenamedColumns = Table.RenameColumns(UnpivotedColumns, {{"Month", "MonthName"}}),
    ChangedType = Table.TransformColumnTypes(RenamedColumns, {{"Sales", type number}})
in
    ChangedType

Splitting Columns and Fixing Data Types

Split Column by Delimiter breaks a single text field — such as 'Smith, John' or a full address — into multiple columns using a character, position, or number of characters as the boundary, while Change Type explicitly casts a column to Whole Number, Decimal, Date, or Text so downstream DAX calculations behave correctly instead of silently treating numbers as text. Detecting and fixing data types early is critical because DAX aggregation functions like SUM will error or return blank on a numeric-looking column that Power Query has actually loaded as text.

🏏

Cricket analogy: This is like a scorer splitting a combined 'Kohli c Dhoni b Bumrah' dismissal string into separate Batsman, Fielder, and Bowler columns so a database can query each role independently.

Use the small type-indicator icon in each column header (ABC for text, 1.2 for decimal, a calendar icon for dates) to verify types at a glance, and always apply Change Type as an early, explicit step rather than relying on Power Query's automatic type detection, which can misfire on ambiguous locale-specific date formats.

Handling Errors and Merging Queries

When a transformation fails for specific rows — a text value that can't convert to a number, for instance — Power Query marks those cells as Error rather than failing the entire query, and Remove Errors or Replace Errors lets you decide whether to drop those rows or substitute a default value. Merge Queries, separately, is Power Query's equivalent of a SQL join: it combines two queries on matching key columns (inner, left outer, right outer, or full outer) and lets you expand chosen columns from the matched table into the base table, which is the standard way to bring in a lookup table like a product catalog or currency rate table.

🏏

Cricket analogy: Replace Errors is like a scorer substituting 'DNB' for a batsman who did not bat rather than crashing the scoreboard, while a Merge is like joining the batting card with the bowling card by match ID to build a full scorecard.

Choosing the wrong join kind in Merge Queries silently changes row counts: a Left Outer join keeps every row from your base table even when no match exists (producing nulls), while an Inner Join drops unmatched rows entirely — always verify the resulting row count against your expectation before trusting downstream totals.

Raw source data is usually wide, inconsistently typed, and unsuitable for direct modeling until it is cleaned.
Unpivot Columns converts wide crosstab layouts into the tall, normalized shape Power BI relationships require.
Split Column by Delimiter, Position, or Number of Characters breaks compound text fields into separate columns.
Explicit Change Type steps prevent numeric or date columns from being silently treated as text in DAX.
Errors from failed conversions are marked per-cell and can be removed or replaced without failing the whole query.
Merge Queries performs join-style lookups between two queries using Inner, Left Outer, Right Outer, or Full Outer logic.
Always confirm row counts after a merge, since the wrong join kind can silently drop or duplicate rows.