Forum Discussion
Solved
Data Cleaning
Hi all,
I have a dataset similar to format:
| Run Time | null | null | 2:24:21 | 60% | null |
| Run Time | null | 3:41:08 | 32% | null | null |
| Run Time | null | null | 1:23:12 | 12% | null |
| Run Time | null | null | null | 3:23:39 | 19% |
The position of the data is not same across each row. I need to convert this table to format:
| Run Time | 2:24:21 |
| Run Time | 3:41:08 |
| Run Time | 1:23:12 |
| Run Time | 3:23:39 |
Can someone please advise how to dynamically solve this problem? Thank you!
You should be able to select your first column, right click and choose unpivot other columns. You can then remove the Attribute column, and filter out values <1 (the % values), to get close to your result.
Pat
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCirNUwjJzE1V0lECIgMjKyMTKyNDINPMQBUkFquDosbA2MrE0MrAAsg0NlKFaEJXA1JmaGVkbGVoBGQaGmEzB6rMGKTM2BKkzFJVKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t]), Custom1 = Table.CombineColumns(Source, List.Skip(Table.ColumnNames(Source)), each List.Select(_, each Text.Contains(_, ":")){0}, "Duration") in Custom1
4 Replies
You should be able to select your first column, right click and choose unpivot other columns. You can then remove the Attribute column, and filter out values <1 (the % values), to get close to your result.
Pat
Thank you!
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCirNUwjJzE1V0lECIgMjKyMTKyNDINPMQBUkFquDosbA2MrE0MrAAsg0NlKFaEJXA1JmaGVkbGVoBGQaGmEzB6rMGKTM2BKkzFJVKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t]), Custom1 = Table.CombineColumns(Source, List.Skip(Table.ColumnNames(Source)), each List.Select(_, each Text.Contains(_, ":")){0}, "Duration") in Custom1Thank you!
