The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
Solved! Go to 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.
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.
i will work on it and do the needful
thank you
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.
Have I solved your problem? Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;). |
If you found this post helpful, please give Kudos. It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen. If you find my signature vaguely amusing, please give Kudos. | Proud to be a Super User! |
i shall and if it works i will post it here
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
34 | |
20 | |
19 | |
17 | |
13 |