CODEWITHSUNDEEP
powerbivisualizationlatitude-longitudepowerbi-desktopcustom-function
KARTHIKEYAN.A
KARTHIKEYAN.A

Reputation: 20118

How to find Country lat, long based on Country Name on Power BI?

I'm trying to find the Country lat, long to visualize the Country in the world map on Power BI.

enter image description here

Please suggest me the procedure to find lat, long on PowerBI or any APIs available from PowerBI tool.

Upvotes: 1

Views: 4813

Answers (1)

KARTHIKEYAN.A
KARTHIKEYAN.A

Reputation: 20118

First, we have to use any API service to get lat-long

Create bingmapsportal account

Here I'm using Microsoft bing maps API services Go to BingMapsPortal account to SignUP account if you already don’t have enter image description here

After SingUp we have login it will redirect to dashboard

enter image description here

Generate key Once we reached dashboard page we have to generate key to use restful api services

enter image description here Once keys is ready then refer the document to find the api to get the lat and long based on given country

enter image description here

We use below give url to get lat and long in xml format

http://dev.virtualearth.net/REST/v1/Locations/india?o=xml&key=AjvYaTSLr8dsu4eqeDt0OigOZ_xuTkdVMUQCDMc0gcDPm

Use virtualearth API service to get lat and long of the location Once data is available then we have to convert that into tabular form enter image description here

Create Invoke Custom function If we need to get multiple countries' dashboard then we have to write custom invoke functions such as given below and save. enter image description here

= (location) =>

let
    Source = Xml.Tables(Web.Contents("http://dev.virtualearth.net/REST/v1/Locations/"&location&")?o=xml&key=AjvYaTSLr8dsu4eqeDt0OigOZ_xuTkdVMUQCDMc0gcDPmj2m57iWiwasSDZSCoNG")),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Copyright", type text}, {"BrandLogoUri", type text}, {"StatusCode", Int64.Type}, {"StatusDescription", type text}, {"AuthenticationResultCode", type text}, {"TraceId", type text}}),
    ResourceSets = #"Changed Type"{0}[ResourceSets],
    ResourceSet = ResourceSets{0}[ResourceSet],
    #"Changed Type1" = Table.TransformColumnTypes(ResourceSet,{{"EstimatedTotal", Int64.Type}}),
    Resources = #"Changed Type1"{0}[Resources],
    #"Expanded Location" = Table.ExpandTableColumn(Resources, "Location", {"Name", "Point", "BoundingBox", "EntityType", "Address", "Confidence", "MatchCode", "GeocodePoint"}, {"Location.Name", "Location.Point", "Location.BoundingBox", "Location.EntityType", "Location.Address", "Location.Confidence", "Location.MatchCode", "Location.GeocodePoint"}),
    #"Location Point" = #"Expanded Location"{0}[Location.Point],
    #"Changed Type2" = Table.TransformColumnTypes(#"Location Point",{{"Latitude", type number}, {"Longitude", type number}})
in
    #"Changed Type2"

Use lat long to visualize maps Use that custom invoke function to get multiple lat long by creating new custom column in table

enter image description here Later we have to convert embedded table data to column data

enter image description here

To show Country and count legend without mouse over we have created custom legend column Using the below query

Syntax: 

State Count COLUMN = 'Table'[State]&" - "&CALCULATE(SUM('Table'[Count]), ALLEXCEPT('Table', 'Table'[State]))

enter image description here Once data is ready on the table then we have to drag and drop the proper value on location, legend, values.

enter image description here

Upvotes: 1

Related Questions