How to extract coordinates from a column in Power Query

[AlyafeiAli]

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!

[PhilipTreacy · Accepted solution]

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"

Regards

Phil

[dufoq3 · Accepted solution]

Hi @AlyafeiAli, different approach here:

Result with correct order Lat and Long

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

[AlyafeiAli]

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

[dufoq3]

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

[PhilipTreacy · Accepted solution]

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"

Regards

Phil

[AlyafeiAli]

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]

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