Forum Discussion
Power Query - How do I Categorize "Table1" based on "Lookup_Table"
>> Go to power query >> select "Table1" >> Go to Home tab Merge Queries >> Select Lookup Table >> Select Bottom Use fuzzy Matching >> Get Your Out Put >> Select Merge Queries and as new your needs.
**Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!
Hi khom ,
I would use the List.ContainsAll function for it.
Therefore you have to split up the strings into list by using "-" as delimiter:let Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source, {{"Item", Int64.Type}, {"Combo", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "ListOfValues", each Text.Split([Combo], "-")), #"Added Custom1" = Table.AddColumn( #"Added Custom", "Category", each Table.SelectRows( Lookup_Table, (LookupTable) => List.ContainsAll([ListOfValues], LookupTable[ListOfValues]) ){0}?[Category]? ) in #"Added Custom1"Please also check the file enclosed.
Hi khom
Place the following M code in a blank query to see the steps. See it all at work in the attached file.
let Source = Table1, #"Changed Type" = Table.TransformColumnTypes(Source,{{"Item", Int64.Type}, {"Combo", type text}}), #"Added Custom1" = Table.AddColumn(#"Changed Type", "Category", each let combo_ = Text.Split([Combo], "-"), res_ = Table.SelectRows(LookUpTable, (inner)=> List.Count(List.Intersect({combo_, Text.Split(inner[Lookup], "-")})) = List.Count(Text.Split(inner[Lookup], "-"))) in try res_[Category]{0} otherwise null), #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"Category", type text}}) in #"Changed Type1"Please accept the solution when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Hi ,
The expression "(Lookuptable)=> " is the beginning of a definition of a function. That function has one argument: The (current) row of the Lookuptable (according to your specification).
Usually, this is identical with the "each" keyword that will be used by Power Query automatically when creating a Table.SelectRow statement. But I am using it in an area where the "each" keyword is used already, therefore to avoid ambiguity, I have to use a specific function declaration.
A number in curly bracket {0} is the positional index operator and picks the n-th element of the object it is applied to (usually a table or a list).
A text in square brackets [Category] is a lookup operator and picks the named element from an object (either a column from a table or a field from a record.
The ? is used to cater for missing values: If the desired element can actually not be found, instead of an error, then null will be returned instead.
If you want to learn more about the basics of the M-language, please check out this page that is the start of a nice compact introduction: Power Query M Formula Language Introduction - PowerQuery M | Microsoft Learn
Hi Imkef,
I've started using the code. I have this scenario whereby:
if my combo listed as below:
e.g.
1. A-B-C = Categorized as " Football"
2. A-B-C-D = Should Categorized as "Soccer", but this one is categorized as "Football" instead.
Is there a way to address this?