Transforming Rows and Columns

Cleaning data is mostly about two things: fixing what is wrong with your rows and fixing what is wrong with your columns. This lesson covers every row and column transformation you will use regularly — removing, reordering, renaming, pivoting, unpivoting, transposing, and promoting headers — with before-and-after examples for each one so you know exactly when to reach for each tool.

The Working Dataset

All examples in this lesson start from the same messy raw table — exactly the kind of thing you get when someone exports data from a legacy system or pastes rows together from multiple spreadsheets.

Row Transformations

Remove Top Rows

When a source file has junk rows above the real data — report titles, export metadata, blank spacers — Remove Top Rows strips them off. You specify exactly how many rows from the top to delete.

Use First Row as Headers

After removing the junk rows, the real column headers are sitting in the first data row — still labelled Column1, Column2, etc. Use First Row as Headers promotes that row into actual column names and removes it from the data.

Remove Bottom Rows

Report footers, totals rows, and "End of file" markers live at the bottom. Remove Bottom Rows strips them the same way Remove Top Rows does from the top. The challenge is knowing exactly how many rows to remove — if the number changes between refreshes, the step breaks. Use Remove Rows → Remove Blank Rows or filter by a specific value instead when the count is unpredictable.

Filter Rows

Row filtering keeps only the rows that match a condition. It is the most frequently used row operation in real-world data cleaning — excluding cancelled orders, restricting to a date range, removing test records, or keeping only a specific region.

Remove Duplicates

Remove Duplicates keeps only the first occurrence of each unique combination of values in the selected columns. Select one column and it deduplicates by that column alone. Select multiple columns and it deduplicates by the combined key across all selected columns.

Column Transformations

Rename Columns

Column names in reports and DAX measures are visible to report viewers. Clean, consistent names make everything downstream easier. Double-click any column header to rename it inline — faster than using the ribbon.

Reorder Columns

The order of columns in Power Query determines the default column order in the Data pane and in Table visuals. Drag column headers left or right in the preview to reorder them — each drag records a "Reordered Columns" step. Alternatively, right-click a header → Move → To Beginning / To End / Left / Right.

Remove Columns / Remove Other Columns

Loading unnecessary columns into the model wastes memory and slows refresh. Remove any column that will never be used in a visual, relationship, or measure.

Pivot and Unpivot

Pivot and Unpivot are among the most powerful — and most confusing — transformations in Power Query. Understanding when to use each one is essential because real-world data frequently arrives in the wrong shape for Power BI's column-based model.

Unpivot — Wide to Tall

A wide table has one column per time period or category — common in Excel budgets and financial exports. Power BI needs tall tables — one row per measurement. Unpivot converts wide to tall automatically.

Pivot — Tall to Wide

Pivot is the reverse — it takes unique values from a column and turns them into new column headers, aggregating the corresponding values. Use it to reshape a tall summary table into a cross-tab format for display, or to create lookup columns from a key-value pair table.

Transpose

Transpose rotates the entire table — rows become columns and columns become rows. It is used rarely but is essential when source data arrives with dates or categories as row headers instead of column headers.

Putting It All Together — Full Cleaning Sequence

Full Applied Steps to clean the raw Sales Report table:

Step 1 — Remove Top Rows (2)
  Removes "Sales Report 2024" and "Exported by Finance" rows
  M: Table.Skip(Source, 2)

Step 2 — Use First Row as Headers
  Promotes row 3 (OrderID, OrderDate...) to column names
  M: Table.PromoteHeaders(#"Removed Top Rows", ...)

Step 3 — Changed Type
  Sets OrderID → Int64, OrderDate → Date, Revenue → Decimal
  M: Table.TransformColumnTypes(...)

Step 4 — Remove Bottom Rows (1)
  Removes "End of report" footer row
  M: Table.RemoveLastN(#"Changed Type", 1)

Step 5 — Remove Blank Rows
  Removes any fully empty rows in the middle of the data
  M: Table.SelectRows(... each not List.IsEmpty(...))

Step 6 — Filter Rows — Status
  Keeps only Shipped, Delivered, Processing (excludes Cancelled)
  M: Table.SelectRows(... each [Status] <> "Cancelled")

Step 7 — Renamed Columns
  Renames any remaining generic names to clean descriptive names
  M: Table.RenameColumns(...)

Step 8 — Reordered Columns
  Puts ID columns first, dates second, measures last
  M: Table.ReorderColumns(...)

Practice

Lesson Quiz

Next up — Lesson 20 covers Grouping and Aggregation — how to use Group By to summarise rows, create running totals, and build aggregated summary tables directly inside Power Query without writing a single DAX measure.