trying to correct the Latitude and longitude data

[Anonymous]

hello everyone.

i am trying to correct the latitude and longitude data in the power query but i am not finding a dax function for it.

i would like to correct the data by making it exact, meaning i am trying to get the accurate date which would look like this.

within excel i am able to do it without any issue i used the formual 

 =LEFT(D2,2)+(MID(D2,3,2)+MID(D2,5,6)/60)/60 (latitude ) and for long =-1*(LEFT(E2,2)+(MID(E2,3,2)+MID(E2,5,6)/60)/60), but i don't think i can use the same formula in power BI in the dax , so kindly tell me how i can do it ?

[v-jingzhang · Accepted solution]

Hi @Anonymous 

You can add two custom columns in Power Query Editor with below code.

Number.IntegerDivide([latitude],10000)+(Number.IntegerDivide(Number.Mod([latitude],10000),100)+Number.RoundDown(Number.Mod([latitude],100),0)/60)/60

(Number.IntegerDivide([longitude],10000)+(Number.IntegerDivide(Number.Mod([longitude],10000),100)+Number.RoundDown(Number.Mod([longitude],100),0)/60)/60)*(-1)

Result

Hope this helps.

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[Anonymous]

i will work on it and do the needful 

[Anonymous]

thank you

[KNP]

I'm not sure how your decimals shifted in the first place in the lat/long data but DAX is not really the correct place to do this data modelling. You should use Power Query (Transform Data).

Paste this into the advanced editor of a blank query...

// Table
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjE1NDUy1zMzMlHSUTIzMzAyNdAzsDBSio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [lat = _t, long = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"lat", type number}, {"long", type number}}),
    #"Divided Column" = Table.TransformColumns(#"Changed Type", {{"lat", each _ / 10000, type number}}),
    #"Divided Column1" = Table.TransformColumns(#"Divided Column", {{"long", each _ / 10000, type number}}),
    #"Multiplied Column" = Table.TransformColumns(#"Divided Column1", {{"long", each _ * -1, type number}})
in
    #"Multiplied Column"

or see attached PBIX.

[Anonymous]

i shall and if it works i will post it here