Transform table with Excel Power Query

Question

I'm new with Pivot Tables, Power Query, DAX formulas, etc and I want to know how to get the output desired with Pivot Tables, Power Query, etc or only would be possible with VBA macro.

I'm using Excel 2016.

Before I asked this question in other site and the only answer I received was a reference to this example but I after I tranformed the table to List the formula they give there Table.FromRows( List.Split( Table1[Column1], 3) ) simply doesnt work for me.

This is my input Table:

| DESCRIPTION | VALUE  |
|-------------|--------|
| STAGE       | 1      |
| ID          | 0      |
| NAME        | JFMSC  |
| TYPE        | MLRR   |
| DFRUL       | P1     |
| ADDR        | 1001   |
| RRUL        | P1     |
| SPRR        | TRUE   |
| ISGALW      | FALSE  |
| ISUTWD      | FALSE  |
| STAGE       | 1      |
| ID          | 2      |
| NAME        | PLLSJS |
| TYPE        | MLRR   |
| DFRUL       | P1     |
| STAGE       | 1      |
| ID          | 4      |
| NAME        | AAAARR |
| TYPE        | MLRR   |
| DFRUL       | R2     |
| ADDR        | 3553   |
| RRUL        | P1     |
| SPRR        | FALSE  |
| ISGALW      | FALSE  |
| ISUTWD      | FALSE  |
| ADDR        | 66444  |
| RRUL        | P1     |
| SPRR        | FALSE  |
| ISGALW      | FALSE  |
| ISUTWD      | FALSE  |
| ADDR        | 890087 |
| RRUL        | P1     |
| SPRR        | FALSE  |
| ISGALW      | FALSE  |
| ISUTWD      | FALSE  |
| STAGE       | 1      |
| ID          | 0      |
| NAME        | PPROOA |
| TYPE        | RRHN   |
| DFRUL       | P1     |
| ADDR        | 7034   |
| RRUL        | P1     |
| SPRR        | FALSE  |
| ISGALW      | FALSE  |
| ISUTWD      | FALSE  |

this is the output I'd like to obtain:

| STAGE | ID | NAME   | TYPE | DFRUL | ADDR   | RRUL |
|-------|----|--------|------|-------|--------|------|
| 1     | 0  | JFMSC  | MLRR | P1    | 1001   | P1   |
| 1     | 2  | PLLSJS | MLRR | P1    |        |      |
| 1     | 4  | AAAARR | MLRR | R2    | 3553   | P1   |
|       |    |        |      |       | 66444  | P1   |
|       |    |        |      |       | 890087 | P1   |
| 1     | 0  | PPROOA | RRHN | P1    | 7034   | P1   |

UPDATE

Input with spaces and some other uneeded text, but with same field to transform.

+----------------------------+---------+
| DESCRIPTION                |   VALUE | 
+----------------------------+---------+
|                            |         |
|                            |         |
| ..d  2019-03-07 17:35:52   |         |
| KLAPW                      | #075286 |
| STAGE=1, ID=0, TYPE=MLRR   |         |
|                            |         |
|                            |         |
| STAGE                      | 1       |
| ID                         | 0       |
| NAME                       | NAME1   |
| TYPE                       | MLRR    |
| DFRUL                      | P1      |
| ADDR                       | 8876    |
| RRUL                       | P1      |
| SPRR                       | TRUE    |
| ISGALW                     | FALSE   |
| ISUTWD                     | FALSE   |
|                            |         |
|                            |         |
| Total count                | 10      |
|                            |         |
| There is together 1 report |         |
|                            |         |
|    END_BLOCK               |         |
|                            |         |
|                            |         |
| ..d  2019-03-07 17:35:52   |         |
| KLAPW                      | #075287 |
| STAGE=1, ID=1, TYPE=MLRR   |         |
|                            |         |
|                            |         |
| STAGE                      | 1       |
| ID                         | 1       |
| NAME                       | NAME2   |
| TYPE                       | MLRR    |
| DFRUL                      | R2      |
| ADDR                       | 526766  |
| RRUL                       | P1      |
| SPRR                       | FALSE   |
| ISGALW                     | FALSE   |
| ISUTWD                     | FALSE   |
|                            |         |
|                            |         |
| Total count                | 10      |
|                            |         |
| There is together 1 report |         |
|                            |         |
|    END_BLOCK               |         |
|                            |         |
|                            |         |
| ..d   2019-03-07 17:35:52  |         |
| KLAPW                      | #075288 |
| STAGE=1, ID=2, TYPE=MLRR   |         |
|                            |         |
|                            |         |
| STAGE                      | 1       |
| ID                         | 2       |
| NAME                       | NAME3   |
| TYPE                       | MLRR    |
| DFRUL                      | P1      |
| ADDR                       | 232424  |
| RRUL                       | R2      |
| SPRR                       | FALSE   |
| ISGALW                     | FALSE   |
| ISUTWD                     | FALSE   |
|                            |         |
| ADDR                       | 13112   |
| RRUL                       | R2      |
| SPRR                       | FALSE   |
| ISGALW                     | FALSE   |
| ISUTWD                     | FALSE   |
|                            |         |
| ADDR                       | 131223  |
| RRUL                       | R2      |
| SPRR                       | FALSE   |
| ISGALW                     | FALSE   |
| ISUTWD                     | FALSE   |
|                            |         |
| ADDR                       | 111324  |
| RRUL                       | R2      |
| SPRR                       | FALSE   |
| ISGALW                     | FALSE   |
| ISUTWD                     | FALSE   |
|                            |         |
| ADDR                       | 56543   |
| RRUL                       | R2      |
| SPRR                       | FALSE   |
| ISGALW                     | FALSE   |
| ISUTWD                     | FALSE   |
|                            |         |
| ADDR                       | 11133   |
| RRUL                       | R2      |
| SPRR                       | FALSE   |
| ISGALW                     | FALSE   |
| ISUTWD                     | FALSE   |
+----------------------------+---------+

Unfortunately you really have a problem in that your initial data is missing a record ID... a third column to identify what rows a field belongs to once it's unpivoted. A person could try to rely on the order in the table, but you have these null values that will foul that up. Also, from a purist position, sets are inherently unordered and you should never rely that the order of records is in any way constant unless enforced by a sort on the available data. — Ryan B., Mar 14 '19 at 19:25
Then Pivot Table, Power Query wouldn't be the tools for this task? — Ger Cas, Mar 14 '19 at 19:39
If you go in and add a third column that can be used to group the fields according to rows, then Power Query can easily do what you want. Otherwise, no. — Ryan B., Mar 14 '19 at 22:00
I understand from you, have a 3rd column with the fields that I want to use as headers in output. Is like that? May you show me how to that in Power Query if I have that 3rd column please? — Ger Cas, Mar 14 '19 at 23:16
@Olly has shown you a programmatic way to add the '3rd column'. If you step through his solution, you see how the 'Record Number' is required for a pivot to work. — Ryan B., Mar 15 '19 at 16:27

score 2 · Accepted Answer · answered Mar 15 '19 at 09:02

If we can assume that your source data is always sorted in field order, even if not all fields are populated for each record, then we can extract the data you want:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
    #"Added Field Index" = Table.AddColumn(#"Added Index", "Field Index", each List.PositionOf({"STAGE","ID","NAME","TYPE","DFRUL","ADDR","RRUL","SPRR","ISGALW","ISUTWD"},[Decription]), Int64.Type),
    #"Added Previous Field Index" = Table.AddColumn(#"Added Field Index", "Previous Field Index", each try #"Added Field Index"[Field Index]{[Index]-1} otherwise null, Int64.Type),
    #"Added Record Index" = Table.AddColumn(#"Added Previous Field Index", "Record Index", each if [Field Index] < [Previous Field Index] or [Previous Field Index] = null then [Index] else null, Int64.Type),
    #"Filled Down Record Index" = Table.FillDown(#"Added Record Index",{"Record Index"}),
    #"Record Numbers" = Table.AddIndexColumn(Table.Group(#"Filled Down Record Index", {"Record Index"}, {}), "Record", 1, 1),
    #"Merged Record Numbers" = Table.NestedJoin(#"Filled Down Record Index",{"Record Index"},#"Record Numbers",{"Record Index"},"Filled Down",JoinKind.LeftOuter),
    #"Expanded Record Numbers" = Table.ExpandTableColumn(#"Merged Record Numbers", "Filled Down", {"Record"}, {"Record"}),
    #"Selected Columns" = Table.SelectColumns(#"Expanded Record Numbers",{"Decription", "Value", "Record"}),
    #"Pivoted Column" = Table.Pivot(#"Selected Columns", List.Distinct(#"Selected Columns"[Decription]), "Decription", "Value"),
    #"Removed Other Columns" = Table.SelectColumns(#"Pivoted Column",{"STAGE", "ID", "NAME", "TYPE", "DFRUL", "ADDR", "RRUL"})
in
    #"Removed Other Columns"

Example file: https://excel.solutions/so_55152879/

You've got a little typo throughout - Decription instead of Description. But this is really nice! I had tried to do something similar using STAGE or ISUTWD to trip the record number -- but they are both missing sometimes. Your Added Field Index logic is a very nice element. — Ryan B., Mar 15 '19 at 16:22
Looks to me like you can take your revised example data and first filter the [value] data so it has no blanks, and then filter the [description] data so that no rows contain "total" or "KLAPW" — Ryan B., Mar 15 '19 at 20:27
@Ryan B. Yes, I was trying and it seems complicates a lot having that empty rows and garbage rows. I need to do a preprocessing. Thanks — Ger Cas, Mar 16 '19 at 00:10
@Olly Thanks so much Olly. It seems to work awesome. Now I ask you if you may check other post based on this where I show your solution but I want to get my final goal. Is I think a more complex table that I would know how to do in VBA but not in Power Query. The post is `https://stackoverflow.com/questions/55192173/complex-transformation-in-excel-power-query` — Ger Cas, Mar 16 '19 at 00:15

Transform table with Excel Power Query

1 Answers1