Trying to manipulate table. Seems like a combination of transposing, pivoting and duplicating

[cavedweller]

Hi there,

I have a table in Power Query that I'm trying to manipulate.  Unfortunately, the format in which I receive it is very different than what I have to change it to and I'm tryning to speed the process up.

Basically, as show in the table on the top of the image, I have items running down the first column and a series of sizes and locations running across the rest of the columns.  What I'm trying to do is convert the table to look like the one at the bottom of the image.  For every cell that has a number in it, I want to create another row, one for each size that's in inventory, and move the numbers to the respective row. 

I think it's pretty clear, but that might be because I've been looking at it for hours.  😕

Thanks for any help you can provide!

[AlienSx · Accepted solution]

hi

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    unp = Table.UnpivotOtherColumns(Source, {"Item"}, "Attribute", "Value"),
    spl = Table.SplitColumn(unp, "Attribute", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"Size", "loco"}),
    pvt = Table.Pivot(spl, List.Distinct(spl[loco]), "loco", "Value", List.Sum)
in
    pvt

[cavedweller]

That was perfect!  Thanks so much!