Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
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!
Solved! Go to Solution.
@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.
Final q - do you know if Table.Buffer could speed this up anywhere
In the synonyms table? If so, I wouldn't know how to structure the Text.Contains step
@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.
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
@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")
User | Count |
---|---|
128 | |
109 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |