Power Query Skills Every Power BI Developer Should Have - Group By, Pivot, Custom Columns

I've reviewed a lot of Power BI reports over the years and one thing keeps showing up. People build complex DAX measures to do work that Power Query could have done in five clicks. The result is reports that load slowly, calculate slowly, and break in ways that are hard to debug.

If you're going to invest in being good at one thing in Power BI, make it Power Query. The transformations you do at the query layer are faster, more reliable, and easier to maintain than equivalent work in DAX. They also compose well, which means once you've built a good query you can reuse the pattern across reports.

This is a practical guide to the Power Query tasks we use on almost every project we do. It's a tour of what Microsoft's docs cover under "common query tasks," with the consulting view layered on top - what works in the real world, what to avoid, and which patterns will save you the most pain.

Connect to Data Without Painting Yourself Into a Corner

The first decision you make on any Power BI project is how to connect to your data. Get this wrong and you'll be untangling it for months.

Power BI Desktop gives you a Get Data button with the usual suspects - Excel, SQL Server, Web, SharePoint, plus dozens of cloud connectors. The trap is that the easiest connector for development isn't always the right one for production.

Excel files on someone's laptop are the classic mistake. They work great until that person goes on leave or moves teams. We've inherited too many reports where the entire data pipeline depends on a spreadsheet sitting on a personal OneDrive. The fix is to move source data into something governed - SharePoint Online with proper permissions, an Azure SQL database, or Microsoft Fabric.

For web data, the From Web option is genuinely useful for one-off enrichment - looking up postcodes, scraping public datasets, pulling in reference data. Don't use it for production data sources. Public web pages change structure without warning and your report breaks silently.

For SQL Server and other databases, the question to ask is: import or DirectQuery? Import is faster to query but the data is stale until refresh. DirectQuery is always fresh but slower and limited in what transformations you can do. For most Australian mid-market clients we recommend import with scheduled refresh, often every 30 minutes or hourly, unless there's a genuine real-time requirement.

When you start a new connection, the Power Query Editor opens with a preview. The Transform Data button is the right entry point - don't load straight into the model and clean up later. Always shape before loading. It makes everything downstream simpler.

For more on architecting this end to end, our Power BI consultants page goes through the patterns we use.

Shape and Combine Data Without Hardcoding Everything

This is where the work happens. Most of what Power Query does is make data look the way you need it to look before it goes into the model.

The basics - removing columns, splitting columns, replacing values, changing data types - are all available through the ribbon. They also work through right-click context menus, which is faster once you know the layout.

Two practical tips that aren't obvious:

Set your data types explicitly, every time. Power Query tries to detect types automatically. It's right most of the time and wrong just often enough to cause production issues. We've had reports break because a column that was a number for the first 1000 rows turned out to have a text value at row 1247 and the auto-detection didn't catch it. Set the types yourself.

Name your applied steps. The Applied Steps panel on the right is your query's history. By default Power Query gives steps generic names like "Removed Columns" and "Changed Type." When you have 30 steps in a query, those names are useless. Rename them to describe what you're actually doing - "Removed PII columns," "Cleaned phone numbers." Future you will thank you.

The shaping work is also where you should be defensive. Anywhere you do a transformation that depends on a specific value or column existing, write a comment in the M code explaining what assumption you're making. Power Query is fragile to source changes. The more your transformations are explicit, the easier the recovery when something upstream moves.

Group Rows for Aggregation Before It Gets to DAX

Grouping rows is where most Power BI developers leave money on the table. People build sum measures in DAX when they should be aggregating in Power Query.

The Microsoft example is a good one: counting agencies per state. Select the State column, hit Group By, and you've got a count by state. The dialogue gives you Sum, Median, Count Distinct Rows, and a few others.

When to aggregate in Power Query versus DAX comes down to a few questions:

Will you ever need the row-level detail? If yes, keep the detail and aggregate in DAX. If no, aggregate in Power Query and save the memory.

Is this a static aggregation? If the granularity is fixed for the report - say, monthly sales totals that never need to drill into individual transactions - aggregate in Power Query. You'll get a faster model with less data scanned.

Are you joining aggregates together? If you need to count records from one table and join those counts to another, doing the count in Power Query and then merging is usually cleaner than doing it through relationships and measures.

The Advanced option in Group By is worth knowing about. It lets you group on multiple columns and create multiple aggregations in one step. For anything more than a simple count, use Advanced.

One trap to watch: when you group, you lose any columns not explicitly included in the group keys or aggregations. This is by design but it surprises people. If you need to keep additional columns, add them as aggregations using First or Max.

Pivot Columns for Wide Tables That Power BI Actually Wants

Power BI likes long, narrow tables. But sometimes your source data is the opposite - or you need to make it wide for a specific visualisation. Pivot Column is the tool.

Microsoft's example is counting products per category. Select the CategoryName column, hit Pivot Column, and you get a new table with one column per category.

In practice we use Pivot Column less than you might think. The Power BI matrix visual handles most "show me this as a wide table" use cases without needing to pivot the underlying data. Where pivoting genuinely helps is when you need the wide format for downstream calculations or for export to a system that requires it.

The opposite operation - Unpivot Columns - is the one we use constantly. Most spreadsheet exports come in wide format with one column per month or per category. Power BI wants this in long format. Unpivoting is usually the first thing we do to any Excel source.

If you're working with Power Query daily, get fluent with both. They're inverses and you'll use them in pairs.

For deeper work on report architecture, our business intelligence services cover the full design pattern.

Custom Columns - Where Power Query Gets Interesting

The Custom Column dialogue lets you write M expressions that operate across rows. This is where Power Query goes from a data cleaner to a proper transformation engine.

The Microsoft example creates a Percent ELL column - a percentage calculation that combines two existing columns. Simple stuff but the pattern extends to almost anything.

Where custom columns become genuinely powerful is in conditional logic. The Conditional Column dialogue gives you a UI for IF-THEN-ELSE expressions without writing M directly. For anything beyond two or three conditions, write the M yourself - the UI gets clunky fast.

A few patterns we use constantly:

Boolean flags for filtering. Create a column like IsActive = [Status] = "Active". Then filtering in DAX or downstream queries is a single condition instead of a string match. Smaller, faster, less error-prone.

Concatenation for unique keys. When you have a composite key spread across multiple columns, concatenate them in Power Query: [CustomerID] & "-" & [OrderID]. Use the result as your primary key in the data model. Far better than trying to do this in DAX.

Date bucketing. Power Query has surprisingly good date functions. Date.IsInPreviousNDays([OrderDate], 30) gives you a boolean flag for recent orders. Use this for any filtering logic that's based on rolling windows.

The thing not to do is reproduce DAX measures as custom columns. Custom columns are computed at refresh and stored in the model. DAX measures are computed on demand. If a calculation depends on filter context - say, "sales this year versus last year for the current selection" - it has to be DAX. Put it in a custom column and it'll just give you the same value every time.

What I'd Actually Practise

If you're newish to Power Query and want to get good at it, the order I'd recommend learning is:

1. Get fluent with data types and applied step naming. Boring but it pays off.
2. Get good at Unpivot Columns. You'll use it more than anything else.
3. Get comfortable with Group By, including the Advanced options.
4. Learn enough M to write custom columns with conditional logic. You don't need to be fluent, but you should be able to read other people's queries.
5. Learn merge queries (we didn't cover this above - it's the equivalent of a SQL JOIN and lives in the Home ribbon). This is where you combine data from multiple sources.

The teams that get this right end up with Power BI reports that perform well and are easy to hand off. The teams that don't end up with brittle DAX nightmares.

Our Microsoft AI consultants and Power BI team help organisations get their reporting layer in shape, often as a step before they start adding AI on top. There's no point bolting Copilot onto a report that's structurally broken. Fix the foundation first.

For Microsoft's full reference on these query tasks, see Perform common query tasks in Power BI Desktop.