Forum Discussion
Solved
Data Cleaning
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!
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
4 Replies
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
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
Thank you so much!
...or slightly cleaner...
= Table.FromRows( List.Select(List.Transform(Table.ToRows( PriorStepName ), List.RemoveNulls), each List.Count(_)>1 ), {"ID","Start","Duration","End"} )
