Forum Discussion
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.Contains", but it returned the wrong category for Group A (expected: Category 1, returned value: Category 2).
Questions:
1. Is there a way to address this wrong value return?
2. Currently, I combined column Item with delimiter ";" for me to define the lookup value. Is there a better way to not use the "delimiter" when defining the lookup table. Sequence order of Item is not important ( e.g item order W-X-Y-Z )
| Data Structure/Format | ||
| No | Group | Item |
| 1 | A | V |
| 2 | A | W |
| 3 | A | X |
| 4 | A | Y |
| 5 | A | Z |
| 6 | B | W |
| 7 | B | X |
| 8 | B | Y |
| 9 | C | F |
| 10 | C | G |
| 11 | C | H |
| Expected Output | ||||
| No | Group | Combo Item | Expected Output | Remarks |
| 1 | A | W - X - Y - Z | Category 1 | I want to have go through column "Item", if the item contains W-X-Y-Z (Character V is can be ignored), output = Category 1 |
| 2 | B | W - X - Y | Category 2 | I want to have go through column "Item", if the item contains W-X-Y, output = Category 2 |
| 3 | C | F - G - H | Category 3 |
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"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"
2 Replies
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"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"