Create duplicate rows in a table

Hi Jeff2Jets 

 

 

As HotChilli mentioned, there are ways to do this automatically, depending on what the change is that you would like to do for each row.

Here is an example:

Let's say you have your data that is a single row with two columns:

Assume you want to duplicate this 19 times and have a third column that is number from 1-20 (different for each row), while Col2 will remain 1 for all rows.

You can create a helper table in Power Query:

let
Source = List.Numbers(1, 20),
Table = Table.FromList(Source, Splitter.SplitByNothing(), {"Numbers"})
in
Table


Which will create 20 rows of data.


You can go back to your original data, and do a full outer merge with the helper table. After expanding

 your query should look like this:

 

Then you can select all the rows except the one from the helper table, and go to Transform > Fill > Up (or Down, depending on whether your one row is at the top or the bottom of the data). Then you can filter out the null from the Helper column, and you are left with your original data duplicated 19 times (resulting in 20 rows) where one column is different for each row, but the rest is the same.


I hope this helps! If your usecase with the one changing column is different, please specify what the changing column should be and hopefully we can help you further!

Best,
Daniel