XML with XSD schema

[odevriend]

Dear Community members,

I run into the following problem.
I have a XML with a XSD schema (same like XBRL instances), when configuring the upload with a sample file all available columns are available and uploaded to Power BI.
However the problem occurs if a XML tag is not available, which is fine as the XSD says it is an optional field.

I use the following function to get the columns:

Table.ExpandTableColumn(#"Removed Other Columns", "header", {"fiscalYear", "startDate", "endDate", "curCode", "dateCreated", "softwareDesc", "softwareVersion"})

So in my specific case softwareVersion is missing, but it could also be another column

Can anybody tell me how to solve this issue, or idealy test against the XSD in importing the XML?

Many thanks in advance,

Olaf

[Anonymous]

@odevriend,

Could you please share the XML file so that I can reproduce? Also please check if you can expand all the columns using Power Query as described in the blog below.

https://blog.crossjoin.co.uk/2014/05/21/expanding-all-columns-in-a-table-in-power-query/

Regards,

[odevriend]

Please find the link to a ZIP file containing all the specifications (including XSD file and a test XML file)

http://www.ictplaza.nl/uploads/xml_auditfiles/xmlfinancieel/20140402_AuditfileFinancieelVersie_3_2.z...

The XML file is the basis for datamodel / import in PowerBI.

When I try to load the following file with the pre-configured settings, it gives an error

https://github.com/odevriend/Analytics/blob/master/A_GENERAL/auditfiles/ExactOnline_V32_2016.xaf

This error is caused by the fact that the "softwareVersion" field in the "header" node is not available.
However due to the specifications given in the XSD, this is an optional field and it would be fine to fill the value with null

If there are more questions, please do not hesitate to contact me.

Many thanks in advance,

Olaf

[Anonymous]

@odevriend,

I am not familiar about XSD schema, could you please elaborate details about how to change the schema and detailed steps you perform in Power BI Desktop?

Regards,

[odevriend · Accepted solution]

Hi,

Please find more information about XSD on the following page:

https://en.wikipedia.org/wiki/XML_Schema_(W3C)

This morning I have completely rewritten the import of the various nested tables in the XML and I do not get an error anymore. The original file was written by someone else in a previous release op PowerBI, probably that is the source of the problem as I see completely specified columns in the functions where this is not the case in the functions I (automatically) created.

For me the problem is solved for this moment, so I will put the message to solved.

However if you know some way to handle errors within one field of a nested table this might be relevant for future problems. Till now I have only been able to handle errors on the table, so if one field within this table gives an error the complete table is not expanded.