Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to 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
...or slightly cleaner...
= Table.FromRows( List.Select(List.Transform(Table.ToRows( PriorStepName ), List.RemoveNulls), each List.Count(_)>1 ), {"ID","Start","Duration","End"} )
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!
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