Forum Discussion
Pbiuserr
Post Prodigy
Post ProdigyPlease help with formula to add another argument
Hello, I perform such action - Ive managed to group ID by max date #"Grouped Rows" = Table.Group(#"Previous Step", {"ID"}, {{"NewTbl", each let t =_ in Table.SelectRows(t, each [CREATION ...
See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nQxVNJRMjIwMtI1MAQiIMfQwEApVge7lCmmlBG6LiO4lDG6LkwpuC5juJQJRMqYfKlYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"CREATION DATE" = _t, VALUE = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"CREATION DATE", type date}, {"VALUE", Int64.Type}}), #"Grouped Rows1" = Table.Group(#"Changed Type", {"ID", "CREATION DATE"}, {{"VALUE", each List.Max([VALUE]), type nullable number}}), #"Grouped Rows" = Table.Group(#"Grouped Rows1", {"ID"}, {{"MaxDate", each List.Max([CREATION DATE]), type nullable date}}), #"Merged Queries" = Table.NestedJoin(#"Grouped Rows", {"ID", "MaxDate"}, #"Grouped Rows1", {"ID", "CREATION DATE"}, "Grouped Rows", JoinKind.LeftOuter), #"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"VALUE"}, {"VALUE"}) in #"Expanded Grouped Rows"
Vijay_A_Verma
Most Valuable Professional
Most Valuable ProfessionalSee the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nQxVNJRMjIwMtI1MAQiIMfQwEApVge7lCmmlBG6LiO4lDG6LkwpuC5juJQJRMqYfKlYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"CREATION DATE" = _t, VALUE = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"CREATION DATE", type date}, {"VALUE", Int64.Type}}),
#"Grouped Rows1" = Table.Group(#"Changed Type", {"ID", "CREATION DATE"}, {{"VALUE", each List.Max([VALUE]), type nullable number}}),
#"Grouped Rows" = Table.Group(#"Grouped Rows1", {"ID"}, {{"MaxDate", each List.Max([CREATION DATE]), type nullable date}}),
#"Merged Queries" = Table.NestedJoin(#"Grouped Rows", {"ID", "MaxDate"}, #"Grouped Rows1", {"ID", "CREATION DATE"}, "Grouped Rows", JoinKind.LeftOuter),
#"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"VALUE"}, {"VALUE"})
in
#"Expanded Grouped Rows"
PbiuserrHi,
It works! May I ask how come you merged table? With which table? I can see that with previous one but, you did that by typing, not using ribbon options and clicking right? Because I tried to replicate that and I couldnt select previous table (with no maxdate just CREATION DATE)
- Vijay_A_Verma
Yes, first you will have to do a self merge i.e same table to same table. Then you will need to change one part manually.