How to extract coordinates from a column in Power Query

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

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