I have a table generated in power query that is currently in the following format

I want

How can this be achieved in power query?

CodePudding user response：

Answer from Peter works, or to make it more generic:

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
base=1,   // # leading columns that will repeat
group=2,  // followed by groups of 2 columns
Combo = List.Transform(List.Split(List.Skip(Table.ColumnNames(Source),base),group), each List.FirstN(Table.ColumnNames(Source),base) & _),
#"Added Custom" =List.Accumulate(
        Combo, 
        #table({"Column1"}, {}),
        (state,current)=> state & Table.Skip(Table.DemoteHeaders(Table.SelectColumns(Source, current)),1)
)
in #"Added Custom"

CodePudding user response：

1. Reference the original table, remove columns 4 and 5 and rename the remaining columns to A, Key and Value.
2. Reference the original table again, remove columns 2 and 3 and rename the remaining columns to A, Key and Value.
3. Append the 2 above queries