Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I'm newbie for power query. I'm looking forward to have this categorization since I have a lot of data with this data arrangement.
I have this "Table1" (blue color) which contains "Combo" column, and I want to categorize the Table accordingly to "Lookup_Table" (orange). Here's my expected output table in green color. My condition is if Table1, Item1 ("A-B-C-D-E") contains "A-B-C" and not in particular order, it will categorize it as Football. Same as Item#2 ("F-G-H") , if Combo contains "F-H" it will categorize it as Basketball.
Solved! Go to Solution.
>> 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.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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?
Thanks for the detailed explanation and your help. This clarifies my doubt.
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 AIB,
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?
Hi AIB. Thanks. This is work as intended. I will take a look on List.Intercept function that you used. Sorry, initially I unable to open the your file directly since my version of powerbi is outdated.
>> 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 !!
Thanks. This is working too. I never tried fuzzy matching before. Glad to know it does the work.
Hi @khom ,
not sure I fully understand the requirement: Would C-B-A also classify as Football or must the order of the characters be maintained and just their position with the substring be ignored?
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hi @ImkeF. For this case, the order of A-B-C does not matter. C-B-A is classified as Football as long as character this combination of A,B,C is there. You are right, the position within the substring is ignored.
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.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Thanks a lot. This is exactly what I'm looking for.
May I clarify below items:
1. What does (LookupTable) indicate?
2. {0}? [Category]? what does it indicate
3. Operator of => means?
each Table.SelectRows( Lookup_Table, (LookupTable) => List.ContainsAll([ListOfValues], LookupTable[ListOfValues]) ){0}?[Category]?