Create new Rows based on a Column Value

[Maschine]

I'm currently pulling orders from my shipping platform into PowerBI. Every order line item in the order creates a row but sometimes we have a product that is a kit and has a quantity of 1 but really there are multiple products within the kit with their own respective quantities. I'm trying to identify the orders that contain a kit and create new rows for every item within that kit for that order. 

Here's a screenshot of a few orders that are kits.

Notice that the quantity is 1 but really there are 3 products in that order (kit).

[Maschine]

I'm attaching two spreadsheets. One is an example of the current data (sample Data.xls) and another is what the expected data would look like (Sample Data Expected.xls).

Sample Data

Sample Data Expected

Things to notice from Sample Data:

When rows contain the same orderId, that row is a line item of that order (hence the same orderId)

Things to notice from Sample Data Expected:

The cells which are highlighted in green are the order items that are part of the bundle or kit with a quantity of 1. They will ultimately have the original orderId and all of the new relevant information about those products which I would like to just hard code at the moment without having to create a relationship table to the product information unless recommended I do so.

• OrderItemId, LineItemKey, and ProductId have been set to a zero value. 
• The quantity of each item in the bundle is 1.
• The original Bundle row quantity has been set to zero and highlighted in red for reference. 

[v-yanjiang-msft]

Hi @Maschine ,

Please see if this is your expected result. I simplify the sample you provided.

Select all columns except "OrderItemId", then click "Group By"

Select "All Rows" in the Operation option.

In the "Grouped Rows" step, tweak the above formula from "each_" to "each Text.Combine(_[OrderItemId],",")". Get the result:

If you want to count the numbers of this product, add a custom column:

List.Count(Table.SelectRows(#"Changed Type",(x)=>x[ProductId]=[ProductId])[ProductId])

Result:

I attach my sample below for your reference.

Best regards,

Community Support Team_yanjiang

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

[Maschine]

Thank you for this but however I believe I didn't communicate my problem effectively. 

I'm attaching two spreadsheets. One is an example of the current data (sample Data.xls) and another is what the expected data would look like (Sample Data Expected.xls).

Sample Data

Sample Data Expected

Things to notice from Sample Data:

When rows contain the same orderId, that row is a line item of that order (hence the same orderId)

Things to notice from Sample Data Expected:

The cells which are highlighted in green are the order items that are part of the bundle or kit with a quantity of 1. They will ultimately have the original orderId and all of the new relevant information about those products which I would like to just hard code at the moment without having to create a relationship table to the product information unless recommended I do so.

• OrderItemId, LineItemKey, and ProductId have been set to a zero value. 
• The quantity of each item in the bundle is 1.
• The original Bundle row quantity has been set to zero and highlighted in red for reference. 

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...