Transform an excel table row with multiple entries into unique rows with relevant column info-CodePudding

I have an excel table like this.

I am seeking to convert it into something like this.

I have tried using powerquery to automate this but I am nowhere close to what I want it to be. Does any of you have any suggestions how I can get it done? Thank you.

CodePudding user response：

Select the item columns and then unpivot the data ...

Source Data

Result

CodePudding user response：

In powerquery, right click the Date column and choose unpivot other columns

You can then edit the code from

= Table.UnpivotOtherColumns(#"Changed Type", {"Column1"}, "Attribute", "Value")

= Table.UnpivotOtherColumns(#"Changed Type", {"Column1"}, "Item", "Amount")

to change column titles