cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
charliedata
Resolver III
Resolver III

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
Super User III
Super User III

@charliedata ,

 

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
Super User III
Super User III

@charliedata ,

 

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
Super User III
Super User III

@charliedata ,

 

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!



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

camargos88
Super User III
Super User III

@charliedata ,

 

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!



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

camargos88
Super User III
Super User III

@charliedata ,

 

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

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?

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

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

amitchandak
Super User IV
Super User IV

@charliedata , 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")



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors