Power Query - split row to multiple row base on number from other column

[nirrobi]

Hi,

I have table as below:

WordQuantity

AAA	3
BBB	2
EEE	4

I need to convert it to the below (repeat every line base on the number on the quntity column)

AAA	3
AAA	3
AAA	3
BBB	2
BBB	2
EEE	4
EEE	4
EEE	4
EEE	4

Thanks!

Nir.

[nirrobi]

https://www.myonlinetraininghub.com/excel-forum/power-query/insert-rows-based-in-criteriaconditions

[Zubair_Muhammad · Accepted solution]

@nirrobi

You can add a Custom Column and then expand it to new rows

={1..[Quantity]}

The delete this custom column

Final code/ File attached as well

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnR0VNJRMlaK1YlWcnJyArKNwGxXV1cg20QpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Word = _t, Quantity = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Word", type text}, {"Quantity", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each {1..[Quantity]}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Custom"})
in
    #"Removed Columns"