Import simple XML into Power BI

Question

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

Answer 1

I can't duplicate what you are seeing when I try to load that XML file.

---

Here's what I'm doing. (I'm using Power BI.)

1. Choose Web under New Source and input the URL.

2. The query editor automatically interprets it as an XML table and loads it.

3. Click on the Table link in the result column and the query editor will expand that table and automatically change the column types to datetime, Int64.Type, and number.

---

Here's the full query code from these steps:

let
    Source = Xml.Tables(Web.Contents("https://resourcescrono.s3-eu-west-1.amazonaws.com/demo-biblio.xml")),
    Table = Source{0}[Table],
    #"Changed Type" = Table.TransformColumnTypes(Table,{{"Fecha", type datetime}, {"Unidades", Int64.Type}, {"Importe", type number}})
in
    #"Changed Type"