Reshaping table Excel PowerQuery

Question

I have a large table in Excel, which is output of a data-gathering tool, that looks more or less like this:

  DateA    |  ValueA  |   DateB    |  ValueB  | ... |    DateZ   | ValueZ
---------------------------------------------------------------------------
2019-01-01 |    3     | 2019-01-01 |    6     | ... | 2019-01-04 |   7
2019-01-02 |    1     | 2019-01-04 |    2     | ... | 2019-01-05 |   3

And I'd like to process it so it would like this:

  Date     |  Value  | Type
-----------------------------
2019-01-01 |   3     |   A
2019-01-02 |   1     |   A
2019-01-01 |   6     |   B
2019-01-04 |   2     |   B
            ...
2019-01-04 |   7     |   Z
2019-01-05 |   3     |   Z

Because this is the format, that is used on our sql database. How to do this in the least tedious way, preferably using PowerQuery? I'd like to avoid brute-force coping and pasting with vba loop. The number of columns is fixed, but would be nice to have an option to add another one later on, the number of rows would however vary around some value (like 20, 21, 20, 22, 19, 20) day-to-day

Alexis Olson Alexis Olson · Accepted Answer · 2019-03-12T16:29:16

Columns are harder to work with, so I'd first transform each column into a new row as a list.

ColumnsToRows =
    Table.FromColumns(
        {
         Table.ToColumns(Source),
         Table.ColumnNames(Source)
        },
        {"ColumnValues","ColumnName"}
    )

This should give you a table as follows where each list consists of values in the corresponding column. For example, the top list is {1/1/2019,1/2/2019}. (The from columns part is to add the ColumnName column.)

| ColumnValues | ColumnName |
|--------------|------------|
| [List]       | DateA      |
| [List]       | ValueA     |
| [List]       | DateB      |
| [List]       | ValueB     |
| [List]       | DateZ      |
| [List]       | ValueZ     |

We can then filter this based on the data type in each list. To get the date rows you can write:

DataRows =
    Table.SelectRows(
        ColumnsToRows,
        each Value.Type(List.First([ColumnValues])) = type date
    )

Which gets you the following filtered table:

| ColumnValues | ColumnName |
|--------------|------------|
| [List]       | DateA      |
| [List]       | DateB      |
| [List]       | DateZ      |

If you expand the first column with Table.ExpandListColumn(DataRows, "ColumnValues"), then you get

| ColumnValues | ColumnName |
|--------------|------------|
| 1/1/2019     | DateA      |
| 1/2/2019     | DateA      |
| 1/1/2019     | DateB      |
| 1/4/2019     | DateB      |
| 1/4/2019     | DateZ      |
| 1/5/2019     | DateZ      |

The logic is analogous to filter and expand the value rows.

ValueRows =
    Table.ExpandListColumn(
        Table.SelectRows(
            ColumnsToRows,
            each Value.Type(List.First([ColumnValues])) = type number
        ),
        "ColumnValues"
    )

Which gets you a similar looking table:

| ColumnValues | ColumnName |
|--------------|------------|
| 3            | ValueA     |
| 1            | ValueA     |
| 6            | ValueB     |
| 2            | ValueB     |
| 7            | ValueZ     |
| 3            | ValueZ     |

Now we just need to combine together the columns we want into a single table:

Combine Columns =
    Table.FromColumns(
        {
         DateRows[ColumnValues],
         ValueRows[ColumnValues],
         ValueRows[ColumnName]
        },
        {"Date", "Value", "Type"}
    )

and then extract the text following Value in the column names.

ExtractType =
    Table.TransformColumns(
        CombineColumnns,
        {{"Type", each Text.AfterDelimiter(_, "Value"), type text}}
    )

The final table should be just as specified:

| Date     | Value | Type |
|----------|-------|------|
| 1/1/2019 | 3     | A    |
| 1/2/2019 | 1     | A    |
| 1/1/2019 | 6     | B    |
| 1/4/2019 | 2     | B    |
| 1/4/2019 | 7     | Z    |
| 1/5/2019 | 3     | Z    |

All in a single query, the M code looks like this:

let
    Source = <Source Goes Here>,
    ColumnsToRows = Table.FromColumns({Table.ToColumns(Source), Table.ColumnNames(Source)}, {"ColumnValues","ColumnName"}),
    DateRows = Table.ExpandListColumn(Table.SelectRows(ColumnsToRows, each Value.Type(List.First([ColumnValues])) = type date), "ColumnValues"),
    ValueRows = Table.ExpandListColumn(Table.SelectRows(ColumnsToRows, each Value.Type(List.First([ColumnValues])) = type number), "ColumnValues"),
    CombineColumnns = Table.FromColumns({DateRows[ColumnValues], ValueRows[ColumnValues], ValueRows[ColumnName]},{"Date", "Value", "Type"}),
    ExtractType = Table.TransformColumns(CombineColumnns, {{"Type", each Text.AfterDelimiter(_, "Value"), type text}})
in
    ExtractType

Reshaping table Excel PowerQuery

1 Answers