Forum Discussion
GPX files to Power BI
I have GPX files loaded to Power BI and have two problems
- The files only incluedes start and end time. Meaning if the route started at 08:30:25 and ended at 08:55:25 i cant get the column to show actual time of the route only the time of the day
- I dont have the distance I only have Long and Lat points between each time it measures.
So my question is, how can I create a measure that calculates the actual time from start to end of each row for each race?
How can i track the distance between each captured points. Also needs to start from 0 when a new race is loaded to the dataset.
Below is what i have from the GPX file.
Hi Lewdis_ ,
I would do some additional columns on your model instead of measures directly that would allow you to do a simple sum in the model instead of iterating over all your column to get the final values.
I don't see the end time neither the lat and long of the arrival on the example you gave but I supposed that you have that column also on your model.
Add the following two columns on Power Query:
Duration will have the following code: [End]-[Time] Distance will have the following code: Number.Acos( Number.Sin([lat])*Number.Sin([lat2])+Number.Cos([lat])*Number.Cos([lat2])*Number.Cos([long2]-[Long]))*6371Final result:
Now you can do the calculations has you need based on the two new columns.
Concerning the start from 0 how do you identify each of the races? do you have an ID?
1 Reply
Hi Lewdis_ ,
I would do some additional columns on your model instead of measures directly that would allow you to do a simple sum in the model instead of iterating over all your column to get the final values.
I don't see the end time neither the lat and long of the arrival on the example you gave but I supposed that you have that column also on your model.
Add the following two columns on Power Query:
Duration will have the following code: [End]-[Time] Distance will have the following code: Number.Acos( Number.Sin([lat])*Number.Sin([lat2])+Number.Cos([lat])*Number.Cos([lat2])*Number.Cos([long2]-[Long]))*6371Final result:
Now you can do the calculations has you need based on the two new columns.
Concerning the start from 0 how do you identify each of the races? do you have an ID?