1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

UN LOCODE - DMS to Latitude and Longitude - M Query

Discussion in 'The Vault' started by chirayu, Oct 23, 2018.

  1. chirayu

    chirayu Well-Known Member

    Messages:
    928
    Hi All,

    I thought I'd post this here as it might be useful to someone.

    For those of you wondering what M Query is, it is the language used by Power BI when loading data. Essentially it is Power Query (dunno why also know as M Query but whatever)

    Basically this is an M Query Code to convert DMS (Degrees Minutes Seconds) from the UNECE dataset (published yearly) to Latitude and Longitude (Decimal)

    Please note that it does remove what i consider to be unnecessary columns; but you can just delete the relevant steps out once you load the Query if you want to keep them.

    So the code can be used to load data into Excel or Power BI.

    Add to Excel:
    Open Excel > Go to Data tab > Click on Get Data button > Click on From Other Sources option > Click on Blank Query option > Click on View tab > Click on Advanced Editor > Paste Query from attachment > Click on Done > Click on Home tab > Click on Close & Load button

    Add to Power BI:
    Open Power BI for Desktop > Click on Get Data button > Click on Other option > Click on Blank Query option > Click on View tab > Click on Advanced Editor > Paste Query from attachment > Click on Done > Click on Home tab > Click on Close & Apply button

    Main use for this would be stuff for geographical outputs e.g. Sales by location etc. plotted to a Map visualisation

    Attached Files:

Share This Page