Power Platform Power Query Data Cleaning

Raw data arrives messy. Dates hide inside text, names carry extra spaces, and files from different offices use different layouts. Power Query cleans all of it before a single chart appears.

What Power Query Does

Power Query is the data preparation engine inside Power BI. It connects to a source, applies a list of transformation steps, and loads the clean result into the model. Every step is recorded, so a refresh repeats the whole cleaning routine automatically.

 SOURCES                POWER QUERY                  MODEL
+-----------+        +------------------+        +-----------+
| Excel     |        | Remove blanks    |        |           |
| SQL       | -----> | Fix data types   | -----> | Clean     |
| SharePoint|        | Split columns    |        | tables    |
| Web       |        | Merge queries    |        |           |
+-----------+        +------------------+        +-----------+

Opening the Editor

Select Transform data on the Home ribbon. The editor shows a query list on the left, a data preview in the center, and the Applied Steps list on the right. Click any step to see the data as it looked at that moment.

Everyday Transformations

Change Data Types

Set each column to the right type: text, whole number, decimal, date, or true/false. Correct types unlock sorting, math, and date functions later.

Remove Columns and Rows

Delete columns the report never uses. Remove blank rows, duplicate rows, and the top rows that hold titles instead of data.

Replace and Trim

Replace Values fixes spelling variants such as N.Y. and New York. Trim removes extra spaces that break matching, and Clean strips invisible characters.

Split and Merge Columns

Split a full name into first and last name by delimiter. Merge two columns, such as city and state, into one location column.

Fill Down

Exported reports often leave repeated labels blank. Fill Down copies the value above into the empty cells below.

Combining Data

Append Queries

Append stacks tables with the same columns. Twelve monthly files become one yearly table.

Merge Queries

Merge joins two tables on a shared column, similar to a lookup. An Orders table gains a Customer Name column by matching CustomerID against the Customers table.

Orders                     Customers
| OrderID | CustomerID |   | CustomerID | Name  |
|  1001   |    C7      |   |    C7      | Meera |
|  1002   |    C9      |   |    C9      | Dev   |
          \                    /
           +--- Merge on CustomerID ---+
| OrderID | CustomerID | Name  |
|  1001   |    C7      | Meera |
|  1002   |    C9      | Dev   |

Unpivot Columns

Spreadsheets often place one month per column. Charts prefer one row per month. Unpivot rotates the month columns into two columns named Attribute and Value, and the table becomes chart-ready.

Group By and Custom Columns

Group By summarizes rows, such as total sales per product. Custom columns add new fields with simple formulas, and Column From Examples learns a pattern when you type a few sample results.

The M Language

Every click writes a line of M code behind the scenes. Open the Advanced Editor to read it.

let
  Source = Excel.Workbook(File.Contents("sales.xlsx")),
  Sheet  = Source{[Item="Data"]}[Data],
  Typed  = Table.TransformColumnTypes(Sheet, {{"Amount", type number}}),
  Clean  = Table.SelectRows(Typed, each [Amount] <> null)
in
  Clean

Layman Example: Washing Vegetables

A cook washes, peels, and chops vegetables before cooking. Nobody boils muddy carrots. Power Query plays the role of the kitchen prep station. The report chef receives tidy ingredients, and the final dish tastes the same every day because the prep routine repeats exactly.

Good Habits

• Rename steps with meaningful names so teammates follow the story.
• Remove unneeded columns early to speed up refresh.
• Keep source queries separate from calculated queries.
• Prefer transformations that support query folding, which pushes work to the database.
• Document unusual rules in a query description.

Refreshing Clean Data

After publishing the report to the Power BI service, schedule a refresh so the cleaning steps run automatically each day. A gateway connects the service to data that lives on a local network. Check the refresh history for failures, and fix broken steps quickly because a changed column name in the source can stop the whole query.

Key Takeaways

Power Query records each cleaning step and replays it on every refresh. Clean data at the source of the pipeline and the entire report stays reliable.