Forum Discussion
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:
- 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
- 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:
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!
Not expecting to crack this easily but very grateful for any help!
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"Check the attached file.
10 Replies
camargos88Community 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"Check the attached file.
camargos88 How did you do that so quickly?! Genius and perfect - thank you so much
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?
- camargos88
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.
- amitchandak
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") - camargos88
Anonymous ,
You can also combine the values and show it like:
Product A = "x, y"
Product B = "w,z"
- camargos88
Anonymous ,
I believe it worthes trying this function.
