SOLVED

Importing the correct decimal data into a spreadsheet (latitude/longitude)

%3CLINGO-SUB%20id%3D%22lingo-sub-2821474%22%20slang%3D%22en-US%22%3EImporting%20the%20correct%20decimal%20data%20into%20a%20spreadsheet%20(latitude%2Flongitude)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2821474%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20everybody.%26nbsp%3B%3C%2FP%3E%3CP%3EQuestions%20about%20latitude%20and%20longitude%20were%20already%20asked%2C%20regarding%20cell%20formatting.%20My%20issue%20relates%20to%20the%20import%20of%20that%20data%20instead.%20I%20might%20be%20missing%20some%20math%20basics%20here%2C%20I%20am%20afraid.%3CBR%20%2F%3EI%20have%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fraw.githubusercontent.com%2Fjpatokal%2Fopenflights%2Fmaster%2Fdata%2Fairports.dat%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ethis%3C%2FA%3E%26nbsp%3Bset%20of%20data%20which%20comprises%20of%20airports%20and%20relative%20coordinates.%20I%20saved%20the%20page%20and%20imported%20the%20data%20as%20a%20comma%20separated%20one%20which%20then%20created%20the%20table%20as%20expected.%3C%2FP%3E%3CP%3EHowever%2C%20I%20realized%20that%20coordinates%20have%20their%20decimals%20all%20wrong.%3C%2FP%3E%3CP%3ETo%20illustrate%20this%2C%20in%20the%20original%20data%20%22BFS%22%2C%20Belfast%20is%20located%20in%26nbsp%3B54.6575012207%2C-6.2158298492399995%2C%20whereas%20my%20file%20is%20showing%20that%20as%20546.575.012.207%2C%20-62.158.298.492.399.900.%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20cells%20are%20now%20formatted%20as%20%22Number%22%3B%20I%20believe%20that%20perhaps%20I%20might%20need%20a%20custom%20cell%20format%20but%20I%20am%20worried%20I%20might%20get%20the%20wrong%20one.%20Any%20help%20would%20be%20highly%20appreciated.%3CBR%20%2F%3E%3CBR%20%2F%3EThank%20you%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EOriginal%20file%20vs%20my%20spreadsheet%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202021-10-07%20at%2011.28.30.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F315844i8632973FD018EB55%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%202021-10-07%20at%2011.28.30.png%22%20alt%3D%22Screenshot%202021-10-07%20at%2011.28.30.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202021-10-07%20at%2011.27.57.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F315845i69D8B72DBC02D4BA%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%202021-10-07%20at%2011.27.57.png%22%20alt%3D%22Screenshot%202021-10-07%20at%2011.27.57.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2821474%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2821671%22%20slang%3D%22en-US%22%3ERe%3A%20Importing%20the%20correct%20decimal%20data%20into%20a%20spreadsheet%20(latitude%2Flongitude)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2821671%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1178217%22%20target%3D%22_blank%22%3E%40Damian92%3C%2FA%3E%26nbsp%3BIf%20you%20connect%20to%20the%20link%20via%20PowerQuery%2C%20the%20import%20will%20produce%20the%20desired%20table.%20See%20attached.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2822523%22%20slang%3D%22en-US%22%3ERe%3A%20Importing%20the%20correct%20decimal%20data%20into%20a%20spreadsheet%20(latitude%2Flongitude)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2822523%22%20slang%3D%22en-US%22%3EWill%20have%20some%20reading%20to%20do%20on%20that%20tool.%20Totally%20forgot%20about%20it.%20Thank%20you.%3C%2FLINGO-BODY%3E
New Contributor

Hi everybody. 

Questions about latitude and longitude were already asked, regarding cell formatting. My issue relates to the import of that data instead. I might be missing some math basics here, I am afraid.
I have this set of data which comprises of airports and relative coordinates. I saved the page and imported the data as a comma separated one which then created the table as expected.

However, I realized that coordinates have their decimals all wrong.

To illustrate this, in the original data "BFS", Belfast is located in 54.6575012207,-6.2158298492399995, whereas my file is showing that as 546.575.012.207, -62.158.298.492.399.900. 

My cells are now formatted as "Number"; I believe that perhaps I might need a custom cell format but I am worried I might get the wrong one. Any help would be highly appreciated.

Thank you

 

Original file vs my spreadsheet

Screenshot 2021-10-07 at 11.28.30.png

Screenshot 2021-10-07 at 11.27.57.png

3 Replies
best response confirmed by Damian92 (New Contributor)
Solution

@Damian92 If you connect to the link via PowerQuery, the import will produce the desired table. See attached.

Will have some reading to do on that tool. Totally forgot about it. Thank you.