Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I'm attempting to import data from an XML file we get exported from one of our systems. I have little experience with XML, so the error might be obvious. Excel opens it just fine and returns the following table, as expected with a bunch more columns (it exports info in Norwegian, sorry about that but not important for my issue)
When i attempt to import the same XML-file into PowerBI I run into trouble with all the information coming in a single column after expanding some columns.
The XML file looks like this (and keeps going)
<?xml version="1.0"?> <?mso-application progid="Excel.Sheet"?> -<Workbook xmlns:html="http(this part is secret)" xmlns:ss="(and this)" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:user="urn:my-scripts" xmlns:msxsl="urn:schemas-microsoft-com:xslt" xmlns="urn:schemas-microsoft-com:office:spreadsheet"> -<Styles> -<Style ss:ID="s21"> <NumberFormat ss:Format="Fixed"/> </Style> </Styles> -<Worksheet ss:Name="Ark1"> -<Table ss:ExpandedRowCount="1724" ss:ExpandedColumnCount="33"> <Column ss:Width="70" ss:AutoFitWidth="0"/> <Column ss:Width="180" ss:AutoFitWidth="0"/> <Column ss:Width="120" ss:AutoFitWidth="0"/> <Column ss:Width="100" ss:AutoFitWidth="0"/> <Column ss:Width="60" ss:AutoFitWidth="0"/> <Column ss:Width="80" ss:AutoFitWidth="0"/> <Column ss:Width="80" ss:AutoFitWidth="0"/> <Column ss:Width="80" ss:AutoFitWidth="0"/> <Column ss:Width="120" ss:AutoFitWidth="0"/> <Column ss:Width="150" ss:AutoFitWidth="0"/> <Column ss:Width="100" ss:AutoFitWidth="0"/> <Column ss:Width="150" ss:AutoFitWidth="0"/> <Column ss:Width="70" ss:AutoFitWidth="0"/> <Column ss:Width="120" ss:AutoFitWidth="0"/> <Column ss:Width="140" ss:AutoFitWidth="0"/> <Column ss:Width="140" ss:AutoFitWidth="0"/> <Column ss:Width="120" ss:AutoFitWidth="0"/> <Column ss:Width="140" ss:AutoFitWidth="0"/> <Column ss:Width="120" ss:AutoFitWidth="0"/> <Column ss:Width="120" ss:AutoFitWidth="0"/> <Column ss:Width="120" ss:AutoFitWidth="0"/> <Column ss:Width="120" ss:AutoFitWidth="0"/> <Column ss:Width="100" ss:AutoFitWidth="0"/> <Column ss:Width="100" ss:AutoFitWidth="0"/> <Column ss:Width="90" ss:AutoFitWidth="0"/> <Column ss:Width="90" ss:AutoFitWidth="0"/> <Column ss:Width="120" ss:AutoFitWidth="0"/> <Column ss:Width="100" ss:AutoFitWidth="0"/> +<Row> +<Row> +<Row> +<Row> +<Row> +<Row> -<Row> -<Cell> <Data ss:Type="String">03</Data> </Cell> -<Cell> <Data ss:Type="String">BRAVIDA NORGE AS</Data> </Cell> -<Cell> <Data ss:Type="String">34940</Data> </Cell> -<Cell ss:StyleID="s21"> <Data ss:Type="Number">220000.00</Data>
Can anyone see where I'm going wrong? Does anyone have a good idea how to fix this? Tempted to say the XML is broken, but since excel handles it I can't really use that as en excuse either.
Any help would be much appreciated.
Regards
H
@HaraldHBub,
Could you please upload the xml file to OneDrive and post shared link of the file here? The above XML file you post contains emoji.
Regards,
Lydia
There you go. This is a dummy XML as the other contains sensitive information. This is exported from the same system and should contain the same structure, just a much smaller dataset.
When I try to import and expand all my data comes in the Row query, Cell.Data.Element:Text column
Thank you!
Regards
Harald
@HaraldHBub,
I am unable to access the XML file, please share it in OneDrive and post shared link of it so that I can download the file from my side.
Regards,
Lydia
@HaraldHBub,
I get the following result when using Data->From Other Sources->From XML Data Import option in Excel. When I using Data->New Query-> From file-> From XML option Excel to import the data, I get same result as that in Power BI Desktop.
Regards,
Lydia
I get the same result as you using the data -> from other sources -> XML option, but i get the following result using the file -> open option, which is possible to work with and what i was hoping to get to in Power BI
@HaraldHBub,
I am not sure that how Excel parse the above result, and I haven't found solutions to get same result after importing XML file in Power BI Desktop.
In your scenario, I would recommend you save the xml file as .xlsx file after you open it in Excel, then directly connect to the Excel file in Power BI Desktop.
Regards,
Lydia
Yeah, that is the workaround I had to use, but it does give some issues with doing several exports and automating the import though. However thank you for trying 🙂
Regards
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.