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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors