Data Cleaning

[anshpalash]

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!

[mahoneypat · Accepted solution]

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

[CNENFRNL · Accepted solution]

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

[anshpalash]

Thank you!

[mahoneypat · Accepted solution]

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

[anshpalash]

Thank you!