Forum Discussion
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!
Hi AlyafeiAli
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
6 Replies
Hi AlyafeiAli
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 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 👍😊
Hi AlyafeiAli
What version of Excel have you got? M365 on the Beta Channel supports regex with some new functions that recently came out
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 SplitColumnByDelimiterNice! Thank you so much!
is it possible to accept two answers as solutions?You're welcome (and yes, it is).
