Creating multiple custom columns using the same multiplier (i.e. unit conversion)

virtualdynamo

New member
Joined
Jan 16, 2022
Messages
14
Reaction score
0
Points
0
Location
Kansas City, MO
Excel Version(s)
Microsoft 365 MSO
I have 4 fields in Garmin GPS coordinates that I need to convert to degree decimal by multiplying by 2^32/360 à la:
https://gis.stackexchange.com/questions/371656/garmin-fit-coodinate-system

As this kind of thing is going to happen a lot, the generic question is: How does one do unit conversions in PQ? In this specific case, I've brute forced it as follows, but I can't believe this is the most efficient or elegant solution:

Code:
let
    Source = #"GPS-lap-staging",
    #"Inserted lap.start_lat" = Table.AddColumn(Source, "lap.start_lat", each [GPS.lap.start_lat] * 360 / Number.Power(2, 32), type number),
    #"Inserted lap.start_long" = Table.AddColumn(#"Inserted lap.start_lat", "lap.start_long", each [GPS.lap.start_long] * 360 / Number.Power(2, 32), type number),
    #"Inserted lap.end_lat" = Table.AddColumn(#"Inserted lap.start_long", "lap.end_lat", each [GPS.lap.end_lat] * 360 / Number.Power(2, 32), type number),
    #"Inserted lap.end_long" = Table.AddColumn(#"Inserted lap.end_lat", "lap.end_long", each [GPS.lap.end_long] * 360 / Number.Power(2, 32), type number)
in
    #"Inserted lap.end_long"
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,519
Reaction score
4
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
Well, the most elegant solution would be Garmin providing the correct output for you needs so that you didn't need it to be transformed. But since they won't...
The solution you've cooked up is exactly what I would do. One operation per column you need to convert.
The only other thing you could do differently here would be to write a custom power query function to do the math, but you'll still need to invoke it onve for each column anyway. If you need to do dozens of columns it might be worth the time to do this, as you wouldn't need to enter the full formula each time, UT if you've only got 4 columns, I wouldn't bother. It won't be any more efficient to execute as it calls the same math pattern each time you run it, and will take you more time to build than just copying and pasting in the formula I expect.
 

virtualdynamo

New member
Joined
Jan 16, 2022
Messages
14
Reaction score
0
Points
0
Location
Kansas City, MO
Excel Version(s)
Microsoft 365 MSO
Thanks for the insight. In the future, I may create PQ functions for English/metric conversions I'll need frequently. (Which then begs the question on how to put such functions in a library, but that's for another day.)
 
Top