Forum Discussion
Check whether an ID has two specific values in another column
Hi All,
I am currently trying to create a calculated table which will only show those entries(has multiple entries) when a certain condition is met.
Any WON which is repeated (3/4/5) if has a Text "C-I" will take the first priority. "C-R" will take the next priority and any text which starts with "EE" will take the last priority.
Final output table should have below entries only:
Any inputs will be greatly appreciated !!!
you can try this in PQ
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXLW9VSK1YlWMgazg5DYCHFXV11DAwMw1wRJmQlEyggqZYokZQqRMoZKmUG4piBuLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [WON = _t, TEXT = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"WON", Int64.Type}, {"TEXT", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"WON"}, {{"COMBINE", each Text.Combine([TEXT],","), type nullable text}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each if Text.Contains([COMBINE], "C-I") then "C-I" else if Text.Contains([COMBINE], "C-R") then "C-R" else [COMBINE]),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"COMBINE"})
in
#"Removed Columns"
3 Replies
not clear about the logic. Do you mean we only consider continous WON? that's why we have C-1 for 1 and 3?
The for EE 100,200 ,300 , we don't need these TEXT? why we will remain the 500?
Hi @ryan_mayu ..if a particular WON has multiple texts, then the priority will be given to C-I first, and if C-I is not present then C-R will get the next priority and last would be anything starting with EE
you can try this in PQ
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXLW9VSK1YlWMgazg5DYCHFXV11DAwMw1wRJmQlEyggqZYokZQqRMoZKmUG4piBuLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [WON = _t, TEXT = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"WON", Int64.Type}, {"TEXT", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"WON"}, {{"COMBINE", each Text.Combine([TEXT],","), type nullable text}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each if Text.Contains([COMBINE], "C-I") then "C-I" else if Text.Contains([COMBINE], "C-R") then "C-R" else [COMBINE]),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"COMBINE"})
in
#"Removed Columns"
