Power Query: Pivot Column With Multiple Values Columns OR Hierarchical Header

[DerekK]

Good day,

In Power Query, I need to go from this:

Serial Number	Date/Time	Data Pont 1	Data Point 2
0001	08/14/2024 10:00	DP1A	DP2A
0001	08/14/2024 10:10	DP1B	DP2B
0001	08/14/2024 10:20	DP1C	DP2C
0001	08/14/2024 10:30	DP1D	DP2D
0002	08/14/2024 10:00	DP1A	DP2A
&c.

to this:

Serial Number	08/14/2024 10:00	08/14/2024 10:00	08/14/2024 10:10	08/14/2024 10:10	08/14/2024 10:20	08/14/2024 10:20	08/14/2024 10:30	08/14/2024 10:30
	Data Point 1	Data Point 2	Data Point 1	Data Point 2	Data Point 1	Data Point 2	Data Point 1	Data Point 2
0001	DP1A	DP2A	DP1B	DP2B	DP1C	DP2C	DP1D	DP2D
0002	DP1A	DP2A	DP1B	DP2B	DP1C	DP2C	DP1D	DP2D
&c.

Basically, pivoting the "Date/Time" column with both the "Data Point 1" and "Data Point 2" columns as values.

I hope this makes sense; hope you can help!

Sincerely,



Derek

 

[JGordon11]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ChangeType = Table.TransformColumnTypes(Source,{{"Date/Time", type datetime}}),
    AddCustom = Table.AddColumn(ChangeType, "Data", each {[Data Point 1],[Data Point 2]}),
    RemoveColumns = Table.RemoveColumns(AddCustom,{"Data Point 1", "Data Point 2"}),
    ExpandData = Table.ExpandListColumn(RemoveColumns, "Data"),
    AddIndex = Table.AddIndexColumn(ExpandData, "Index", 0, 1, Int64.Type),
    AddCustom1 = Table.AddColumn(AddIndex, "Custom", each Text.From([#"Date/Time"]) & (if Number.IsEven([Index]) then " DP1" else " DP2")),
    RemoveColumns1 = Table.RemoveColumns(AddCustom1,{"Date/Time", "Index"}),
    PivotColumn = Table.Pivot(RemoveColumns1, List.Distinct(RemoveColumns1[Custom]), "Custom", "Data")
in
    PivotColumn

 

[DerekK]

Terrific, thank you, JGordon11!

 

[alex78]

Good day,

In Power Query, I need to go from this:

Serial Number	Date/Time	Data Pont 1	Data Point 2
0001	08/14/2024 10:00	DP1A	DP2A
0001	08/14/2024 10:10	DP1B	DP2B
0001	08/14/2024 10:20	DP1C	DP2C
0001	08/14/2024 10:30	DP1D	DP2D
0002	08/14/2024 10:00	DP1A	DP2A
&c.

to this:

Serial Number	08/14/2024 10:00	08/14/2024 10:00	08/14/2024 10:10	08/14/2024 10:10	08/14/2024 10:20	08/14/2024 10:20	08/14/2024 10:30	08/14/2024 10:30
	Data Point 1	Data Point 2	Data Point 1	Data Point 2	Data Point 1	Data Point 2	Data Point 1	Data Point 2
0001	DP1A	DP2A	DP1B	DP2B	DP1C	DP2C	DP1D	DP2D
0002	DP1A	DP2A	DP1B	DP2B	DP1C	DP2C	DP1D	DP2D
&c.

Basically, pivoting the "Date/Time" column with both the "Data Point 1" and "Data Point 2" columns as values.

I hope this makes sense; hope you can help!

Sincerely,



Derek

Hi Derek,
Another solution for fun.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    S_cols = Table.ColumnNames(Source),
    Grouped = Table.Group(Source, S_cols{0}, {{"x", each
        let
        a = List.Union(Table.ToColumns (Table.SelectColumns(_, List.Skip(S_cols,2)))), 
        b = List.Union(List.Accumulate(List.Skip(S_cols,2), {},(s,c)=> s & {List.Transform(Table.Column(_,S_cols{1}), each Text.From(_) & " - " & c) }))
        in Table.FromRows({a},b)}}),
    Result = let F_cols = List.Sort(List.Distinct(List.Combine(List.Transform(Grouped[x], Table.ColumnNames)))) 
             in Table.ExpandTableColumn(Grouped, "x", F_cols)
in
    Result

Regards,

 

[DerekK]

Appreciate it!

Now, would some patient soul be kind enough to explain exactly what took place to accomplish this, viz., what each step did?

I tried to reproduce it just by doing individual steps instead of pasting in a slightly revised version of the code but I was not able.

 

[alex78]

Please see attempt to split code with explanation step by step.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    // list of Source's columns name
    S_cols = Table.ColumnNames(Source),
    // grouped Source per "Serial Number" - 1st item of S_cols list (S_cols{0})
    Grouped = Table.Group(Source, S_cols{0}, 
              {
                  // col a: list of values for 2 last Source's columns per group
                  {"a", each List.Union(Table.ToColumns (Table.SelectColumns(_, List.Skip(S_cols,2))))}, 
                  // col b: list of columns name through list.accumulate to add the column name suffix for each date per group
                  {"b", each List.Union(List.Accumulate(List.Skip(S_cols,2), {},(s,c)=> s & {List.Transform(Table.Column(_,S_cols{1}), each Text.From(_) & " - " & c)}))}
              }),
    // create table from columns a and b          
    #"Added Custom" = Table.AddColumn(Grouped, "x", each Table.FromRows({[a]},[b])),
    // list of sorted columns name to dynamically expand column x in next step
    F_cols = List.Sort(List.Distinct(List.Combine(List.Transform(#"Added Custom"[x], Table.ColumnNames)))),
    Expanded = Table.ExpandTableColumn (#"Added Custom", "x", F_cols),
    #"Removed Columns" = Table.RemoveColumns(Expanded,{"a", "b"})
in
    #"Removed Columns"

Hopefully this helps.

Regards,