Power Query: Mad Max Too
9 September 2020
Welcome to our Power Query blog. This week, I look at how to get the maximum example I used last week in just one step.
As I had last week, I have the following tent data:
Again, I wish to deduce the latest date for each supplier and the salesperson who contacted them. Last time, I achieved my result by using ‘Group By’. However, this took several steps to get to the desired result.
In the ‘Advanced Editor’, I can see the M code regarding how I achieved this outcome.
The M code is:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Supplier", type text}, {"Date", type datetime}, {"Salesperson", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Supplier"}, {{"Latest", each List.Max([Date]), type nullable datetime}, {"By", each _, type table [Supplier=nullable text, Date=nullable datetime, Salesperson=nullable text]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Contacted By", each Table.Max([By], "Date")),
#"Expanded Contacted By" = Table.ExpandRecordColumn(#"Added Custom", "Contacted By", {"Salesperson"}, {"Salesperson"})
in
#"Expanded Contacted By"
I am interested in the line where the column containing a table is created, i.e.
#"Grouped Rows" = Table.Group(#"Changed Type", {"Supplier"}, {{"Latest", each List.Max([Date]), type nullable datetime}, {"By", each _, type table [Supplier=nullable text, Date=nullable datetime, Salesperson=nullable text]}}),
I don’t need the table, but I do need to get the salesperson, so the bit I am interested in is:
{"By", each _, type table [Supplier=nullable text, Date=nullable datetime, Salesperson=nullable text]}
This section is creating a table for each row with the supplier, date and salesperson. I don’t need the table; I just need the salesperson associated with the maximum date in that table. I can use the Table.Max() M function to get this.
{"By", each Table.Max(_,”Date”), type table [Supplier=nullable text, Date=nullable datetime, Salesperson=nullable text]}
This gives me the maximum record. To get just the salesperson, I pass the column name as a parameter to Table.Max():
{"By", each Table.Max(_,”Date”)[Salesperson], type table [Supplier=nullable text, Date=nullable datetime, Salesperson=nullable text]}
In fact, I don’t need to format the table, so I can remove that part too.
{"By", each Table.Max(_,”Date”)[Salesperson], type text}
I can also remove the ‘Added Custom’ and ‘Expanded Contacted By’ steps as I don’t need them either.
The M code is now:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Supplier", type text}, {"Date", type datetime}, {"Salesperson", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Supplier"}, {{"Latest", each List.Max([Date]), type nullable datetime}, {"By", each Table.Max(_,"Date")[Salesperson], type text}})
In
#"Grouped Rows"
If I run this code, I get:
I have the latest date and the correct salesperson in one step. Howzat!?
Come back next time for more ways to use Power Query!
