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
Anonymous
Not applicable

Power Query challenge! "For loop" between two tables to match strings and add columns

I need the help of a Power Query expert to create a loop logic as part of a sales data set preparation

 

My input tables are:

 

  1. product synonyms - the various names used by the sales team to talk about the products (in my example below there are 3 products with various synonyms

  2. sales calls - each record is a call with notes that I'd like to scan to categorise sales activity regarding the 3 products, by matching the call notes with the synonyms list

 

My output table would essentially be the sales calls table with an additional column per product, in each of which the row value would reflect whether the product was mentioned in the call notes:

for loop.PNG

 

Using Text.Contains an Table.AddColumn, I've created the matching logic with which I can create a new column for a specific product. I could now replicate that M code for each product - however, ideally I would simply loop through the columns of the product table and replicate the Text.Contains and Table.AddColumn logic for each product. 

 

I'm sure this is possible although as a new learner, it's a bit beyond me!

 

matching.PNG

 

Not expecting to crack this easily but very grateful for any help!

1 ACCEPTED SOLUTION
camargos88
Community Champion
Community Champion

@Anonymous ,

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwVNJRMjIwMjA0MjIFMoML8rNTFRKT8ktLFJzyKxQS81IUnDKzU5VidUCqjXCrdk4sAisyQjbSDK4oPCM1NacYbJ5zUWIJ0MBYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Call ID" = _t, #"Call Date" = _t, #"Call notes" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Call ID", Int64.Type}, {"Call Date", Int64.Type}, {"Call notes", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "ProductTable", each let _text = [Call notes] in
Table.PromoteHeaders(
Table.Transpose(
Table.RemoveColumns(
Table.AddColumn(
Table.SelectRows(
   #"Product Synonyms",
   each Text.Contains( _text, [Value])),
   "Text", each "Yes"),
   "Value")))),
    #"Expanded ProductTable" = Table.ExpandTableColumn(#"Added Custom", "ProductTable", Table.ColumnNames(Table.Combine(#"Added Custom"[ProductTable])))
in
    #"Expanded ProductTable"

 

Capture.PNG

Check the attached file.

 



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

Proud to be a Super User!



View solution in original post

10 REPLIES 10
camargos88
Community Champion
Community Champion

@Anonymous ,

 

I believe it worthes trying this function.



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

Proud to be a Super User!



camargos88
Community Champion
Community Champion

@Anonymous ,

 

Sorry, I misunderstood what you said. 

Once you don't need to show the word (only yes), the Table.Distinct is the best option.



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

Proud to be a Super User!



Anonymous
Not applicable

Final q - do you know if Table.Buffer could speed this up anywhere

camargos88
Community Champion
Community Champion

@Anonymous ,

 

You can also combine the values and show it like:

Product A = "x, y"

Product B = "w,z"



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

Proud to be a Super User!



Anonymous
Not applicable

In the synonyms table? If so, I wouldn't know how to structure the Text.Contains step

camargos88
Community Champion
Community Champion

@Anonymous ,

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwVNJRMjIwMjA0MjIFMoML8rNTFRKT8ktLFJzyKxQS81IUnDKzU5VidUCqjXCrdk4sAisyQjbSDK4oPCM1NacYbJ5zUWIJ0MBYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Call ID" = _t, #"Call Date" = _t, #"Call notes" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Call ID", Int64.Type}, {"Call Date", Int64.Type}, {"Call notes", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "ProductTable", each let _text = [Call notes] in
Table.PromoteHeaders(
Table.Transpose(
Table.RemoveColumns(
Table.AddColumn(
Table.SelectRows(
   #"Product Synonyms",
   each Text.Contains( _text, [Value])),
   "Text", each "Yes"),
   "Value")))),
    #"Expanded ProductTable" = Table.ExpandTableColumn(#"Added Custom", "ProductTable", Table.ColumnNames(Table.Combine(#"Added Custom"[ProductTable])))
in
    #"Expanded ProductTable"

 

Capture.PNG

Check the attached file.

 



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

Proud to be a Super User!



Anonymous
Not applicable

One follow up question if I can...

 

If the call description includes more than one synonym for a product, the query generates duplicate columns - e.g. if it includes both "wheels" and "car", there will be a Product B_1 in the output table

 

Any final tip on how to only have one result (ie. column) per product?

Anonymous
Not applicable

Think I got it - I added a Table.Distinct around the Table.RemoveColumns

Anonymous
Not applicable

@camargos88  How did you do that so quickly?! Genius and perfect - thank you so much

amitchandak
Super User
Super User

@Anonymous , Create three column like this in salescall table

Product A = if(countx(filter(product,search(product[productA],salescall[call notes],,0)>0),product[productA])+0 >0, "Yes", "No")
Product b = if(countx(filter(product,search(product[productb],salescall[call notes],,0)>0),product[productb])+0 >0, "Yes", "No")
Product c = if(countx(filter(product,search(product[productc],salescall[call notes],,0)>0),product[productc])+0 >0, "Yes", "No")

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.