Showing results for 
Search instead for 
Did you mean: 
Helper II
Helper II

Split rows based on quantity

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?

Community Support
Community Support

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

Hi @v-cazheng-msft 


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

Helper II
Helper II

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.

Solution Sage
Solution Sage

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

Helpful resources

PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!


Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors