Chapter 2 · 8 levels
Getting and shaping data in Power Query
Connect to Excel, SQL Server and web sources, then clean and shape the data with Applied Steps in the Power Query Editor: types, text clean-up, split and extract, conditional columns, unpivot, merge, append and group.
StartEvery report starts with data that isn’t ready. Prices arrive as text, city names have trailing spaces, the marketplace sends months as columns, and the numbers you need live in two tables. Power BI’s Power Query Editor is where you fix all of that once, as a list of recorded Applied Steps that replay on every refresh.
In this chapter you work in a practice editor built for this site. It is not Power Query and it runs no M code; it gives you Power Query’s way of working with the same step names and the same results: start from an imported table, add steps one at a time, watch the table change after each one. Every lesson gives the real path in Power BI Desktop (Home → Transform data) and the M code each step writes.
You will pick only the columns and rows a report needs from a SQL Server table, fix types and messy text, split codes, add conditional columns, unpivot a wide export, merge and append queries, and finish with a boss level that turns a raw export into a table ready to load into the model.
Levels
- Import only what the report needs
from Kevin Walsh, Head of Customer Service
power query, get data, sql server, choose columns, filter rows, applied steps
- Fix types and messy text in a customer export
from Brittany Hayes, Events Manager
power query, change type, trim, capitalize each word, uppercase, replace values, data quality
- Split a SKU into the parts you can join on
from Luis Ortega, Warehouse Supervisor
power query, duplicate column, split column, extract, text before delimiter, change type, rename column
- Band orders by size and add the month
from Nicole Turner, Business Accounts Manager
power query, conditional column, if then else, custom column, date parts, month name, change type
- Turn a month-per-column report into rows
from Thomas Whitaker, Head of Merchandising
power query, unpivot columns, unpivot other columns, fill down, tidy data, rename column
- Which join kind does the merge need?
from Sarah Mitchell, CRM Manager
power query, merge queries, join kind, left outer, inner join, left anti, full outer
- Merge in the category, then group the returns
from Angela Rivera, Head of Customer Support
power query, merge queries, expand, group by, count rows, sum, sort rows
- Boss: two messy exports, one clean table
from Laura Bennett, Head of Finance
power query, append queries, remove duplicates, change type, trim, capitalize each word, group by, close and apply