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

[Anonymous]

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

[yesenita]

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]

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

[Anonymous]

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

[sherryliu]

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.

[Dindo]

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/

[Greg_Deckler]

@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"

[Anonymous]

@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!

[Greg_Deckler]

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

[Anonymous]

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

[jagostinhoCT]

hello,

having the same type of need and problem.

did you manage to get it to work?

Thanks

J

[alex-potter]

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

[Greg_Deckler]

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.