Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I'm trying to split rows based on a quantity value.
Each row has a quantity from 1-100. If the quantity is more than 1 I would like to split it into multiple rows. For instance if the quantity is 17 I would like 17 rows for that item.
How could I best achieve this?
Hi, @Locco
Is your problem solved? If not, can you provide some data removing sensitive data or show your expected table?
Best Regards,
Caiyun Zheng
I am still having an issue, but I don't think I can actually do this with the current data setup. I'm going to review the data and how it is pulled to the report to see what/if changes can be made and then go from there.
OK. If you have problem, please let us known.
Best Regards,
Caiyun Zheng
If I can validate that each new row actually matches the initial quantity then I could use this and instead of counting the QUANTITY field I could count rows. I'll see if I can validate that and go from there.
You can try this:
1) Create a new custom column in Power Query Editor using the below formula:
= Table.AddColumn(#"Previous Step", "CustomCoumn", each List.Repeat({[Quantity]},[Quantity]))
2) Now Expand to new rows as below:
I hope this will solve your purpose
That didn't work, all it did was copy the column. It did it copy rows though, where I had less than 100,000 rows I now have over 1 million. The Quantity doesn't show 1 though, it shows the value that is in "QUANTITY."
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
98 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |