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

Question

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

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

Answer 1

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

After SingUp we have login it will redirect to dashboard

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

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

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

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.

= (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

Later we have to convert embedded table data to column data

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

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