Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
khom
Frequent Visitor

Power Query - How do I Categorize "Table1" based on "Lookup_Table"

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.

Screenshot 2022-12-28 143507.png

3 ACCEPTED SOLUTIONS
Mahesh0016
Super User
Super User

@khom 

 

>> 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.

 

Mahesh0016_1-1672222607577.png

 

 

 

 

 

Mahesh0016_0-1672222556891.png

 

 

**Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!

View solution in original post

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

View solution in original post

AlB
Super User
Super User

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"

 

 

 

SU18_powerbi_badge

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.

 

View solution in original post

12 REPLIES 12
ImkeF
Super User
Super User

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

khom
Frequent Visitor

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?

khom
Frequent Visitor

Thanks for the detailed explanation and your help. This clarifies my doubt. 

AlB
Super User
Super User

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"

 

 

 

SU18_powerbi_badge

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.

 

khom
Frequent Visitor

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?

khom
Frequent Visitor

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.

Mahesh0016
Super User
Super User

@khom 

 

>> 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.

 

Mahesh0016_1-1672222607577.png

 

 

 

 

 

Mahesh0016_0-1672222556891.png

 

 

**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.

ImkeF
Super User
Super User

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

khom
Frequent Visitor

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

khom
Frequent Visitor

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]?

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors