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 tal...
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.
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?
Think I got it - I added a Table.Distinct around the Table.RemoveColumns