cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Locco
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?

6 REPLIES 6
v-cazheng-msft
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

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

PC2790
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]))

PC2790_0-1614349457247.png

2) Now Expand to new rows as below:

PC2790_1-1614349561594.png

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

Announcements
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!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors