Split rows based on quantity

[Locco]

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?

[v-cazheng-msft]

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

[Locco]

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.

[v-cazheng-msft]

OK. If you have problem, please let us known.

Best Regards,

Caiyun Zheng

[Locco]

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]

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

[Locco]

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