Power Query: See it, Save it, Sort it - Part 6
22 June 2022
Welcome to our Power Query blog. This week, I group my data.
In Power Query: See it, Save it, Sort it – Part 1, I started with some data for my imaginary salespeople:
and extracted it into Power Query, in order to perform some transformations.
Last week, I merged my query with an earlier version of the same query to filter my data.
This time, I want to summarise my data so that I have one row per day. To do this, I use the ‘Group by’ functionality, which is on the Home tab:
This brings up a dialog:
I will need to create more than one column when I group the data, so I choose the Advanced options:
I’d like to record the names of the salespeople that contributed to the sales on each date, but there is no option to append the names together. I am going to choose to Sum the Name column!
Power Query can’t do this, so the Names column has errors:
However, I am going to adjust the M code. Currently, the M code for the ‘Grouped Rows’ step is:
= Table.Group(#"Removed Columns", {"Date"}, {{"Amount", each List.Sum([Amount]), type nullable number}, {"Names", each List.Sum([Name]), type nullable text}})
Currently, I have the following code to create the Names column:
each List.Sum([Name])
I want to change this so that it appends the text instead, so I am going to use the Text.Combine() function instead:
each Text.Combine([Name], ", ")
The second argument here is the separator(, ). The M code for the full step is now:
= Table.Group(#"Removed Columns", {"Date"}, {{"Amount", each List.Sum([Amount]), type nullable number}, {"Names", each Text.Combine([Name], ", "), type nullable text}})
This gives me the Names column without any errors:
However, I have values for Names with repeating names, e.g. ‘Paul, Paul, John, John, John, Paul, Paul, Paul, Paul’.
Next time, I’ll modify this step again to only show each name once.
Come back next time for more ways to use Power Query!
