Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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.
Solved! Go to Solution.
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
Proud to be a Super User!
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
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |