Forum Discussion

dass6's avatar
dass6
Regular Visitor
1 year ago
Solved

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 h...
Need Help
  • ryan_mayu's avatar
    ryan_mayu
    1 year ago

    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"

     

     

dass6's avatar
dass6
Regular Visitor
1 year ago

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

ryan_mayu's avatar
ryan_mayu
Super User
1 year ago

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"