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.
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
ChangedTypeSplitting 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.
Practice what you learned
1. What problem does Unpivot Columns solve?
2. Why is explicit Change Type important even though Power Query often auto-detects types?
3. In Merge Queries, what does a Left Outer join do differently from an Inner Join?
4. What happens by default when a transformation fails on a specific cell, such as converting non-numeric text to a number?
5. Which tool would you use to break a single 'City, Country' text column into two separate columns?
Was this page helpful?
You May Also Like
Power Query Basics
Learn how Power Query lets you connect, shape, and clean data before it ever reaches your Power BI model, using a repeatable, no-code ETL workflow.
Relationships and Star Schema
Understand how Power BI relationships work and why a star schema of fact and dimension tables is the recommended foundation for accurate, performant models.
Calculated Columns vs Measures
Learn the fundamental DAX distinction between calculated columns, which compute row-by-row and are stored, and measures, which compute dynamically based on filter context.
Related Reading
Related Study Notes in Programming
Browse all study notesApache Spark Study Notes
Programming · 30 topics
ProgrammingApache Flink Study Notes
Programming · 30 topics
ProgrammingHadoop Study Notes
Programming · 30 topics
ProgrammingSnowflake Study Notes
Programming · 30 topics
ProgrammingApache Airflow Study Notes
Programming · 30 topics
Programmingdbt (Data Build Tool) Study Notes
Programming · 30 topics