Results 1 to 3 of 3

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

  1. #1
    Seeker virtualdynamo's Avatar
    Join Date
    Jan 2022
    Location
    Kansas City, MO
    Posts
    14
    Articles
    0
    Excel Version
    Microsoft 365 MSO

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



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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/questi...odinate-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"

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,491
    Articles
    43
    Blog Entries
    14
    Excel Version
    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.
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book Master Your Data for Excel and Power BI, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Seeker virtualdynamo's Avatar
    Join Date
    Jan 2022
    Location
    Kansas City, MO
    Posts
    14
    Articles
    0
    Excel Version
    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.)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •