I have a table generated in power query that is currently in the following format
| A | Key1 | Value1 | Key2 | Value2 |
|---|---|---|---|---|
| A1 | B1 | C1 | D1 | E1 |
I want
| A | Key | Value |
|---|---|---|
| A1 | B1 | C1 |
| A1 | D1 | E1 |
How can this be achieved in power query?
I have a table generated in power query that is currently in the following format
| A | Key1 | Value1 | Key2 | Value2 |
|---|---|---|---|---|
| A1 | B1 | C1 | D1 | E1 |
I want
| A | Key | Value |
|---|---|---|
| A1 | B1 | C1 |
| A1 | D1 | E1 |
How can this be achieved in power query?
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"