Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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!
You should have some little arrows in the column heading for Children that allow you to expand the table.
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"
@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.
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.