Latest value in sub groups

[sv12]

Hi, 

I am trying to get the most recent record (based on "Created on" column) for each sub group (based on "Opportunity ID") in Power Query.

I would greatly appreceate any help! Thanks

Sample Data:

Opportunity ID	Advisor Name	Prospect Name	Account Value	Stage	Created On
1	Tony Stark	Hulk	$100	Proposal Sent	1/1/2020
1	Tony Stark	Hulk	$100	Proposal Reviewed with Advisor	1/10/2020
2	Tony Stark	Thor	$500	Proposal Sent	1/1/2020
2	Tony Stark	Thor	$500	Proposal Reviewed with Advisor	1/20/2020
2	Tony Stark	Thor	$500	Case Lost	2/1/2020
3	Tony Stark	Batman	$800	Proposal Sent	2/1/2020
3	Tony Stark	Batman	$800	Proposal Reviewed with Advisor	3/1/2020
3	Tony Stark	Batman	$800	Case Won	3/2/2020

[ChrisMendoza · Accepted solution]

@sv12 -

Using https://excelgorilla.com/power-bi/power-query/reach-underlying-rows-using-table-max/ as a template.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQrJz6tUCC5JLMoGcjxKc0CUiqGBAZAKKMovyC9OzFEITs0rAfIN9Q31jQyMDJRidYjXG5RalplanpqiUJ5ZkqHgmFKWWZxfpAAxzQBhnBG6cSEZ+UUg40wJO4VYvXicYkS8U5wTi1MVfPKLQc4wQnaGMbo+p8SS3MQ8kE4LrJ4gUzdObxiTZB7YH+H5eWCNRlCNsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Opportunity ID" = _t, #"Advisor Name" = _t, #"Prospect Name" = _t, #"Account Value" = _t, Stage = _t, #"Created On" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Opportunity ID", Int64.Type}, {"Advisor Name", type text}, {"Prospect Name", type text}, {"Account Value", Currency.Type}, {"Stage", type text}, {"Created On", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Opportunity ID"}, {{"Grouped", each _, type table [Opportunity ID=number, Advisor Name=text, Prospect Name=text, Account Value=number, Stage=text, Created On=date]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.Max([Grouped],"Created On")),
    #"Expanded Custom" = Table.ExpandRecordColumn(#"Added Custom", "Custom", {"Advisor Name", "Prospect Name", "Account Value", "Stage", "Created On"}, {"Advisor Name", "Prospect Name", "Account Value", "Stage", "Created On"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Grouped"})
in
    #"Removed Columns"

[BA_Pete · Accepted solution]

Hi @sv12 

I've completed this in Power Query as follows:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQrJz6tUCC5JLMoGcjxKc0CUiqGBAZAKKMovyC9OzFEITs0rAfIN9Q31jQyMDJRidYjXG5RalplanpqiUJ5ZkqHgmFKWWZxfpAAxzQBhnBG6cSEZ+UUg40wJO4VYvXicYkS8U5wTi1MVfPKLQc4wQnaGMbo+p8SS3MQ8kE4LrJ4gUzdObxiTZB7YH+H5eWCNRlCNsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Opportunity ID" = _t, #"Advisor Name" = _t, #"Prospect Name" = _t, #"Account Value" = _t, Stage = _t, #"Created On" = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source,"$","",Replacer.ReplaceText,{"Account Value"}),
    chgTypeUSDate = Table.TransformColumnTypes(#"Replaced Value", {{"Created On", type date}}, "en-US"),
    chgTypeAll = Table.TransformColumnTypes(chgTypeUSDate,{{"Opportunity ID", type text}, {"Advisor Name", type text}, {"Prospect Name", type text}, {"Account Value", Currency.Type}, {"Stage", type text}, {"Created On", type date}}),
    groupByOppID = Table.Group(chgTypeAll, {"Opportunity ID"}, {{"data", each _, type table [Opportunity ID=text, Advisor Name=text, Prospect Name=text, Account Value=number, Stage=text, Created On=date]}}),
    addMaxDateCol = Table.AddColumn(groupByOppID, "maxDate", each Table.Max([data], "Created On")),
    expandMaxDateCol = Table.ExpandRecordColumn(addMaxDateCol, "maxDate", {"Advisor Name", "Prospect Name", "Account Value", "Stage", "Created On"}, {"Advisor Name", "Prospect Name", "Account Value", "Stage", "Created On"}),
    remDataCol = Table.RemoveColumns(expandMaxDateCol,{"data"}),
    chgTypAll2 = Table.TransformColumnTypes(remDataCol,{{"Advisor Name", type text}, {"Prospect Name", type text}, {"Account Value", Currency.Type}, {"Stage", type text}, {"Created On", type date}})
in
    chgTypAll2

Paste this into a blank query using Advanced Editor to follow the steps I took.

I get the following output (NB, UK date format!)

Pete

[ChrisMendoza · Accepted solution]

@sv12 -

Using https://excelgorilla.com/power-bi/power-query/reach-underlying-rows-using-table-max/ as a template.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQrJz6tUCC5JLMoGcjxKc0CUiqGBAZAKKMovyC9OzFEITs0rAfIN9Q31jQyMDJRidYjXG5RalplanpqiUJ5ZkqHgmFKWWZxfpAAxzQBhnBG6cSEZ+UUg40wJO4VYvXicYkS8U5wTi1MVfPKLQc4wQnaGMbo+p8SS3MQ8kE4LrJ4gUzdObxiTZB7YH+H5eWCNRlCNsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Opportunity ID" = _t, #"Advisor Name" = _t, #"Prospect Name" = _t, #"Account Value" = _t, Stage = _t, #"Created On" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Opportunity ID", Int64.Type}, {"Advisor Name", type text}, {"Prospect Name", type text}, {"Account Value", Currency.Type}, {"Stage", type text}, {"Created On", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Opportunity ID"}, {{"Grouped", each _, type table [Opportunity ID=number, Advisor Name=text, Prospect Name=text, Account Value=number, Stage=text, Created On=date]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.Max([Grouped],"Created On")),
    #"Expanded Custom" = Table.ExpandRecordColumn(#"Added Custom", "Custom", {"Advisor Name", "Prospect Name", "Account Value", "Stage", "Created On"}, {"Advisor Name", "Prospect Name", "Account Value", "Stage", "Created On"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Grouped"})
in
    #"Removed Columns"