Forum Discussion
Anonymous
5 years agoNot 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: product synonyms - the various names used by the sales team to tal...
- 5 years ago
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
Community Champion
5 years agoAnonymous ,
You can also combine the values and show it like:
Product A = "x, y"
Product B = "w,z"
Anonymous
5 years agoNot applicable
In the synonyms table? If so, I wouldn't know how to structure the Text.Contains step