Split Sales into each component in a Bundle

[duycao]

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 (combination of multiple Single products)

Ex:

- Single: A mouse

- Bundle: (01 x mouse)+ (02 x USB)

In the end, we dont want to see the total Sales of that bundle only, we need to "de-bundle", which means we need to split the Sales into each components in the final report. Illustration as below. I dont know if this is something do-able in Power BI? 

Thank you for helping out!

[amitchandak · Accepted solution]

@duycao , My suggestion would merge these two using left join in power query

https://radacad.com/append-vs-merge-in-power-bi-and-power-query

[v-deddai1-msft · Accepted solution]

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/EVZ8RWjHyBhGhMMqTg...

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

[amitchandak · Accepted solution]

@duycao , My suggestion would merge these two using left join in power query

https://radacad.com/append-vs-merge-in-power-bi-and-power-query