Search code examples
powerbidaxpowerquerym

Duplicate Rows in Power BI multiple times by a number indicated in another column

The idea of the problem I am having is that in Power BI I have a table like:

col1 col2
entry1 1
entry2 2
entry3 1

I would like to create a table of the form:

col1
entry1
entry2
entry2
entry3

That is you duplicate each row by the number specified in a different column. In my actual case my table has many other columns whose values should also be duplicated in each row.

I would like to be able to do this using power queries.

Thanks

Solution

  • You can add a custom column to your table with formula

    List.Repeat( { [col1] }, [col2] )

    This produces a column with a list in each row where the elements of the list are [col1] listed [col2] number of times.

    From there, you can expand that list into rows using the button on the table.

    Here's what the full M code looks like:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSs0rKao0VNJRMlSK1YFyjYBcIwTXGCIbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [col1 = _t, col2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"col1", type text}, {"col2", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Repeat({[col1]},[col2])),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom")
in
    #"Expanded Custom"

    Expanded Rows

    From here, you can pick either col1 or Custom and delete the other columns if you choose.