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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

When I connect to an XML URL, Power BI only returns document settings

Hi,

 

I am working with ProjectorPSA data. I ran a report, and they provide a means of connecting to the results of that report via a URL. e.g. website.com/xmlconnectiondetails.xml. When I navigate there in a browser, my data looks correct. However, when I try and put this url into Power BI, the editor returns a single record with the following data:

 

Kind             Name    Children    Text
Element      HTML     Table         null

 

So rather than bringing in the data on the page, it looks to me like it is bringing in the data describing the page. Hopefully that makes sense.

 

Any thoughts on how to fix this?

 

Thanks,

Scott

12 REPLIES 12
yesenita
Frequent Visitor

Something that helped me was changing "HTTP:/" to "HTTPS:/" in the URL, which prompts for credentials and therefore returns tables with data

 

Thanks to Angry Analytics for the help!

Greg_Deckler
Super User
Super User

You should have some little arrows in the column heading for Children that allow you to expand the table.


Follow on LinkedIn
@ 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!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

I can keep drilling into the table details, but it never gets to the xml for the data itself. here is a sample of what it drills down to:

 

Kind

Name

Children.Kind

Children.Name

Children.Children.Kind

Children.Children.Name

Children.Children.Children.Kind

Children.Children.Children.Name

Children.Children.Children.Children

Children.Children.Children.Text

Children.Children.Text

Children.Text

Text

Element

HTML

Element

HEAD

Text

null

null

null

null

null

 

null

null

Element

HTML

Element

HEAD

Element

TITLE

Text

null

null

Projector  | Log In

null

null

null

Element

HTML

Element

HEAD

Text

null

null

null

null

null

 

null

null

 

Thanks, though.

Scott

Same problem here. I have a field CompletionType.

XML:

Field Name: CompletionType Type: String Value: Online Completion

Power BI

Field Name: CompletionType Type: Any Value:  Table

Inside the table

Field Name: Element:Text  Value: Online Completion

Query related

#"Changed Type" = Table.TransformColumnTypes(Table4,{{"CompletionType", type any}})
in
    #"Changed Type"

 

 Can anyone help on this? The drill down and xml parser do not work out.

There's a way to do it called web scraping. But you have to know how the page is structured. This blog post really helped me a lot. Hope it will solve your issue. https://datachant.com/2017/03/30/web-scraping-power-bi-excel-power-query/

@Anonymous - Was just doing some more digging into this. Try editing your query and click on the gear for the Source step. Make sure that "Open File As" is set to "Xml Tables" and not HTML Page. Or, you could try "Text"


Follow on LinkedIn
@ 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!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

@Greg_Deckler - that got me perhaps closer? Instead of bringing in the html (you diagnosed it correctly), I receive the following error:

DataFormat.Error: Xml processing failed. Either the input is invalid or it isn't supported. (Internal error: Reference to undeclared entity 'nbsp'. Line 4, position 23.)
Details:
Binary

 

I have to log in to get to the XML, so I was hoping Power BI would prompt me for a log in, but this did not happen. So perhaps this is a credential issue at this point.

 

Thanks so much for your help!

I think that you're on the money with that one.


Follow on LinkedIn
@ 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!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

Thanks. Unfortunately, I do not see a way to force a log in for an XML file on the web, so i appear to be out of luck.

 

Best,

Scott

hello,

 

having the same type of need and problem.

 

did you manage to get it to work?

 

Thanks

J

I think you are reading it as an HTML document rather than an XML document. Power BI gets confused with web based ones a lot.  If feeding a url to the XML type query is being reverted to HTML, injesting it as text and using the "Parse XML" button in the "Transform" tab might work for you. 

 

Alex

Alex Potter
Lead Visualization Engineer
Luxoft Inc.

Yeah, when I told you that @Anonymous, I was thinking "well, I probably should have told him that he probably wasn't going to have much luck".

 

I have yet to really have very good success ingesting XML into Power BI in general. The way XML is structured is so hierarchical that it really doesn't translate to tables very well and you end up drilling down endlessly it seems like. It is almost like you would need to point to an XSL to translate the structure to something useable but since each XML is different...

 

If anyone has had great success, or even marginal success, with ingesting XML I'd love to know some tricks to making it work.

 


Follow on LinkedIn
@ 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!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors