Shaping and Combining Data in Power BI Desktop - A Practical Guide

One of the first things that catches new Power BI users off guard is the gap between connecting to a data source and actually having usable data. You connect to a SQL database or an Excel file, and the data arrives in Power BI looking nothing like what you need for your report. Columns have cryptic names. There are rows you don't want. The data you need is split across two different tables that need to be joined together.

This is where Power Query Editor comes in. It's the transformation layer inside Power BI Desktop, and getting comfortable with it is probably the single most important skill for anyone building reports seriously.

Why Shaping Data Matters More Than You Think

I've lost count of the number of projects where a client says "we just need a dashboard" and the actual work turns out to be 70% data preparation and 30% visualisation. The data is messy. It always is. Source systems weren't designed with your reporting needs in mind. They were designed for whatever operational purpose they serve.

A manufacturing client we worked with recently had product data in one system, sales data in another, and returns data in a third. Each system used different column names for the same thing - "ProductID" in one, "Product_Code" in another, "SKU" in the third. The actual report building took two days. Getting the data into the right shape took a week.

Power Query is where that shaping work happens, and doing it well means your reports are built on a solid foundation rather than a mess of workarounds.

Getting Into Power Query Editor

Open your .pbix file in Power BI Desktop and go to Home > Transform data. This opens Power Query Editor in a separate window.

On the left, you'll see the Queries pane listing all your data sources. On the right, the Query Settings pane shows the applied steps for whichever query you've selected. These steps are your transformation history - every change you make gets recorded as a step, and they execute in order every time you refresh the data.

This step-based approach is one of Power Query's best features. You can go back and edit any step, insert new steps between existing ones, or delete steps you no longer need. It's like version control for your data transformations. Not quite as sophisticated as actual version control, but for data preparation work, it's very good.

Shaping Data - The Basics That Matter

Renaming Columns

This sounds trivial but it matters. Your source systems use column names like "PROD_ID" or "CustName" or "dt_created". Your report consumers expect readable names like "Product ID" or "Customer Name" or "Date Created".

Right-click any column header and select Rename. Simple. Do this early in your transformation steps, before you start building other transformations that reference column names. It makes everything downstream easier to read and maintain.

A rule of thumb we use: rename columns to match your organisation's business glossary. If finance calls it "Revenue" and your source system calls it "TotalSalesAmount", rename it to "Revenue". Your DAX measures and report visuals will be much clearer.

Filtering Rows

Click the dropdown arrow on any column header to filter. You can exclude specific values, filter by condition (greater than, contains, etc.), or remove blank rows.

Filtering early in your transformation steps is good practice. If your source table has 10 million rows but you only need data for the Australian region, filter by Region early. Every subsequent step runs faster because it's working with less data. This is especially noticeable when you're iterating on transformations - each preview refresh is quicker.

Changing Data Types

Power Query tries to auto-detect data types when you first connect. It's right most of the time. When it's not, you get subtle problems downstream - a "number" column that's actually text won't work in arithmetic DAX measures, dates stored as text won't sort properly, and so on.

Check your data types early. Select a column, look at the type icon in the column header. If it's wrong, right-click and select Change Type. Common issues we see:

• Dates imported as text (especially from CSV files)
• Decimal numbers imported as whole numbers (losing precision)
• Codes like "001", "002" imported as numbers (stripping leading zeros)

Getting types right in Power Query saves debugging time later when your DAX formulas throw errors.

Combining Data - Merging Queries

This is where Power Query goes from useful to essential. Most real-world reports need data from multiple tables. You've got sales in one table and returns in another. Customer details here, transaction history there. You need to bring them together.

Power Query gives you two main options: Merge (joins) and Append (unions). The one you need depends on the relationship between your tables.

Merge Queries

Use Merge when you want to add columns from one table to another based on a matching key. This is conceptually the same as a SQL JOIN.

Go to Home > Merge Queries > Merge Queries as New. This creates a new query rather than modifying either source query, which is almost always what you want.

In the merge dialogue:

1. Select your first table (say, Sales)
2. Select your second table (say, Returns)
3. Click on the matching column in each table (Product ID in both)
4. Choose your join type

The join type matters. The default is Left Outer - all rows from the first table, matching rows from the second. This is right for most scenarios where you want your sales data with return information added where it exists.

After merging, you'll see a new column with a table icon. Click the expand button and choose which columns from the second table you want to include. Don't just select all - only bring in the columns you actually need. Extra columns bloat your model and slow things down.

Append Queries

Use Append when you have two tables with the same structure and you want to stack them on top of each other. Common example: you have sales data from 2024 in one file and 2025 in another. Same columns, different rows. Append puts them into a single table.

Go to Home > Append Queries > Append Queries as New. Select the tables to combine. Done.

The columns need to match by name. If one table has "Sales_Amount" and the other has "SalesAmount", Power Query treats these as different columns and you'll end up with two separate columns, each half-populated with nulls. Rename first, append second.

Applied Steps - Your Transformation History

Every action you take in Power Query creates a step in the Applied Steps list. These steps are recorded in the M language (Power Query's formula language) behind the scenes.

This is worth understanding because:

1. Steps are sequential. Later steps depend on earlier ones. If you delete a step in the middle, everything after it might break.
2. Steps are editable. Double-click any step to re-open its configuration. Changed your mind about which regions to filter? Find the filter step and modify it instead of adding another filter step.
3. Steps are readable. Click the gear icon next to any step to see what it does. For complex transformations, this is how you (or your colleague who inherits the report) understand what's happening.

I strongly recommend naming your steps descriptively. Right-click a step and select Rename. "Changed Type" tells you nothing six months later. "Set Product ID as integer, Date as date" tells you everything.

Close and Apply - The Final Step

When your transformations are done, hit Close and Apply on the Home tab. Power BI loads the transformed data into the model and you're ready to build visuals.

A word of caution here. If you're working with large datasets, Close and Apply can take a while. Power BI is executing every step in every query against the full dataset. During development, consider using "Keep Top Rows" to work with a smaller sample, then remove that step before publishing.

Common Mistakes We See

Not using "Merge as New". If you merge into an existing query instead of creating a new one, you modify the original query. This can break other things that depend on that query. Always merge as new unless you have a specific reason not to.

Too many transformations in Power Query. Power Query is great for shaping data. It's less great for complex calculations. If you find yourself writing elaborate custom columns with M formulas, consider whether that logic would be better as a DAX measure in the data model. Rule of thumb - row-level transformations in Power Query, aggregations and calculations in DAX.

Ignoring query folding. When connected to SQL databases, Power Query tries to convert your steps into SQL and execute them on the server (this is called query folding). Some steps break query folding, meaning Power Query downloads all the data and processes it locally. For large datasets, this is the difference between a 5-second refresh and a 5-minute refresh. Watch for the "View Native Query" option - if it's greyed out on a step, folding has broken.

Where This Fits in a Proper BI Setup

For small teams with a couple of reports, doing all your data shaping in Power BI Desktop is fine. But as your organisation scales its BI practice, you'll want to think about where transformation logic should live.

For our clients running Microsoft Fabric, we often recommend moving complex transformations into Fabric dataflows or data pipelines and keeping Power Query transformations minimal - just renaming and type casting. This separates the data engineering from the report development, which is cleaner and more maintainable.

If you're not at that scale yet, Power Query is perfectly capable of handling your transformations. Just keep your steps organised and documented so the next person who opens your file can understand what's happening.

We help Australian organisations set up their Power BI environments properly - from data modelling and Power Query best practices through to deployment and governance. If your team is spending more time fighting data than building reports, reach out and we can take a look at what's going wrong.

For the full step-by-step tutorial with sample data, see Microsoft's Shape and combine data guide.