cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
nirrobi Member
Member

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

Hi,

 

I have table as below:

 

WordQuantity

AAA3
BBB2
EEE

4

 

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

AAA3
AAA3
AAA3
BBB2
BBB2
EEE4
EEE4
EEE4
EEE4

 

 

Thanks!

 

Nir.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

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

@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"
2 REPLIES 2
nirrobi Member
Member

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

Super User
Super User

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

@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"