Unpivot pairs of associated columns to rows with excel

Question

I have a table in excel with staggered article prices like this:

ArtNr   Amount1   Price1   Amount2   Price2   Amount3   Price3
--------------------------------------------------------------
4711          1      3.5         5      3.0        10      2.5
4712          1      5.0         3      4.5         5      4.0
4713          1      7.0        10      6.0       100      5.0

I want to transpose that into this structure:

ArtNr   Amount   Price
----------------------
4711         1     3.5
4711         5     3.0
4711        10     2.5
4712         1     5.0
4712         3     4.5
4712         5     4.0

... Can this be done with PIVOT/UNPIVOT in Excel using Power Query or anyway in excel?

I need a solution on with excel exact result

Answer 1

Here's a way to do this using Power Query.
Paste this code into the Advanced Editor, making the change for the data source as indicated.
Read the code comments to understand the alogrithm.

let

//change next line to reflect actual table source
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

//Unpivot except for ArtNr column        
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"ArtNr"}, "Attribute", "Value"),

//Remove the terminal digits from the column names in the Attribute column
    #"Remove Trailing Digits" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", 
        Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Attribute"}),

//Create column for grouping
    #"Added Index" = Table.AddIndexColumn(#"Remove Trailing Digits", "Index", 0, 1, Int64.Type),
    #"Add Grouper Column" = 
        Table.AddColumn(#"Added Index", "Grouper", each Number.IntegerDivide([Index], 2), Int64.Type),
    #"Removed Columns" = Table.RemoveColumns(#"Add Grouper Column",{"Index"}),

//Group, then Pivot each subtable with no aggregation
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Grouper"}, {
        {"Pivot", each Table.Pivot(Table.RemoveColumns(_,"Grouper"),[Attribute],"Attribute","Value"),
            type table[ArtNr=Int64.Type, Amount=Int64.Type, Price=number]
        }}),

//Remove the now unneeded grouper column, then expand the pivotted tables
    #"Removed Columns1" = Table.RemoveColumns(#"Grouped Rows",{"Grouper"}),
    #"Expanded Pivot" = Table.ExpandTableColumn(#"Removed Columns1", "Pivot", {"ArtNr", "Amount", "Price"})
        
in
    #"Expanded Pivot"