August 2, 2012
I am using pq often to unpivot tables but i have a situation where i would actually like to "unpivot" a table by repeating table rows based on a value in the last column.
In att is a simple example
I would very much appreciate if anyone could help with this and it would be perfect if this could be done in power query, but any other suggestion (vba or other) is more than welcome!
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Excel Customer Service
November 8, 2013
You can simply add a column with this formula:
In column 5 should be the count you want for rows to be duplicated.
Then, from transform tab, split the new column by number of characters, use 1 char split, but from advanced settings, make sure you split into rows, not into columns, this will duplicate all other columns.
Now you can remove the additional column, to diplay only the results.
I can be done with custom M code, with List.Accumulate, but it's easier via interface, see the attached file.