Unpivot data

[metcala]

Hi

I'm struggling to work out how to unpivot a data table to make it more usable.

Data is currently structured as below

Date	Name	Activity 1 Type	Activity 1 Time	Activity 2 Type	Activity 2 Time	Activity 3 Type	Activity 3 Time

I am trying to get the data into the following structure (I don't need to know the activity number).

Date	Name	Type	Time

My current thinking is to unpivot Activity 1 Type - Activity 3 Time, then replace values as "Type/Time" for each column then re-pivot.

Any help would be very much appreciated!

[Anonymous · Accepted solution]

Hi @metcala ,

Please try this way:
Here is the whole M function in the Advanced Editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtU3stQ3MjAyUdJRMjQyBpKOQAwUNYSJOoH5hnC+M4QP4cbGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Name = _t, #"Activity 1 Type" = _t, #"Activity 1 Time" = _t, #"Activity 2 Type" = _t, #"Activity 2 Time" = _t, #"Activity 3 Type" = _t, #"Activity 3 Time" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Name", Int64.Type}, {"Activity 1 Type", type text}, {"Activity 1 Time", type date}, {"Activity 2 Type", type text}, {"Activity 2 Time", type date}, {"Activity 3 Type", type text}, {"Activity 3 Time", type date}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Date", "Name"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Custom", each Text.End([Attribute],Text.Length([Attribute])-Text.PositionOfAny([Attribute],{"1".."9"}))),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Attribute"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns", "Custom", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Custom.1", "Custom.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Custom.1", Int64.Type}, {"Custom.2", type text}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type1", List.Distinct(#"Changed Type1"[Custom.2]), "Custom.2", "Value"),
    #"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Custom.1"})
in
    #"Removed Columns1"

And the final output is as below:

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AlienSx · Accepted solution]

let
    Source = your_table,
    to_list = Table.ToList(
        Source, 
        (w) => List.TransformMany(
            {w},
            (x) => List.Split(List.Skip(x, 2), 2),
            (x, y) => List.FirstN(x, 2) & y
        )
    ),
    to_table = #table({"Date", "Name", "Type", "Time"}, List.Combine(to_list))
in
    to_table

[Anonymous · Accepted solution]

Hi @metcala ,

Please try this way:
Here is the whole M function in the Advanced Editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtU3stQ3MjAyUdJRMjQyBpKOQAwUNYSJOoH5hnC+M4QP4cbGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Name = _t, #"Activity 1 Type" = _t, #"Activity 1 Time" = _t, #"Activity 2 Type" = _t, #"Activity 2 Time" = _t, #"Activity 3 Type" = _t, #"Activity 3 Time" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Name", Int64.Type}, {"Activity 1 Type", type text}, {"Activity 1 Time", type date}, {"Activity 2 Type", type text}, {"Activity 2 Time", type date}, {"Activity 3 Type", type text}, {"Activity 3 Time", type date}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Date", "Name"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Custom", each Text.End([Attribute],Text.Length([Attribute])-Text.PositionOfAny([Attribute],{"1".."9"}))),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Attribute"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns", "Custom", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Custom.1", "Custom.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Custom.1", Int64.Type}, {"Custom.2", type text}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type1", List.Distinct(#"Changed Type1"[Custom.2]), "Custom.2", "Value"),
    #"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Custom.1"})
in
    #"Removed Columns1"

And the final output is as below:

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.