I am pulling several poorly formatted Excel files into PowerBi, these are pulled directly from a a machine so the formatting must remain the same.
The table looks like the below:
I need to create a conditional column for the start and end time of water so I can see it on a gantt chart or similar. Since they are not on the same row, I cannot do this.
I know how to do this via indexes, however since there are around 50 different examples of this, I don't think doing this is feasible. Are there any other methods I could use to achieve the result I want?