cancel
Showing results 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

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!

2 ACCEPTED SOLUTIONS
Super User

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),

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

Regards

Phil

If I helped you, click on the Thumbs Up to give Kudos.

Proud to be a Super User!

Super User

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)
],
[ 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.

6 REPLIES 6
Super User

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)
],
[ 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.

Frequent Visitor

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

Super User

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.

Super User

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),

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

Regards

Phil

If I helped you, click on the Thumbs Up to give Kudos.

Proud to be a Super User!

Frequent Visitor

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 👍😊

Super User

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

If I helped you, click on the Thumbs Up to give Kudos.

Proud to be a Super User!

Announcements

#### 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.

#### Power BI Monthly Update - July 2024

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

#### Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors