The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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!
Solved! Go to 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.
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
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
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.