Duplicate Rows of a Query (Power Query)

[PietroFarias]

Would anyone know how I can duplicate the rows of a query based on a value in a column?

Here's an example:

ID	Installments
1357	5
1606	3
1126	2

To:

ID	Installments
1357	1
1357	2
1357	3
1357	4
1357	5
1606	1
1606	2
1606	3
1126	1
1126	2

[cbhaskarUTA]

Thanks Pietro & Ashish for this question and solution! Very helpful for adding/duplicating rows based on a column variable so that each unique instance can be assigned a primary key.

[Ashish_Mathur]

You are welcome.

[Ashish_Mathur · Accepted solution]

Hi,

Try this M code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ2NVfSUTJVitUBcswMzIAcYwjH0AjEMVKKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Installments = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Installments", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each {Number.From(1)..Number.From([Installments])}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Installments"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Instalments"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Instalments", Int64.Type}})
in
    #"Changed Type1"

Here is the result