Create duplicate rows in a table

[Jeff2Jets]

I know this sounds a bit crazy, but...

I have a table with one row that I would like to duplicate 19 times. There is one column I would like to change for each row. Is there a way to do that in Power Bi or am I forced to do it manually in the .csv file before ingesting into Power BI. I am trying to eliminate any manual steps.

[dk_dk · Accepted solution]

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

[HotChilli]

I'm sure it's possible.  What are the details? Do the changed values depend on values in the table (for each row) or are they a set of 19 values that you have in a separate list/table?