Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
Maschine
Regular Visitor

Create new Rows based on a Column Value

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.Screenshot 2023-06-08 115134.png

 

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

 

4 REPLIES 4
Maschine
Regular Visitor

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)

orderId-sameorder.png

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.

 

product-bundle.png

  • 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
Community Support
Community Support

Hi @Maschine ,

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

vyanjiangmsft_0-1686562583914.png

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

vyanjiangmsft_1-1686562658881.png

Select "All Rows" in the Operation option.

vyanjiangmsft_2-1686562784186.png

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

vyanjiangmsft_3-1686562912527.png

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:

vyanjiangmsft_4-1686563250307.png

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.

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)

orderId-sameorder.png

 

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.

 

product-bundle.png

 

  • 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
Super User
Super User

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

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.