Prepare the data · Topic 6 of 8
Grouping, Pivoting and Semi Structured Data
PL-300 reshaping data: group and aggregate rows, pivot, unpivot and transpose tables, and convert JSON and XML into tables in Power Query.
- 4 min
- Group By offers Sum, Average, Median, Min, Max, Count Rows, Count Distinct Rows and All Rows.
- Unpivot Other Columns copes with new columns in future refreshes. Unpivot Selected Columns does not.
- Unpivot gives Attribute and Value columns. Pivot needs a values column and an aggregate.
What it is
Source data often has the wrong shape. A report may list months across the columns, a log may hold one row per event when you need one per day, and a web service may return JSON instead of a table. Power Query has commands to group and aggregate rows, to pivot, unpivot and transpose, and to convert semi structured data such as JSON and XML into a normal table.
How it works
Group By
Group By collapses many rows into one per group and computes a result.
- Basic groups by one column and applies one operation.
- Advanced groups by several columns and adds several aggregations.
| Operation | Result |
|---|---|
| Sum, Average, Median, Min, Max | The numeric aggregate |
| Count Rows | Number of rows in the group |
| Count Distinct Rows | Number of different rows |
| All Rows | A nested table you can expand later |
Group in Power Query to reduce granularity and shrink the model, for example from one row per transaction to one row per day and product. Group only when you will never need the detail. If you need both, keep the detail and aggregate with DAX measures.
Pivot
Pivot Column turns the values of one column into new column headers. Choose the column to pivot, then the Values Column and an aggregate function (Sum, Count, Don't Aggregate and so on). Use Don't Aggregate for text. Pivot is rare in star schema preparation, but useful for turning an attribute and value list into one column per attribute.
Unpivot
Unpivot does the opposite. It turns columns into rows, producing two columns, by default called Attribute and Value.
| Command | Effect |
|---|---|
| Unpivot Columns | Unpivots the columns you selected |
| Unpivot Other Columns | Unpivots every column except those selected |
| Unpivot Only Selected Columns | Unpivots exactly the selection, and ignores new columns later |
Choose Unpivot Other Columns when new months will be added as columns in future files, because new columns will be unpivoted too. This is the standard fix for a table with one column per month or per year.
Transpose
Transpose swaps rows and columns. It is used on tables where headers run down the first column. After transposing, the old headers are in the first row, so use Use First Row as Headers, and then check the data types, because transpose turns everything into text or a single type.
Semi structured data
JSON and XML store nested data. Power Query reads them in steps.
- Connect to the file or web service. For JSON you get a List or Record.
- If it is a list, use To Table to turn it into a one column table.
- Select the expand icon on the column header to expand a record into columns, or to expand a list to new rows.
- Repeat for each nested level, and set data types last.
For a text column that contains JSON or XML, use Transform then Parse, and choose JSON or XML. This creates a record or table you can expand. You can also combine files from a folder when each file has the same JSON structure, because Power Query builds a helper query and a sample file for you.
| Value type | What it contains | How to flatten |
|---|---|---|
| Record | Named fields | Expand to columns |
| List | Several values | Expand to new rows |
| Table | Rows and columns | Expand to columns |
A worked scenario
A regional report has Sales for Jan, Feb and Mar in three columns. The analyst selects Region and Product, then Unpivot Other Columns, renames Attribute to Month and Value to Sales, and sets the types. Next month a new April column appears, and the refresh handles it without changes. She then calls a web service that returns orders as JSON, with a nested list of line items. She expands the Orders record, then expands the Items list to new rows, which gives one row per line item.
What to remember for the exam
- Group By offers Sum, Average, Median, Min, Max, Count Rows, Count Distinct Rows and All Rows.
- Unpivot Other Columns copes with new columns in future refreshes. Unpivot Selected Columns does not.
- Unpivot gives Attribute and Value columns. Pivot needs a values column and an aggregate.
- Transpose swaps rows and columns, so promote headers and fix types afterwards.
- Expand a record to columns and a list to new rows. Parse converts JSON or XML text.
A table with a column per period is usually a request to unpivot.
How the exam asks about this
- "Months are separate columns, and more will be added." Unpivot Other Columns.
- "Reduce a table to one row per customer with total sales." Group By with Sum.
- "A column contains JSON text." Parse as JSON, then expand.
- "Each file in a folder has the same layout." Combine files.
Common mistakes
Grouping away detail. You cannot get it back in the model.
Unpivoting selected columns. New columns are not included in later refreshes.
Forgetting to promote headers after transposing.
Expanding a list to columns. Lists expand to rows.
Check yourself
Which unpivot command handles new columns added later?
Unpivot Other Columns.
How do you flatten a JSON list?
Expand it to new rows.
What does Group By with All Rows return?
A nested table for each group.
Topic 6 of 8 in Prepare the data
Test yourself on Prepare the data
The full Microsoft PL-300 question bank has 657 questions, with timed practice exams and an explanation for every answer.
This study guide is independent and is not affiliated with, endorsed by, or sponsored by Microsoft. Vendor names, certification names and exam codes are trademarks of their respective owners and are used for identification only.