Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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).
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).
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.
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.
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).
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.
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...
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
24 | |
13 | |
12 | |
11 | |
8 |
User | Count |
---|---|
43 | |
26 | |
16 | |
15 | |
12 |