Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
101 | |
50 | |
19 | |
12 | |
11 |