Shaping Data with Power Query

A regional sales manager exports last month’s orders from the company’s ordering system. The file lands on her desktop, and it is a mess. The first three rows are a company logo header and a “Report generated on…” line, so the real column titles sit down in row 4. Customer names were typed by hand over the years, so “Acme Inc,” “ACME, INC.,” and “acme inc” all show up as if they were three different companies. A handful of rows are completely blank. The order dates look like dates, but the software treats them as plain text, so she cannot sort by them. The amounts have stray spaces that break the totals.

She needs a clean version by Monday, and she will need it again next month, and the month after that. Doing it by hand in a spreadsheet takes an hour and introduces its own typos. This chapter is about the tool inside Power BI that does the cleanup for you, remembers exactly what you did, and re-does it in one click every time fresh data arrives.

Why this matters

Real business data almost never arrives clean. It comes out of accounting systems, point-of-sale tools, web forms, and other people’s spreadsheets, each with its own quirks. Before that data can become a chart or a report, someone has to fix the headers, correct the types, and throw out the junk rows.

Power BI gives you a dedicated workshop for exactly that job. It is called Power Query, and it does two things that a spreadsheet cannot. First, it records every cleanup action you take as a numbered step. Second, it replays that whole list automatically whenever the data updates. You clean the mess once. After that, next month’s export gets cleaned the instant you load it.

Getting this right early saves you from a specific kind of pain later. If you skip the cleaning and try to patch problems inside your charts instead, every chart carries its own hidden fix, and the mess follows you through the entire report. Clean it once at the front door, and everything downstream stays simple.

Opening the workshop: Transform data

In Power BI Desktop, after you connect to a file or database, you get a preview and two buttons: Load and Transform data. Load drops the data straight into your report as-is. Transform data opens the cleaning workshop instead. That button lives on the Home tab of the ribbon, and you can also reach it any time after loading.

Choosing Transform data opens a separate window called the Power Query Editor. Take a moment to learn its four areas, because you will live here.

+------------+-------------------------------+------------------+
|  Queries   |        Data preview           |  Query Settings  |
|  (left)    |        (center)               |  (right)         |
|            |                               |                  |
| Orders     |  a preview of your table,     |  Properties      |
| Customers  |  rows and columns you can     |  APPLIED STEPS   |
| Products   |  click and reshape            |   1. Source      |
|            |                               |   2. Changed Type|
+------------+-------------------------------+------------------+
       ^ the ribbon runs across the top: Home, Transform, Add Column, View

On the left, the Queries pane lists each table you are shaping. A query is simply the saved recipe for one table: where it comes from and every change you make to it. In the center is a preview of the data. On the right is the Query Settings pane, and inside it the part that makes Power Query special: Applied Steps.

The big idea: steps that re-run themselves

Every action you take gets written down as a step in the Applied Steps list. Change a column’s type, and a step called “Changed Type” appears. Filter out blank rows, and a “Filtered Rows” step appears under it. The list reads top to bottom like a recipe: start with the raw data, then do this, then this, then this.

You can click any step to see what the table looked like at that moment. You can delete a step by selecting the X next to it, and you can rename a step to something meaningful like “Removed Header Junk.” This is your undo history and your documentation in one place.

Here is why it matters for a real job. When new data arrives and you press Refresh, Power Query does not ask you to clean it again. It pulls the fresh data and runs your entire step list against it, from top to bottom, exactly as recorded.

So the sales manager’s hour of monthly cleanup becomes a one-time investment. She builds the steps once. Every future export flows through the same steps automatically. That is the whole promise of this chapter.

The everyday cleanup toolkit

Most cleaning work uses a small set of transforms. A transform is just one change to your data. Here are the ones you will use constantly, with where to find each. You do not need to memorize the ribbon; right-clicking a column usually offers the same commands.

Promote the headers. When the real column titles are stuck in a data row, select Use First Row as Headers (also called promote headers) on the Home or Transform tab. The top row of data becomes your column names.

Fix the data types. A data type tells Power BI what a column holds: text, a whole number, a decimal, a date. Dates stored as text cannot be sorted or used in a timeline, so this matters. Select a column, then use the Data type dropdown on the Home tab, or click the small type icon on the left of the column heading, and pick the correct type.

Remove columns you do not need. Select a column and choose Remove Columns on the Home tab, or right-click the column and remove it. Fewer columns means a lighter, faster report.

Rename and reorder columns. Double-click a column heading to rename it in plain language, so “Col3” becomes “Order Date.” Drag a column heading left or right to reorder it.

Split a column. When one column crams two things together, like “Smith, John” or “Red - Large,” select the column and use Split Column on the Home tab (Transform group) or the Transform tab. Split Column by Delimiter breaks it apart at a chosen character such as a comma, space, or dash.

Duplicate a column. Right-click a column and choose Duplicate Column when you want to keep the original and experiment on a copy.

Remove blank and duplicate rows. On the Home tab, in the Reduce Rows group, open Remove Rows. Remove Blank Rows clears out empty rows, and Remove Duplicates keeps one copy of each repeated row.

Filter rows. Click the small triangle in a column heading to open the filter menu, then keep only the values you want, the same way you filter a spreadsheet. Filter a “Status” column to “Completed,” for example, and the rest drop out.

Replace values. To fix a recurring wrong value, right-click the column and choose Replace Values, or find it on the Home tab. This is how you turn every “N/A” into a blank, or every “acme inc” into “Acme Inc.”

Trim and clean text. Extra spaces and hidden characters are a classic cause of totals that do not match and duplicates that should have matched. On the Transform tab, use Format, then Trim to strip leading and trailing spaces, and Clean to remove non-printing characters.

Add a column that calculates. When you need a value the source did not include, add one. Custom Column, on the Add Column tab, builds a new column from a small formula, such as Quantity times Unit Price for a line total. Conditional Column, also on the Add Column tab, builds a column from plain if-this-then-that rules with no formula writing, such as labeling any order above 1,000 as “Large.”

Try It Build a small mess and clean it. In a spreadsheet, create a file with three junk rows at the top (a title, a blank row, a “generated on” line), then a header row reading Name, Region, Amount, then a few data rows. Type one customer name three ways (“Globex”, “GLOBEX”, “globex “), leave one row blank, and format the Amount column as text.

Now in Power BI Desktop: on the Home tab select Get data, choose Excel workbook or Text/CSV, pick your file, and in the preview select Transform data.

In the Power Query Editor, do these clicks in order and watch the Applied Steps list grow:

On the Home tab, open Remove Rows and choose Remove Top Rows, enter 3, select OK.

On the Transform tab, select Use First Row as Headers.

Select the Name column. On the Transform tab, open Format and choose Trim.

Right-click the Name column, choose Replace Values, and replace “GLOBEX” and “globex” with “Globex” (do it twice, once for each).

On the Home tab, open Remove Rows and choose Remove Blank Rows.

Click the type icon on the Amount column and set it to Decimal Number.

Now click the earlier steps one by one and watch the preview rewind. You just built a repeatable recipe. What did you notice about the order of the steps mattering?

Combining tables: merge versus append

Two of the most useful transforms bring separate tables together. They sound similar and do opposite jobs, so it helps to picture them.

Append stacks tables on top of each other, adding rows. Reach for it when you have the same kind of data split across several files: January orders, February orders, March orders, each with identical columns. Appending them produces one long table of all orders. You find it on the Home tab in the Combine group under Append Queries. If the columns do not line up perfectly, Power Query keeps all of them and fills the gaps with blanks.

Append (stack rows):

  Jan orders          Feb orders           Result
  +---------+         +---------+          +---------+
  | 3 rows  |    +    | 4 rows  |    =     | 7 rows  |
  +---------+         +---------+          +---------+

Merge matches two tables side by side, adding columns. This is called a join in data work. Reach for it when one table has part of the story and another table has the rest, connected by a shared value. Your Orders table has a CustomerID on every row; your Customers table has the CustomerID plus the company name, city, and account manager. Merging on CustomerID pulls those customer details next to each order. You find it on the Home tab under Merge Queries.

Merge (match columns by a shared key):

  Orders                 Customers                Result
  OrderID | CustID       CustID | Company         OrderID | CustID | Company
  --------+-------  +    -------+---------   =     --------+--------+--------

When you merge, Power Query asks which join kind to use. The join kind decides which rows survive the match. In plain language:

Left outer keeps every row from your main table and adds matching details from the second. This is the everyday default: keep all my orders, attach whatever customer info I can find.
Inner keeps only rows that have a match in both tables. Use it to keep only orders whose customer still exists in the customer list.
Full outer keeps every row from both tables, matched where possible.
Left anti keeps only the rows from your main table that found no match. This is a quiet workhorse for spotting problems: orders pointing at a customer who is missing from the customer list.

Start with left outer unless you have a reason not to. It is the safe choice that keeps your main data intact.

In business

A store operations analyst gets three exports every Monday: last week’s transactions from the register system, a store directory with each store’s region and manager, and a returns file. Her goal is one clean table she can chart: sales by region, with returns netted out.

Her Power Query recipe, built once, looks like this. She loads all three files with Transform data. On the transactions query she removes the register’s junk header rows, promotes the real headers, sets the date column to a Date type, trims the product names, and filters out the training-mode test transactions. She appends any additional store files so every store sits in one transactions table. Then she merges that table with the store directory on Store ID using a left outer join, which pulls each store’s region and manager onto every transaction. Finally she merges the returns file with a left anti join to surface any return that has no matching original sale.

Every step lands in the Applied Steps list. The following Monday she drops in the new exports and presses Refresh. The recipe re-runs on the fresh files, and her clean, combined table is ready before her coffee is. The work lives in the steps, not in her memory.

Common pitfalls

Cleaning inside your charts instead of in Power Query. It is tempting to leave the mess and try to work around it later while building visuals. That way lies pain. Every chart ends up compensating for the same bad data, the fixes contradict each other, and no one can find where a number really comes from. Clean once in Power Query, at the front door, and every visual downstream inherits clean data for free.

Forgetting to fix data types. A date left as text will not sort, will not build a timeline, and will not roll up by month. A number left as text will not add up. Setting types is not busywork; it is what unlocks nearly everything you will do later. Do it early, and do it deliberately.

Deleting a source column your steps depend on. Because the steps re-run in order, a later step can break if an earlier column it referenced is gone. If a step suddenly shows an error after you change something upstream, look at what that step expects and whether a rename or removal above it pulled the rug out. This is the price of repeatability, and it is a fair one.

Cleaning what should have been fixed at the source. Power Query is excellent, but the very best cleanup is the cleanup you never have to do. If the reason names arrive three different ways is a data-entry form with no dropdown, fixing that form helps every report forever. When you have the access and the influence, transforming the data at its source beats transforming it in Power Query. When you do not, Power Query is your reliable second best.

Reaching for formulas too soon. You can build a lot with the buttons and the Applied Steps alone, and you should. The heavier calculation language, DAX, comes later in this track. For now, keep your cleaning inside Power Query’s menus.

Practice

Take a real export from your own work, something with visible flaws: a report with title rows on top, inconsistent text, blank rows, or dates trapped as text. Do not chart it yet. Just clean it.

Load it with Transform data, then build the steps to make it presentable: promote the headers, set every column to its correct type, trim the text columns, remove the blank rows, and rename the columns in plain language. Watch the Applied Steps list fill in as you go. When you finish, on the Home tab select Close & Apply to save the recipe and send your clean table into the report.

If you have two related files, try one merge and one append so both live in your fingers. Then imagine next month’s version of the file arriving and pressing Refresh. That mental picture is the point of everything you just built.

With clean, well-typed tables in hand, you are ready for the next question: how do separate tables relate to each other so a single chart can pull from all of them? That is data modeling, and it is where Chapter 4 begins.

Part of the Power BI track. Developed with AI assistance and reviewed by a human editor. Power BI changes often; menu names, features, and prices may have shifted since the “checked on” dates in this chapter. Verify anything critical against Microsoft’s official documentation.