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 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!

Can you elaborate on how you got your data from the Bing API and what function you are invoking to get a list of records? It would be nice to have some sample data as a starting point and then follow all the steps you took to get to your data. — Joe Gravelyn
@Joe I just updated the question to include how I got the data from the Bing API with the function used — user4192303

Foxan Ng Foxan Ng · Accepted Answer · 2017-10-18T06:16:28

This sounds like an XY Problem to me. Let me try to clarify:

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 afterwards, 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

(Side note: 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"

Hope it helps :)

Power BI create column from nested Record values

2 Answers