cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
hokiefan1113 Regular Visitor
Regular Visitor

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
hokiefan1113 Regular Visitor
Regular Visitor

POST http request with XML content as a data source

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

Super User
Super User

Re: Using XML API as data source

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"

I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

hokiefan1113 Regular Visitor
Regular Visitor

Re: Using XML API as data source

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!

ferdsjoseph Regular Visitor
Regular Visitor

Re: Using XML API as data source

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

Highlighted
SasaOvie Visitor
Visitor

Re: Using XML API as data source

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.

Helpful resources

Announcements
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Top Kudoed Authors
Users Online
Currently online: 193 members 1,866 guests
Please welcome our newest community members: