Forum Discussion
categorize the groups based on some logic
I have a table grouped by id. Now I want to add a new column "local" filled bay a value Yes or no based on a condition.
Condition is that whenever there is first instance of event C and there is the type local against that event then in the new column local fill the value "Yes" for all the records of the id category. Please note that Event C could have multiple instances in the group. So need to consider only first instance.
id event type
1 A local
1 B null
1 C null
1 C local
2 A null
2 C local
2 B external
2 C external
3 A local
3 C local
4 B external
4 C external
4 C Local
Output table will look like
id event type local
1 A local No
1 B null No
1 C External No
1 C local No
2 A null Yes
2 C local Yes
2 B external Yes
2 C external Yes
3 A local Yes
3 C local Yes
4 B external No
4 C external No
4 C local No
I would appreciate any help in this regard.
Thank you!
Rashid
Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately)
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIE4pz85MQcpVgdiIgTEOeV5iAEnLEJIPQYQU2BKzHCqgRkbGpFSWpRXiKqMhRBYwwnGWOYZoLNNBNspsEEfSB6YwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, event = _t, #"type" = _t]), #"Filtered Rows" = Table.SelectRows(Source, each ([event] = "C")), #"Grouped Rows" = Table.Group(#"Filtered Rows", {"id"}, {{"Temp", each _, type table [id=nullable text, event=nullable text, type=nullable text]}}), //Function Start fxProcessNext=(Tbl)=> let #"Added Index" = Table.AddIndexColumn(Tbl, "Index", 0, 1, Int64.Type), #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each if [Index]=0 and [type]="local" then "Yes" else "No") in #"Added Custom", //Function End #"Invoked Custom Function" = Table.AddColumn(#"Grouped Rows", "fxProcessNext", each fxProcessNext([Temp])), #"Expanded fxProcessNext" = Table.ExpandTableColumn(#"Invoked Custom Function", "fxProcessNext", {"Custom"}, {"Custom"}), #"Filtered Rows1" = Table.SelectRows(#"Expanded fxProcessNext", each ([Custom] = "Yes")), #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows1",{"Temp"}), #"Merged Queries" = Table.NestedJoin(Source, {"id"}, #"Removed Columns", {"id"}, "Removed Columns", JoinKind.LeftOuter), #"Expanded Removed Columns" = Table.ExpandTableColumn(#"Merged Queries", "Removed Columns", {"Custom"}, {"Custom"}), #"Replaced Value" = Table.ReplaceValue(#"Expanded Removed Columns",null,"No",Replacer.ReplaceValue,{"Custom"}) in #"Replaced Value"
2 Replies
Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately)
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIE4pz85MQcpVgdiIgTEOeV5iAEnLEJIPQYQU2BKzHCqgRkbGpFSWpRXiKqMhRBYwwnGWOYZoLNNBNspsEEfSB6YwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, event = _t, #"type" = _t]), #"Filtered Rows" = Table.SelectRows(Source, each ([event] = "C")), #"Grouped Rows" = Table.Group(#"Filtered Rows", {"id"}, {{"Temp", each _, type table [id=nullable text, event=nullable text, type=nullable text]}}), //Function Start fxProcessNext=(Tbl)=> let #"Added Index" = Table.AddIndexColumn(Tbl, "Index", 0, 1, Int64.Type), #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each if [Index]=0 and [type]="local" then "Yes" else "No") in #"Added Custom", //Function End #"Invoked Custom Function" = Table.AddColumn(#"Grouped Rows", "fxProcessNext", each fxProcessNext([Temp])), #"Expanded fxProcessNext" = Table.ExpandTableColumn(#"Invoked Custom Function", "fxProcessNext", {"Custom"}, {"Custom"}), #"Filtered Rows1" = Table.SelectRows(#"Expanded fxProcessNext", each ([Custom] = "Yes")), #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows1",{"Temp"}), #"Merged Queries" = Table.NestedJoin(Source, {"id"}, #"Removed Columns", {"id"}, "Removed Columns", JoinKind.LeftOuter), #"Expanded Removed Columns" = Table.ExpandTableColumn(#"Merged Queries", "Removed Columns", {"Custom"}, {"Custom"}), #"Replaced Value" = Table.ReplaceValue(#"Expanded Removed Columns",null,"No",Replacer.ReplaceValue,{"Custom"}) in #"Replaced Value"Thank you! Vijay_A_Verma
