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

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

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

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.

secondImage

Experience what’s next for Power BI

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