Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
metcala
Helper III
Helper III

Unpivot data

Hi

 

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

 

Data is currently structured as below

 

DateNameActivity 1 TypeActivity 1 TimeActivity 2 TypeActivity 2 TimeActivity 3 TypeActivity 3 Time
        

 

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

 

DateNameTypeTime
    

 

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!

2 ACCEPTED SOLUTIONS
Anonymous
Not applicable

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:

vjunyantmsft_0-1716953577695.png


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.

View solution in original post

AlienSx
Super User
Super User

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

View solution in original post

2 REPLIES 2
AlienSx
Super User
Super User

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
Not applicable

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:

vjunyantmsft_0-1716953577695.png


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.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Top Kudoed Authors