Apply conditional formula to identical row values

[Anonymous]

Hello everyone!

I am wondering if it is possible to add a conditional formula for every identical transaction number, in power query, in my data below:

Store	Transaction no	product number	dept no	product type	sales $
1	123	412	1	merch	700
1	123	413	2	warranty	150
1	123	414	3	install	150
1	123	415	4	merch	500
2	124	416	4	merch	600
2	126	416	1	merch	600
2	126	418	2	warranty	100
3	125	417	1	merch	450

Essentially i want to be able to mark the entire transaction as a "warranty transaction" or a "install transaction" or a "merch only transaction", so i'd like to achieve something like the below:

Store	Transaction no	product number	dept no	product type	sales $	warranty flag	install flag	merch only flag
1	123	412	1	merch	700	1	1	0
1	123	413	2	warranty	150	1	1	0
1	123	414	3	install	150	1	1	0
1	123	415	4	merch	500	1	1	0
2	124	416	4	merch	600	0	0	1
2	126	416	1	merch	600	1	0	0
2	126	418	2	warranty	100	1	0	0
3	125	417	1	merch	450	0	0	1

I understand that i can write a conditional formula if [product type] = x then 1 else 0, but the trick i would like to know is if its possible to apply that formula to search all transaction numbers of identical type.

Any help you can provide would be appreciated!

Thanks so much!

[edhans · Accepted solution]

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:

 

[Anonymous]

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! 

[edhans]

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.  

[Anonymous]

Hi @Anonymous ,

it is definitely possible (please refer to the sample code below), but I wonder why would you want it, looks like data duplication... Maybe look again at what you are trying to achieve by that. If there is a simpler way?

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1YlWMgKTxkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Record.AddField([], [Column1], 1)),
    #"Expanded Custom" = Table.ExpandRecordColumn(#"Added Custom", "Custom", List.Distinct(Source[Column1]), List.Distinct(Source[Column1]))
in
    #"Expanded Custom"

Kind regards,

JB