Search code examples
powerquerym

Power Query: Duplicate Rows Based on Value

I have a column that contains the Total Stock of an item. I'd like to expand this out into 1 row per item (i.e. the item has 6 in stock and therefore appears as 6 line items).

Is this possible with power query?

Solution

  • The M-Code below will expand this input table

    enter image description here

    to this

    enter image description here

    let
    Source = Excel.CurrentWorkbook(){[Name="tblData"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ColA", type text}, {"Stock", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Col", each List.Repeat({[ColA]},[Stock])),
    #"Expanded Col" = Table.ExpandListColumn(#"Added Custom", "Col")
in
    #"Expanded Col"