Data Cleaning

[anshpalash]

Hi,

I have a dataset in the form.

1	null	Start: 7:20	null	null	Duration: 3	null	End:10
1	null	null	null	null	null	null	null
1	null	null	null	null	null	null	null
1	null	null	null	null	null	null	null
2	Start: 6:30	null	null	Duration:5	null	null	End:11
2	null	null	null	null	null	null	null
2	null	null	null	null	null	null	null
2	null	null	null	null	null	null	null
2	null	null	null	null	null	null	null
3	null	null	null	null	null	null	null
3	null	null	null	null	null	null	null
3	Start: 9:15	null	null	Duration:4	null	End:1	null
3	null	null	null	null	null	null	null
3	null	null	null	null	null	null	null
3	null	null	null	null	null	null	null

I want to convert it to format:

1	Start: 7:20	Duration: 3	End:10
2	Start: 6:30	Duration:5	End:11
3	Start: 9:15	Duration:4	End:1

I would really appreciate if someone could advise how to achieve this since there is so much irregularity in the position of the start, duration, and end across different files. Thank you!

[Jakinta]

...or slightly cleaner...

= Table.FromRows( List.Select(List.Transform(Table.ToRows( PriorStepName ), List.RemoveNulls), each List.Count(_)>1 ), {"ID","Start","Duration","End"} )

[Syndicate_Admin · Accepted solution]

You can this step to your query.

= Table.FromRows( List.RemoveNulls( List.Transform(Table.ToRows(PriorStepName), each let l=List.RemoveNulls(_) in if List.Count(l)>1 then l else null) ), {"ID","Start","Duration","End"} )

Result

[anshpalash]

Thank you so much!

[Jakinta]

You can add this step to your query.

= Table.FromRows( List.RemoveNulls( List.Transform(Table.ToRows(PriorStepName), each let l=List.RemoveNulls(_) in if List.Count(l)>1 then l else null) ), {"ID","Start","Duration","End"} )

Result