Forum Discussion
Solved
Data Preparation
Hi all,
I have a dataset in format:
| 1 | Start: 9/23 | null | null | Duration: 6 | null | null | End: 9/24 | null |
| 2 | Start: 9/24 | null | null | Duration: 4 | null | End: 9/25 | null | null |
| 3 | Start: 9/27 | null | null | null | Duration: 8 | null | null | End: 9/27 |
I want to convert it to format:
| 1 | Start: 9/23 | Duration: 6 | End: 9/24 |
| 2 | Start: 9/24 | Duration: 4 | End: 9/25 |
| 3 | Start: 9/27 | Duration: 8 | End: 9/27 |
Positions of start, duration, end keeps changing. Can somebody please help me how to achieve the above table dynamically?
I would really appreciate your help. Thanks!
Hi anshpalash ,
Here is the whole M syntax, please check:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQouSSwqsVKw1DcyBvLySnNyEJRLaVFiSWZ+npWCGYaca14KWJcJTChWJ1rJCMVAEzwGmmCaZIqmHGSgMYqB5hgGYphrgdOh5kqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [A = _t, B = _t, C = _t, D = _t, E = _t, F = _t, G = _t, H = _t, L = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"A", Int64.Type}, {"B", type text}, {"C", type text}, {"D", type text}, {"E", type text}, {"F", type text}, {"G", type text}, {"H", type text}, {"L", type text}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"A"}, "Attribute", "Value"), #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value] <> "null")), #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom", each Text.Split([Value],":"){0}), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Attribute"}), #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Custom]), "Custom", "Value") in #"Pivoted Column"Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Hi anshpalash ,
Here is the whole M syntax, please check:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQouSSwqsVKw1DcyBvLySnNyEJRLaVFiSWZ+npWCGYaca14KWJcJTChWJ1rJCMVAEzwGmmCaZIqmHGSgMYqB5hgGYphrgdOh5kqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [A = _t, B = _t, C = _t, D = _t, E = _t, F = _t, G = _t, H = _t, L = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"A", Int64.Type}, {"B", type text}, {"C", type text}, {"D", type text}, {"E", type text}, {"F", type text}, {"G", type text}, {"H", type text}, {"L", type text}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"A"}, "Attribute", "Value"), #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value] <> "null")), #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom", each Text.Split([Value],":"){0}), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Attribute"}), #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Custom]), "Custom", "Value") in #"Pivoted Column"Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.