How to handle composite products calculation in query editor/DAX?

[Anonymous]

Hi all, assume I have a sales transaction data in lineitems loaded into Power BI. In some of the transaction data, there are composite products (aka bundles) with the following breakdown:

Product X is a bundle product consisting 1 Product A, 2 Product B and 3 Product C. I am trying to achieve the following:

The transaction history is:

The desired output is:

How do I achieve this in Power BI? Do I do it in Query Editor or DAX? Or should I not be doing this is Power BI at all?

[v-yuta-msft]

Hi johnconnor92 ,

Does Product X and Product A, B, C in two tables or one table? If they are in two tables, suppose table1(Product X), table2(Product A, B, C), then you can modify table1[SKU] as table1[SKU_Composite], then merge the two tables. If they are in one table, you can create a new column which can bundle Product X and A, B, C to a same value.

Regards,

Jimmy Tao

[Anonymous]

Hi @v-yuta-msft, Products X, A, B, and C will all appear in the sale transactions table. In addition, I have the "Product Master" table that indicates all the Bundle SKU's constituent SKUs.

Here is the link to the Excel and PBIX file. My goal is to automate the process of converting the Sales Transactions rows containing Product X into as many lines as possible (3 different lines of Product A, B and C in this case, some bundle SKUs may have 4 or 5 individual products).

Should I do this in DAX? or Query Editor? Or is this not a task for Power BI at all?