Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
hokiefan1113
Advocate I
Advocate I

Using XML API as data source

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.

5 REPLIES 5
Anonymous
Not applicable

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.

Greg_Deckler
Super User
Super User

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"

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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? 

 

help.PNG

 

When I use the web connector and put the URL in, this is what happens. 

 

fail.PNG

 

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: 

error.PNG

 

I may be missing something obvious, but I'm rather new working with APIs as well as PBI, so I appreciate any help!

 

Thanks!

Anonymous
Not applicable

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

hokiefan1113
Advocate I
Advocate I

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!

post request.PNG

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors