Basic Transformations

Raw data from any source — a CSV export, a database table, a shared Excel file — almost never arrives clean. Before you build a single visual you need to shape it: remove the junk, fix the structure, and make every column mean exactly one thing. This lesson covers every everyday transformation you will use in Power Query Editor.

The Raw Dataset We Will Clean

Throughout this lesson we will use a single sales export that has all the classic real-world problems. Every transformation below fixes a specific issue in this table.

Step 1 — Use First Row as Headers

Sometimes Power BI reads the first row of data as a header row and names columns "Column1", "Column2" etc. This happens with CSV files that have no clear column name row, or when the data starts with a blank row. You fix it in one click.

Step 2 — Rename Columns

Column names in raw data are often inconsistent — lowercase, uppercase, with spaces, with special characters. Clean names make your DAX easier to write and your Data pane easier to read. The rule is simple: PascalCase, no spaces, descriptive but short.

To rename a column, double-click the column header in Power Query Editor and type the new name. Or right-click the header and choose Rename. Each rename adds a "Renamed Columns" step to Applied Steps.

Step 3 — Remove Blank Rows

Exported files commonly contain blank rows between data sections, at the bottom after a total row, or at the top before the headers. These blank rows load as null-filled rows in your model and distort row counts and aggregations.

Step 4 — Remove Duplicate Rows

Duplicates in a dataset cause every aggregation to be inflated. A revenue total double-counts orders that appear twice. Always check for duplicates before loading — especially in data that has been manually assembled or exported from a system that allows re-submissions.

Step 5 — Filter Rows

Filtering in Power Query Editor removes rows permanently from the query — unlike a report-level filter which just hides them. Use this when you genuinely do not need certain rows in your model at all, such as cancelled orders, test records, or data from years outside your analysis window.

Click the dropdown arrow on any column header to open the filter menu. Uncheck values to exclude them, or use Text Filters / Number Filters / Date Filters for condition-based filtering like "greater than $500" or "after 2023-01-01".

Step 6 — Remove Columns

Only load the columns your report actually needs. Every extra column increases model size, slows refresh, and clutters the Data pane. There are two approaches — remove the columns you do not want, or choose the columns you do want and remove all others.

Step 7 — Trim and Clean Text

Text columns from exported files almost always carry invisible baggage — leading spaces, trailing spaces, and non-printable characters embedded in the values. These cause silent failures: "North" and " North" look identical in a chart but group as two separate categories. A filter for "North" will miss " North" entirely.

Step 8 — Fix Inconsistent Casing

Our dataset has "North", "NORTH", and "north" all in the Region column. Power BI treats these as three different categories. A chart will show three separate bars for the same region. You fix this by standardising the case across the entire column.

To apply: select the column → Transform → Format → UPPERCASE / lowercase / Capitalize Each Word.

Step 9 — Replace Values

Replace Values is Power Query's find-and-replace. You use it to fix specific known problems — a currency symbol preventing a type change, a legacy code that needs renaming, or a status value with inconsistent spelling.

Common Replace Values scenarios:

SCENARIO 1: Remove $ and commas so Revenue can become a number
  Column: Revenue
  Find: $        Replace with: (nothing — leave blank)
  Find: ,        Replace with: (nothing)
  Then: Change type to Decimal Number

SCENARIO 2: Standardise status values
  Column: Status
  Find: shipped    Replace with: Shipped
  Find: DELIVERED  Replace with: Delivered

SCENARIO 3: Replace null with a default
  Column: Region
  Find: null       Replace with: Unknown

SCENARIO 4: Fix a legacy product code rename
  Column: ProductCode
  Find: LPT-OLD   Replace with: LPT-2024

How to do it:
  Right-click column header → Replace Values
  OR: Transform → Replace Values in the ribbon

Step 10 — Reorder Columns

Column order in Power Query controls the order columns appear in the Data pane and in Data View. This does not affect your DAX calculations but it makes your model easier to navigate. Put key columns first — IDs, dates, and the main measure column — then descriptive text columns.

To reorder, simply drag column headers left or right in Power Query Editor. Or select a column and use Transform → Move → To Beginning / To End / Before / After.

The Full Cleaning Sequence in Applied Steps

After applying all ten steps above, the Applied Steps panel shows the full cleaning history of the query. Every step is a recorded instruction that Power BI replays in order on every refresh. If you spot a mistake in step 4, you can click that step, fix it, and all later steps re-run from there automatically.

Applied Steps — orders_raw.csv (after full cleaning)

  Source                    ← connects to the CSV file
  Promoted Headers          ← Use First Row as Headers
  Changed Type              ← auto-detected types (may be wrong)
  Renamed Columns           ← Column1→OrderID, order date→OrderDate, etc.
  Removed Blank Rows        ← deleted all-null rows
  Removed Duplicates        ← deduplicated on OrderID
  Filtered Rows             ← removed Status = Cancelled, Status = Test
  Removed Columns           ← dropped columns not needed
  Trimmed Text              ← stripped leading/trailing spaces from Region
  Replaced Value            ← removed $ from Revenue
  Replaced Value1           ← removed , from Revenue
  Changed Type1             ← set Revenue to Decimal Number
  Replaced Value2           ← standardised Status casing
  Capitalized Each Word     ← fixed Region casing
  Reordered Columns         ← moved key columns first

Total steps: 15
Every step runs automatically on each refresh.

Practice

Lesson Quiz

Next up — Lesson 11 moves into Model View and shows you how to create relationships between your tables — what a relationship is, the difference between one-to-many and many-to-many, how to draw relationship lines correctly, and why getting your model structure right before building visuals makes everything else easier.