Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
AlyafeiAli
Frequent Visitor

How to extract coordinates from a column in Power Query

Hi!

 

I'm trying to extract Longtitude and Latitude from the column "text" as shown below.  

Kindly note that there will be wrong coordinates, so latitude should strictly start with 22. , 23. , 24. , or 25.   - and longtitude should be between 51 and 56 as well ( 51. , 52. , 53. , 54. , 55. , and 56. ).  

Also please note that they vary in length, that's why I'm extracting only the first 4 digits after the period. 

Moreover, sometimes the user enters a comma instead of a period, like 23,434565 , 54,424534.

 

in regex logic, it should be something like this: `` ^2[2-5]\.[0-9]+`` or something like that lol.  

In excel I'm using this: `` SEARCH("2?.????", A2) `` 

 

Thank you!

 

AlyafeiAli_0-1720609422898.png

 

 

2 ACCEPTED SOLUTIONS
PhilipTreacy
Super User
Super User

Hi @AlyafeiAli 

 

Download example Excel file

 

I'm sure there's other ways to do it but I'm very tired after a long day so I quickly put this together

 

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"text", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",",",".",Replacer.ReplaceText,{"text"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Value", "Lat", each if Text.Contains([text], "22.") then Text.Middle([text], Text.PositionOf([text], "22."), 7) else 

if Text.Contains([text], "23.") then Text.Middle([text], Text.PositionOf([text], "23."), 7) else 

if Text.Contains([text], "24.") then Text.Middle([text], Text.PositionOf([text], "24."), 7) else 

if Text.Contains([text], "25.") then Text.Middle([text], Text.PositionOf([text], "25."), 7) else

null),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Long", each if Text.Contains([text], "51.") then Text.Middle([text], Text.PositionOf([text], "51."), 7) else 

if Text.Contains([text], "52.") then Text.Middle([text], Text.PositionOf([text], "52."), 7) else 

if Text.Contains([text], "53.") then Text.Middle([text], Text.PositionOf([text], "53."), 7) else 

if Text.Contains([text], "54.") then Text.Middle([text], Text.PositionOf([text], "54."), 7) else

if Text.Contains([text], "55.") then Text.Middle([text], Text.PositionOf([text], "55."), 7) else

if Text.Contains([text], "55.") then Text.Middle([text], Text.PositionOf([text], "55."), 7) else

null)
in
    #"Added Custom1"

 

latlong.png

 

Regards

 

Phil

 

 

 



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


View solution in original post

dufoq3
Super User
Super User

Hi @AlyafeiAli, different approach here:

 

Result with correct order Lat and Long

dufoq3_0-1720703796967.png

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VY5BCsIwEEWvMnRdgsaZJrmDK7dtF5UOGGiSkqTi8R0tQl3O5837v++ba8ocwK9lCzCnJWUovsIUuA5RoyJrHQKhOmtCi3DAh/jPN2P702mtiNAAaWVdR7D6FXwsdVoWIKdIosrl+LEP0KTQWClqpbGjT6P5Qjd+ci48A5EsuSBBSYFF8qogK501okz1wXnPpjhD3MJdbjopxGYc3w==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [text = _t]),
    Helper = [ lat = List.Transform({22..25}, Number.ToText),
    long = List.Transform({51..56}, Number.ToText)
  ],
    Ad_LatLong = Table.AddColumn(Source, "LatLong", each 
        [ a = Text.SplitAny([text], "#(lf) ,"),
          b = List.Select(a, (x)=> List.Contains(Helper[lat] & Helper[long], x, (y,z)=> Text.StartsWith(z,y))),
          c = if Value.FromText(b{0}, "en-US") < Value.FromText(b{1}, "en-US") then b else List.Reverse(b), //lat and long correct order
          d = Text.Combine(c, "|")
        ][d], type text ),
    SplitColumnByDelimiter = Table.SplitColumn(Ad_LatLong, "LatLong", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Lat", "Long"})
in
    SplitColumnByDelimiter

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

View solution in original post

6 REPLIES 6
dufoq3
Super User
Super User

Hi @AlyafeiAli, different approach here:

 

Result with correct order Lat and Long

dufoq3_0-1720703796967.png

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VY5BCsIwEEWvMnRdgsaZJrmDK7dtF5UOGGiSkqTi8R0tQl3O5837v++ba8ocwK9lCzCnJWUovsIUuA5RoyJrHQKhOmtCi3DAh/jPN2P702mtiNAAaWVdR7D6FXwsdVoWIKdIosrl+LEP0KTQWClqpbGjT6P5Qjd+ci48A5EsuSBBSYFF8qogK501okz1wXnPpjhD3MJdbjopxGYc3w==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [text = _t]),
    Helper = [ lat = List.Transform({22..25}, Number.ToText),
    long = List.Transform({51..56}, Number.ToText)
  ],
    Ad_LatLong = Table.AddColumn(Source, "LatLong", each 
        [ a = Text.SplitAny([text], "#(lf) ,"),
          b = List.Select(a, (x)=> List.Contains(Helper[lat] & Helper[long], x, (y,z)=> Text.StartsWith(z,y))),
          c = if Value.FromText(b{0}, "en-US") < Value.FromText(b{1}, "en-US") then b else List.Reverse(b), //lat and long correct order
          d = Text.Combine(c, "|")
        ][d], type text ),
    SplitColumnByDelimiter = Table.SplitColumn(Ad_LatLong, "LatLong", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Lat", "Long"})
in
    SplitColumnByDelimiter

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Nice! Thank you so much!  
is it possible to accept two answers as solutions?

You're welcome (and yes, it is).


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

PhilipTreacy
Super User
Super User

Hi @AlyafeiAli 

 

Download example Excel file

 

I'm sure there's other ways to do it but I'm very tired after a long day so I quickly put this together

 

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"text", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",",",".",Replacer.ReplaceText,{"text"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Value", "Lat", each if Text.Contains([text], "22.") then Text.Middle([text], Text.PositionOf([text], "22."), 7) else 

if Text.Contains([text], "23.") then Text.Middle([text], Text.PositionOf([text], "23."), 7) else 

if Text.Contains([text], "24.") then Text.Middle([text], Text.PositionOf([text], "24."), 7) else 

if Text.Contains([text], "25.") then Text.Middle([text], Text.PositionOf([text], "25."), 7) else

null),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Long", each if Text.Contains([text], "51.") then Text.Middle([text], Text.PositionOf([text], "51."), 7) else 

if Text.Contains([text], "52.") then Text.Middle([text], Text.PositionOf([text], "52."), 7) else 

if Text.Contains([text], "53.") then Text.Middle([text], Text.PositionOf([text], "53."), 7) else 

if Text.Contains([text], "54.") then Text.Middle([text], Text.PositionOf([text], "54."), 7) else

if Text.Contains([text], "55.") then Text.Middle([text], Text.PositionOf([text], "55."), 7) else

if Text.Contains([text], "55.") then Text.Middle([text], Text.PositionOf([text], "55."), 7) else

null)
in
    #"Added Custom1"

 

latlong.png

 

Regards

 

Phil

 

 

 



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


Hi @PhilipTreacy 

Thank you so much for the solution, I really appreciate it! 

As for your question, I'm not on the Beta channel so I can't use regex just yet.  

Thanks again 👍😊

PhilipTreacy
Super User
Super User

Hi @AlyafeiAli 

 

What version of Excel have you got?  M365 on the Beta Channel supports regex with some new functions that recently came out

 

Excel REGEX Functions

 

Regards

 

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors