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.

Impactful Individual
Impactful Individual

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

March Update

Check it Out!

Click here to read more about the March 2021 Updates!


The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.


Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.