Forum Discussion

duycao's avatar
duycao
Frequent Visitor
5 years ago
Solved

Split Sales into each component in a Bundle

Hi all,   I've been struggling with the below exercise from my team which I want to have your expert thoughts! We're a retailer, we sell multiple types of product including Single and Bundle (comb...
Need Help
  • v-deddai1-msft's avatar
    v-deddai1-msft
    5 years ago

    Hi duycao ,

     

    You can convert your query to expected ouput bu query editor:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VY4xCsAgDEXvklloEhXs6hU6SreWUihuvX/zdagiwZj8x7MUUlZZ7Cg52pgF112v57QGs7YQxotpdz/gbZSZsc5vPRoQrXxANoEKMxG6QkcFCIF0tQpxysee92O+/aQBMCQT7B8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Product ID" = _t, #"Single/Bundle" = _t, Items = _t, #"Original price" = _t, #"Paid Price" = _t, Sales = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Product ID", type text}, {"Single/Bundle", type text}, {"Items", Int64.Type}, {"Original price", Int64.Type}, {"Paid Price", Int64.Type}, {"Sales", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Product ID"}, #"Bundle lookup", {"Product ID"}, "Bundle lookup", JoinKind.LeftOuter),
    #"Expanded Bundle lookup" = Table.ExpandTableColumn(#"Merged Queries", "Bundle lookup", {"Component ID", "Quantity in Bundle"}, {"Bundle lookup.Component ID", "Bundle lookup.Quantity in Bundle"}),
    #"Added Conditional Column" = Table.AddColumn(#"Expanded Bundle lookup", "Product ID 2", each if Text.Contains([Product ID], "S") then [Product ID] else if Text.Contains([Product ID], "B") then [Bundle lookup.Component ID] else null),
    #"Added Custom" = Table.AddColumn(#"Added Conditional Column", "items2", each [Items]*([Bundle lookup.Quantity in Bundle]+1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"items2"}),
    #"Replaced Value" = Table.ReplaceValue(#"Removed Columns",null,1,Replacer.ReplaceValue,{"Bundle lookup.Quantity in Bundle"}),
    #"Added Custom1" = Table.AddColumn(#"Replaced Value", "Items2", each [Items]*[Bundle lookup.Quantity in Bundle]),
    #"Merged Queries1" = Table.NestedJoin(#"Added Custom1", {"Product ID 2"}, #"Added Custom1", {"Product ID"}, "Added Custom1", JoinKind.LeftOuter),
    #"Expanded Added Custom1" = Table.ExpandTableColumn(#"Merged Queries1", "Added Custom1", {"Paid Price"}, {"Added Custom1.Paid Price"}),
    #"Added Custom2" = Table.AddColumn(#"Expanded Added Custom1", "Custom", each [Items2]*[Added Custom1.Paid Price]),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom2",{"Product ID", "Single/Bundle", "Items", "Original price", "Paid Price", "Sales", "Bundle lookup.Component ID", "Bundle lookup.Quantity in Bundle", "Added Custom1.Paid Price"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Product ID 2", "Product ID"}, {"Items2", "Items"}, {"Custom", "Sales"}})
in
    #"Renamed Columns"

     

     

    For more details, please refer to the pbix file: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EVZ8RWjHyBhGhMMqTg5QGaYBP9M89EB5zXb8K8vNN0BWuw?e=noDYnI

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    Best Regards,

    Dedmon Dai