Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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"
Proud to be a Super User!
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
Proud to be a Datanaut!
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
Proud to be a Datanaut!
@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"
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
104 | |
77 | |
67 | |
63 |
User | Count |
---|---|
144 | |
107 | |
105 | |
82 | |
69 |