Search code examples
bing-mapspowerbipowerquerym

Power BI create column from nested Record values

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.

enter image description here

enter image description here enter image description here

enter image description here

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

1. Get data from the web

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

enter image description here

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

enter image description here

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

enter image description here

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

6. In the target table, Navigate to Add Column > Invoke Custom Function enter image description here

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. enter image description here

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

enter image description here

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!

Solution

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

    1. 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.

    2. 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.

    result

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