Forum Discussion
Apply conditional formula to identical row values
It is getting late here so I am not 100% sure I followed your requirements on when it is merch, install, or warranty, but give this a shot.
Paste this code into a blank query and see if the results are what you need.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI0MgaSJoZGIDYQ56YWJWcAaXMDA6VYHVQ1IBKkrjyxqCgxr6QSJGWKqcwESIJYmXnFJYk5OThUmYJIJAtNoRaCHWJkAlZjhqbGDEWNGVyNIQE1FlgcDlVmDFYGdoyhOZpRJiBnxwIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Store = _t, #"Transaction no" = _t, #"product number" = _t, #"dept no" = _t, #"product type" = _t, #"sales $" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Store", Int64.Type}, {"Transaction no", Int64.Type}, {"product number", Int64.Type}, {"dept no", Int64.Type}, {"product type", type text}, {"sales $", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Transaction no"}, {{"All Rows", each _, type table [Store=number, Transaction no=number, product number=number, dept no=number, product type=text, #"sales $"=number]}}), #"Added Warranty Flag" = Table.AddColumn(#"Grouped Rows", "Warranty Flag", each Table.ContainsAll( [All Rows], {[product type="merch"],[product type="warranty"]}, "product type"), type logical), #"Added Install" = Table.AddColumn(#"Added Warranty Flag", "Install", each Table.Contains([All Rows],[product type = "install"]), type logical), #"Added Merch Only" = Table.AddColumn(#"Added Install", "Merch Only", each if Table.ContainsAll( [All Rows], {[product type="merch"],[product type="warranty"]}, "product type") = false and Table.Contains([All Rows],[product type = "merch"]) = true then true else false, type logical), #"Expanded All Rows" = Table.ExpandTableColumn(#"Added Merch Only", "All Rows", {"Store", "product number", "dept no", "product type", "sales $"}, {"Store", "product number", "dept no", "product type", "sales $"}) in #"Expanded All Rows"Basically I grouped all records by the transaction number, then did some Table.Contains/Table.ContainsAny logic on the nested table, then expanded the nested table once I was done.
End result:
It is getting late here so I am not 100% sure I followed your requirements on when it is merch, install, or warranty, but give this a shot.
Paste this code into a blank query and see if the results are what you need.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI0MgaSJoZGIDYQ56YWJWcAaXMDA6VYHVQ1IBKkrjyxqCgxr6QSJGWKqcwESIJYmXnFJYk5OThUmYJIJAtNoRaCHWJkAlZjhqbGDEWNGVyNIQE1FlgcDlVmDFYGdoyhOZpRJiBnxwIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Store = _t, #"Transaction no" = _t, #"product number" = _t, #"dept no" = _t, #"product type" = _t, #"sales $" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Store", Int64.Type}, {"Transaction no", Int64.Type}, {"product number", Int64.Type}, {"dept no", Int64.Type}, {"product type", type text}, {"sales $", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Transaction no"}, {{"All Rows", each _, type table [Store=number, Transaction no=number, product number=number, dept no=number, product type=text, #"sales $"=number]}}),
#"Added Warranty Flag" = Table.AddColumn(#"Grouped Rows", "Warranty Flag", each Table.ContainsAll( [All Rows], {[product type="merch"],[product type="warranty"]}, "product type"), type logical),
#"Added Install" = Table.AddColumn(#"Added Warranty Flag", "Install", each Table.Contains([All Rows],[product type = "install"]), type logical),
#"Added Merch Only" = Table.AddColumn(#"Added Install", "Merch Only", each if Table.ContainsAll( [All Rows], {[product type="merch"],[product type="warranty"]}, "product type") = false and Table.Contains([All Rows],[product type = "merch"]) = true then true else false, type logical),
#"Expanded All Rows" = Table.ExpandTableColumn(#"Added Merch Only", "All Rows", {"Store", "product number", "dept no", "product type", "sales $"}, {"Store", "product number", "dept no", "product type", "sales $"})
in
#"Expanded All Rows"
Basically I grouped all records by the transaction number, then did some Table.Contains/Table.ContainsAny logic on the nested table, then expanded the nested table once I was done.
End result:
This worked great, thank you!
I was wondering however, why you did what you did for the warranty column? I tried out using the same method you used for the install column and that seemed to work the same.
Table.AddColumn(#"Added Warranty Flag", "Install", each Table.Contains([All Rows],[product type = "warranty"])Just curious if there would be a situation where this wouldnt work.
Thanks!
I used the following for Warranty:
= Table.ContainsAll( [All Rows], {[product type="merch"],[product type="warranty"]}, "product type")I may have misunderstood your requirements, but my function above will only return TRUE if the item has both "merch" and "warranty" in it.
Your function will work if it has "warranty" in it, and not test for "merch" as well.