CODEWITHSUNDEEP
xmlexcelpowerbipowerquerym
Pau
Pau

Reputation: 33

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:

How can get this data into my Power BI dashboard or Excel 2016?

Power BI screenshot

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

Upvotes: 3

Views: 7409

Answers (2)

Alejandro Lopez-Lago - MSFT
Alejandro Lopez-Lago - MSFT

Reputation: 4134

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.

Upvotes: 0

Alexis Olson
Alexis Olson

Reputation: 40214

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.

New Source > Web

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

XML Table

  1. 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.

Loaded Table

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"

Upvotes: 1

Related Questions