Power BI create column from nested Record values

Question

I'm trying to create a new column from one of the records in a List type column. The value is the country that corresponds with the Latitude and Longitude fields. The information is retrieved from the Bing Map API, which got using Get Data from Web (following the tutorial here: https://sqldusty.com/2016/04/26/power-bi-and-the-bing-maps-api/).

Basically, I need List.Record[1].address.countryRegion. Is it possible to make a column that holds this specific value without doing "Expand to new rows"? The issue is that some of the columns come back with France, and the number of rows increases to over 1000 but there should only be around 250.

Here is how I got to the point of having the column of Lists:

1. Get data from the web

2. Used Basic option and pasted working API request for a location with my bing maps key. Then click ok.

http://dev.virtualearth.net/REST/v1/Locations/point=40,-122?&key=BingMapsKey

 3. Navigated to the Advanced editor in View > Advanced editor.

4. Made a function that uses Latitude and Longitude as input

let getCountry = (Latitude as text, Longitude as text) =>

let

    Source = Json.Document(Web.Contents("http://dev.virtualearth.net/REST/v1/Locations/point="& Latitude &","& Longitude &"?&key=BingMapsKey"))

in

    Source

in

    getCountry

 5. Renamed the function to GetCountry, then navigated to the desired table to add the column to (with Latitude and Longitude) 

6. In the target table, Navigate to Add Column > Invoke Custom Function 

7. Chose GetCountry from the list of functions, changed the type to the column name and assigned the inputs to respective column names (latitude and longitude). Then clicked OK. 

8. The column shows up on the right. I filtered out all columns besides 'resourceSets' because that has the address values.

EDIT I found a way to reduce the number of lists that are returned in the request, which is to only request the Country/Region as a query parameter:

http://dev.virtualearth.net/REST/v1/Locations/40,-122?key=BingMapsKey&includeEntityTypes=CountryRegion

This works for my needs for now, but maybe it's a good idea to keep this open to see if someone knows how to make a table from the nested table values? Thanks for all your help! 

Answer 1

• It is an XY Problem 

• The Table.ExpandListColumn function expands records to multiple rows because there are indeed multiple rows of records returned from the API endpoint.

• Unless you apply filter logic afterward, there are no ways for the code to understand which row of records to choose.

• There shouldn't be multiple rows of records returned from the API. (Find the countryRegion for a given (lat, long))

• So after reading through the question, the real problem lies in the API endpoint you're using.

• It should be

http://dev.virtualearth.net/REST/v1/Locations/40,-122?key=yourAPIKey

instead of

http://dev.virtualearth.net/REST/v1/Locations/point=40,-122?key=yourAPIKey

• The point= is not needed. (Yes, the documentation is slightly confusing)

• So you can update your GetCountry function as follows:

let getCountry = (Latitude as text, Longitude as text) =>

let

    Source = Json.Document(Web.Contents("http://dev.virtualearth.net/REST/v1/Locations/"& Latitude &","& Longitude &"?key=yourAPIKey"))

in

    Source

in

    getCountry

• It is better not to expose your API Key to public

• As a result, there should only be one countryRegion for each place.

• My query for your reference:

let

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs/PT89JLchJrVDSUTI21zMxMjIwMACydQ2NjPQMLEwMTM2VYnWilRwLgIoUPPPSMvMyS1IVfPLzCyA6jI0NzczN4DqMDQwtLJViYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Place = _t, Lat = _t, Long = _t]),

    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Place", type text}}),

    #"Invoked Custom Function" = Table.AddColumn(#"Changed Type", "GetCountry", each GetCountry([Lat], [Long])),

    #"Expanded GetCountry" = Table.ExpandRecordColumn(#"Invoked Custom Function", "GetCountry", {"resourceSets"}, {"GetCountry.resourceSets"}),

    #"Expanded GetCountry.resourceSets" = Table.ExpandListColumn(#"Expanded GetCountry", "GetCountry.resourceSets"),

    #"Expanded GetCountry.resourceSets1" = Table.ExpandRecordColumn(#"Expanded GetCountry.resourceSets", "GetCountry.resourceSets", {"resources"}, {"resources"}),

    #"Expanded resources" = Table.ExpandListColumn(#"Expanded GetCountry.resourceSets1", "resources"),

    #"Expanded resources1" = Table.ExpandRecordColumn(#"Expanded resources", "resources", {"address"}, {"address"}),

    #"Expanded address" = Table.ExpandRecordColumn(#"Expanded resources1", "address", {"countryRegion"}, {"countryRegion"})

in

    #"Expanded address"

Want to become a Business Analyst? Then have a look at Power BI training course from Intellipaat!