Import simple XML into Power BI
I am trying to import this simple XML data into Power BI (or Excel 2016):
https://resourcescrono.s3-eu-west-1.amazonaws.com/demo-biblio.xml
It's a simple XML, with a 3 column table (it even has the XSD schema attached). The same XML can be easily imported in former Excel versions (Excel 2003 and so on).
However, Power BI and Excel 2016 fails in multiple ways:
- It seems to ignore schema data
- It fails to detect invariant number formats.
How can get this data into my Power BI dashboard or Excel 2016?
My regional settings are spanish, but I don't know the regional setting of my users (may vary).
UPDATE: Microsoft has accepted it as a bug. Excel should respect XSD schema: https://community.powerbi.com/t5/Issues/Bug-importing-simple-XML-file/idc-p/429822
The number conversion issue comes from the Table.TransformColumnTypes call. Power Query will try to parse the number using the workbook/pbix's locale settings. Therefore, it tries to parse a number like "83647.49" using a Spanish locale, and since (Spanish/International) Spanish uses commas as the decimal separator, the number will be read as an oddly-formatted representation of "8364749".
The locale settings travel with the workbook/pbix, and you can set it in the Options dialog under Current Workbook | Regional Settings in Excel, and Current File | Regional Settings in PowerBI Desktop.
I don't believe we support noNamepsaceSchemaLocation, but if you'd like to see support please suggest it at the Power BI Ideas site.
- How to retrieve the second XML element with a specific attribute value in DataWeave 2.0
- Setting XML Namespace sets it empty in children
- Setting Namespace Attributes on an Element
- How to update Inno Setup AppVersion [Setup] value from Config.xml file
- Calculate weeknumber from a date value
- Jackson Xml parsing chooses attribute instead of tag
- Android App locale changes back to English on Theme Change
- LINQ/XML in C# - returning an XPATH count with XPathEvaluate
- How to add a state to a Selection field and show it ordered in the statusbar in Odoo 11?
- Don't get any result using Saxon Xpath 2.0
- ScrollView doesn't scroll to the bottom
- ASP.NET ASMX WebService custom namespace prefix; Building a dotnet service that communicates with both SOAP and REST services
- How to reformat XML layouts in Android Studio
- Can not read XML data from the file written with 'dataTable.WriteXml()' method
- No permissions found in Manifest [Android]
- Wix install path registry value assignment - what is wrong?
- Java & XML : clarification on using getNextSibling() vs getChildNode()
- Parse a xml file with multiple root element in python
- my xsd schema isn't validating: Invalid Content Was Found Starting With Element 'PRODUCTNAME'
- SAXParseException; src-resolve: Cannot resolve the name '...' to a(n) 'type definition' component
- How to change attribute to a specific non-alphabetic order using XML::Twig?
- Get the minimum value from XML set of nodes using XPath
- Convert NBB XBRL into DataFrame
- What is the difference between XML and AXML in Xamarin.Android Apps?
- How to insert "<<" text in TextView?
- How to get following sibling from xml using xslt from child node
- Illegal characters in path error when reading xml file
- C# XML documentation: dealing with the entirety
- How to get highest value from xml element in C#?
- xlsx error: "Removed Records: Named range from /xl/workbook.xml part" when tried to resolve errors