Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Apply conditional formula to identical row values

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 numbe...
Need Help
Tutorial Requests
  • edhans's avatar
    edhans
    6 years ago

    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's avatar
Anonymous
Not applicable
6 years ago

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