Forum Discussion

mohd_khomaini's avatar
mohd_khomaini
New Member
1 year ago
Solved

Power Query - Need help to categorize table based on lookup table

Hi all, Need some help, I have data in this format with two columns "Group" and "Item". I have the lookup table of Item vs Expected Output in Expected Output table. In the past, I used "List.Contain...
  • wdx223_Daniel's avatar
    1 year ago

    if List.ContainsAll(YourItems,{"W","X","Y","Z"}) then "Category 1" else if List.ContainsAll(YourItems,{"W","X","Y"}) then "
    Category 2" else "Category 3"

  • Omid_Motamedise's avatar
    1 year ago

    Use the below code

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Pcu7DYBQDEPRXVy/gvCnBCRgAn5R9l+DKLEoLPkUVxWCgtl3woqipq5QQ92hlnpCHfWGen/L3w1UdiOV3eRv9W0hqcg9KeQBsw8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [No = _t, Group = _t, Item = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Group"}, {{"Count", each Text.Combine(_[Item],"-")}}),
    #"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Category", 1, 1, Int64.Type)
    in
    #"Added Index"