BING API INVOKE FUNCTION

[Peter_Verweij]

Hello,

I am struggeling with to get the LAT and LONG from the BING API.

I am following this video: https://www.youtube.com/watch?v=uZoH4wITCH0

But at 11:45 when i invoke custom column i get an error.

This is the error:

DataFormat.Error: De verwerking van het xml-item is mislukt. De invoer is ongeldig of de bewerking wordt niet ondersteund. (Interne fout: Gegevens op hoofdniveau zijn ongeldig. Regel 1, positie 1.)
Details:
[Binary]

My translation:

DataFormat.Error: Xml processing failed. Either the input is invalid or it isn't supported.

(Internal error:  Rootelements are invalid. row 1, position 1.)

the first step that i did was to test the API from an own created link like this:

And it gave me all the information back.

The URL

http://dev.virtualearth.net/REST/v1/Locations?countryRegion="Netherlands"&"&AdminDistrict2="Krimpen aan den IJssel""&postalCode="2922"&addressLine="Sleepvaart "&o=xml&key=BING KEY

Some info of the respons

<Address>

<AddressLine>Sleepvaart</AddressLine>

<AdminDistrict>South Holland</AdminDistrict>

<AdminDistrict2>Krimpen aan den IJssel</AdminDistrict2>

<CountryRegion>Netherlands</CountryRegion>

<FormattedAddress>Sleepvaart, 2922 DD Krimpen aan den IJssel</FormattedAddress>

<Locality>Krimpen aan den IJssel</Locality>

<PostalCode>2922 DD</PostalCode>

</Address>

Then i used the URL in Power BI through the WEB connector.

It imported the same information.

Next step was to get rid of the hardcoded lines.

So i used this in the function from the video:

let
Location = ( Place as text, Postalcode as text, Adres as text) =>
let
Bron = Xml.Tables(Web.Contents("http://dev.virtualearth.net/REST/v1/Locations?countryRegion=""Netherlands"""
&"AdminDistrict2=" &Place
&"postalCode=" &Postalcode
&"addressLine=" &Adres
&"o=xml&key=BINGKEY")),

#"Type gewijzigd" = Table.TransformColumnTypes(Bron,{{"Copyright", type text}, {"BrandLogoUri", type text}, {"StatusCode", Int64.Type}, {"StatusDescription", type text}, {"AuthenticationResultCode", type text}, {"TraceId", type text}}),
#"Andere kolommen verwijderd" = Table.SelectColumns(#"Type gewijzigd",{"ResourceSets"}),
#"ResourceSets uitgevouwen" = Table.ExpandTableColumn(#"Andere kolommen verwijderd", "ResourceSets", {"ResourceSet"}, {"ResourceSet"}),
#"ResourceSet uitgevouwen" = Table.ExpandTableColumn(#"ResourceSets uitgevouwen", "ResourceSet", {"Resources"}, {"Resources"}),
#"Resources uitgevouwen" = Table.ExpandTableColumn(#"ResourceSet uitgevouwen", "Resources", {"Location"}, {"Location"}),
#"Location uitgevouwen" = Table.ExpandTableColumn(#"Resources uitgevouwen", "Location", {"EntityType", "Address", "Confidence", "GeocodePoint"}, {"EntityType", "Address", "Confidence", "GeocodePoint"}),
#"Address uitgevouwen" = Table.ExpandTableColumn(#"Location uitgevouwen", "Address", {"FormattedAddress"}, {"FormattedAddress"}),
#"GeocodePoint uitgevouwen" = Table.ExpandTableColumn(#"Address uitgevouwen", "GeocodePoint", {"Latitude", "Longitude", "UsageType"}, {"Latitude", "Longitude", "UsageType"}),
#"Rijen gefilterd" = Table.SelectRows(#"GeocodePoint uitgevouwen", each true)
in
#"Rijen gefilterd"
in
Location

Table where i invoke a custom column

Adres	Place	Postalcode
Sleepvaart	Krimpen aan den Ijssel	2922
Toccata	Krimpen aan den Ijssel	2925
Vijverlaan	Krimpen aan den Ijssel	2923

what i see is that the first line of the fuction code is not displayed in the small formula box.

this row: l

et
Location = ( Place as text, Postalcode as text, Adres as text) =>

is shown as:

 = ( Place as text, Postalcode as text, Adres as text) =>

This is the same as in the video and when i open the advanced editor it shows it correct.

[PhilipTreacy · Accepted solution]

Hi @Peter_Verweij 

The Web.Contents call needs to be modified slightly.  Try this (I tested this on my own PC and it works)

BINGKEY = "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx",
Bron = Xml.Tables(Web.Contents("http://dev.virtualearth.net/REST/v1/Locations?countryRegion=Netherlands"
&"&AdminDistrict2=" &Place
&"&postalCode=" &Postalcode
&"&addressLine=" &Adres
&"&o=xml&key="&BINGKEY)),

Regards

Phil

[Peter_Verweij]

Thank you so much @PhilipTreacy 

I just coundt find it, but you did 😉

i am so happy.