Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I'm trying to pass an XML API as a datasource on Power BI, but I'm having a lot of trouble/have no idea how (I've tried multiple ways) as a data source to PBI desktop. Any suggestions or experience with this?
Thanks.
Have you been able to get this to work?
I am trying something similar(March 2019), but I just cannot figure out how to pass an XML body correctly within the request to get back an authentication token(I need to send credentials or something similar). I tried a lot of stuff, but i keep getting invalid XML or when I use Text.ToBinary it gives me the error of text could not be converted to binary(or the other way).
I kind of stopped trying for the moment, but there must be a way. I cannot sleep with the thought that Power BI cannot make a Post request to an XML API, sending a XML body within the request, with credentials or something else.
I have experience as a programmer. But not with Power BI and or Power Query and the language it uses(M).
Therefor it could be me. I hope you fixed your problem.
An example of what I am trying to pass in the request to an API URL:
<request> <client>xxxx</client> <username>xxxx</username> <password>xxxx</password> </request>
This returns a response with a token and a time(see below).
<response> <valid>yes</valid> <token>xxxx</token> <validUntil>xxxx</validUntil> </response>
The reason I am doing this within power bi, is because the rest of the requests(when i have the token) are also post-requests, which need to send a XML body within the request.
Juse use the Web connector:
let Source = Xml.Tables(Web.Contents("https://www.ncaa.com/news/football/fbs/rss.xml")), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Attribute:version", type number}}), #"Expanded channel" = Table.ExpandTableColumn(#"Changed Type", "channel", {"title", "link", "description", "language", "item"}, {"channel.title", "channel.link", "channel.description", "channel.language", "channel.item"}), #"Expanded http://www.w3.org/XML/1998/namespace" = Table.ExpandTableColumn(#"Expanded channel", "http://www.w3.org/XML/1998/namespace", {"Attribute:base"}, {"http://www.w3.org/XML/1998/namespace.Attribute:base"}), #"Expanded channel.item" = Table.ExpandTableColumn(#"Expanded http://www.w3.org/XML/1998/namespace", "channel.item", {"title", "link", "description"}, {"channel.item.title", "channel.item.link", "channel.item.description"}) in #"Expanded channel.item"
Hi @Greg_Deckler,
Thanks for your response. I've used web connector before, and it's worked for what I was doing (like in your example). But in this case it hasn't. I'm trying to post the XML request (in the first XML body) to the link in the screen shot. The bottom XML body is what I should be getting back. Is it even possible to post a local XML request on PBI?
When I use the web connector and put the URL in, this is what happens.
I then added ".xml" to the end of the URL, since the Attribute:Message column says "Valid types: text/xml, application/xml" and this happens:
I may be missing something obvious, but I'm rather new working with APIs as well as PBI, so I appreciate any help!
Thanks!
Hi @hokiefan1113,
I am trying to do exactly what you were trying to do back in June 2018. Were you able to make it work? I am lost as to where to enter the XML code that get posted to the URL.
Best regards,
Ferdinand
I'm trying to use an XML API with an http POST request as a data source. I'm having a hard time calling the POST request with the XML API in Power Query (which through a lot of googling/reading is what I'm finding the solution is). Any advice or experience with this? (Below is a screenshot from Postman)
Thanks!