Please note javascript is required for full website functionality.
MVP

Blog

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!

Newsletter