Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
_n_MarianLein
Helper II
Helper II

Importing XML files with "Excel scheme" informatin

//Edit: Sorry for the emoji to appear... No idea how to fix this...

Dear all,

 

Thanks for having a look at this.

From a tool, I get an XML file that I need to import into Excel. Attached "user.xml" is the demo file I will be referencing.

 

File:

Spoiler
<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
          xmlns:o="urn:schemas-microsoft-com:office:office"
          xmlns:x="urn:schemas-microsoft-com:office:excel"
          xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
 <Styles>
  <Style ss:ID="s62">
   <Interior ss:Color="#8DB4E2" ss:Pattern="Solid"/>
  </Style>
  <Style ss:ID="s63">
   <NumberFormat ss:Format="dd/mm/yyyy\ hh:mm:ss"/>
  </Style>
 </Styles>
 <Worksheet ss:Name="CAM Export">
  <Table>
   <Column ss:Width="50"/>
   <Column ss:Width="100"/>
   <Column ss:Width="50"/>
   <Column ss:Width="100"/>
   <Column ss:Width="50"/>
   <Column ss:Width="50"/>
   <Column ss:Width="100"/>
   <Column ss:Width="100"/>
   <Row>
    <Cell ss:StyleID="s62"><Data ss:Type="String">Tool</Data></Cell>
    <Cell ss:StyleID="s62"><Data ss:Type="String">ToolDescription</Data></Cell>
    <Cell ss:StyleID="s62"><Data ss:Type="String">Level</Data></Cell>
    <Cell ss:StyleID="s62"><Data ss:Type="String">LevelDescription</Data></Cell>
    <Cell ss:StyleID="s62"><Data ss:Type="String">Profile</Data></Cell>
    <Cell ss:StyleID="s62"><Data ss:Type="String">Person</Data></Cell>
    <Cell ss:StyleID="s62"><Data ss:Type="String">PersonName</Data></Cell>
    <Cell ss:StyleID="s62"><Data ss:Type="String">Validity</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="String">Tool1</Data></Cell>
    <Cell><Data ss:Type="String">ToolDesc1</Data></Cell>
    <Cell><Data ss:Type="String">Level1</Data></Cell>
    <Cell><Data ss:Type="String">LevelDesc</Data></Cell>
    <Cell><Data ss:Type="String">Profile1</Data></Cell>
    <Cell><Data ss:Type="String">Approver1</Data></Cell>
    <Cell><Data ss:Type="String">Name1</Data></Cell>
    <Cell ss:StyleID="s63"><Data ss:Type="DateTime">2020-01-17T07:58:24Z</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="String">Tool2</Data></Cell>
    <Cell><Data ss:Type="String">ToolDesc2</Data></Cell>
    <Cell><Data ss:Type="String">Level1</Data></Cell>
    <Cell><Data ss:Type="String">LevelDesc</Data></Cell>
    <Cell><Data ss:Type="String">Profile2</Data></Cell>
    <Cell><Data ss:Type="String">Approver2</Data></Cell>
    <Cell><Data ss:Type="String">Name2</Data></Cell>
    <Cell ss:StyleID="s63"><Data ss:Type="DateTime">2020-01-07T08:40:41Z</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="String">Tool3</Data></Cell>
    <Cell><Data ss:Type="String">ToolDesc4</Data></Cell>
    <Cell><Data ss:Type="String">Level1</Data></Cell>
    <Cell><Data ss:Type="String">LevelDesc</Data></Cell>
    <Cell><Data ss:Type="String">Profile3</Data></Cell>
    <Cell><Data ss:Type="String">Approver3</Data></Cell>
    <Cell><Data ss:Type="String">Name3</Data></Cell>
    <Cell ss:StyleID="s63"><Data ss:Type="DateTime">2020-01-13T13:48:39Z</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="String">Tool3</Data></Cell>
    <Cell><Data ss:Type="String">ToolDesc4</Data></Cell>
    <Cell><Data ss:Type="String">Level1</Data></Cell>
    <Cell><Data ss:Type="String">LevelDesc</Data></Cell>
    <Cell><Data ss:Type="String">Profile4</Data></Cell>
    <Cell><Data ss:Type="String">Approver4</Data></Cell>
    <Cell><Data ss:Type="String">Name4</Data></Cell>
    <Cell ss:StyleID="s63"><Data ss:Type="DateTime">2020-01-04T19:48:28Z</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="String">Tool3</Data></Cell>
    <Cell><Data ss:Type="String">ToolDesc4</Data></Cell>
    <Cell><Data ss:Type="String">Level1</Data></Cell>
    <Cell><Data ss:Type="String">LevelDesc</Data></Cell>
    <Cell><Data ss:Type="String">Profile4</Data></Cell>
    <Cell><Data ss:Type="String">Approver4</Data></Cell>
    <Cell><Data ss:Type="String">Name4</Data></Cell>
    <Cell ss:StyleID="s63"><Data ss:Type="DateTime">2020-01-04T19:48:28Z</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="String">Tool3</Data></Cell>
    <Cell><Data ss:Type="String">ToolDesc4</Data></Cell>
    <Cell><Data ss:Type="String">Level1</Data></Cell>
    <Cell><Data ss:Type="String">LevelDesc</Data></Cell>
    <Cell><Data ss:Type="String">Profile3</Data></Cell>
    <Cell><Data ss:Type="String">Approver3</Data></Cell>
    <Cell><Data ss:Type="String">Name3</Data></Cell>
    <Cell ss:StyleID="s63"><Data ss:Type="DateTime">2020-01-13T13:48:39Z</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="String">Tool3</Data></Cell>
    <Cell><Data ss:Type="String">ToolDesc4</Data></Cell>
    <Cell><Data ss:Type="String">Level1</Data></Cell>
    <Cell><Data ss:Type="String">LevelDesc</Data></Cell>
    <Cell><Data ss:Type="String">Profile4</Data></Cell>
    <Cell><Data ss:Type="String">Approver4</Data></Cell>
    <Cell><Data ss:Type="String">Name4</Data></Cell>
    <Cell ss:StyleID="s63"><Data ss:Type="DateTime">2020-01-04T19:48:28Z</Data></Cell>
   </Row>
  </Table>
  <AutoFilter x:Range="R1C1:R1C8" xmlns="urn:schemas-microsoft-com:office:excel">
  </AutoFilter>
 </Worksheet>
</Workbook>

This file has the following scheme/style information embedded:

<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
          xmlns:o="urn:schemas-microsoft-com:office:office"
          xmlns:x="urn:schemas-microsoft-com:office:excel"
          xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
 <Styles>
  <Style ss:ID="s62">
   <Interior ss:Color="#8DB4E2" ss:Pattern="Solid"/>
  </Style>
  <Style ss:ID="s63">
   <NumberFormat ss:Format="dd/mm/yyyy\ hh:mm:ss"/>
  </Style>
 </Styles>

When I double-click the file, Excel displays it correctly:

File correctly being displayed in ExcelFile correctly being displayed in Excel

When importing into a PowerQuery (Data -> Import -> From File -> From XML), the XML scheme seems not to be recognized and therefore the file is not properly being imported:

Incorrect import in ExcelIncorrect import in Excel

Does anyone have an idea how I can get Excel to treat this file as it does in the "viewer"? Reason that I want to import the data is that I obviously have a number of user.XML files and they are frequently changing. Manually copy&pasting from the viewer into a table is therefore not an option...

 

I found some similar threads in the forum, but they did not work for me:

  • From here: Loading file into PQ-Editor, expanding the table (multiple times):
    • I end up with this instead of a table with multiple columns
      Single column, serial valuesSingle column, serial values
  • From here: Investigating with advanced editor:
    • No idea what exactly I should do here...

Any help is appreciated!

1 ACCEPTED SOLUTION

That's because your JSON must be different than the one I used in the query I've pasted.

If you follow the steps in the code I've pasted you will see that there is no such intermediate result than the one you've shown.

 

To retrieve Worksheet-table from the table you've pasted, you can use the following code for example:

#"Parsed XML"{[Name = "Worksheet"]}[Table]

 

And to convert it to a function, you replace the XML-string by the function parameter like so:

 

(XMLFilePath as text) =>
Source = File.Contents(XMLFilePath),
....

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

8 REPLIES 8
aik3e
New Member

Hi, I am new to power bi and I've tried the solution and it works great. However, I find that the part of loading the folder contents and applying the custom function "fxImportXML" to each file found a bit cumbersome, specially if you just want to apply it to one file. How can the solution be adopted to loading a single file and applying the custom function to it?

I belive you'd have to change the source to a single file, but this may be a topic for another post 🙂

ImkeF
Super User
Super User

This is one option to parse the XML: 

 

let
    Source = "<?xml version=""1.0""?>#(cr)#(lf)<?mso-application progid=""Excel.Sheet""?>#(cr)#(lf)<Workbook xmlns=""urn:schemas-microsoft-comffice:spreadsheet""#(cr)#(lf)          xmlnsa=""urn:schemas-microsoft-comfficeffice""#(cr)#(lf)          xmlns:x=""urn:schemas-microsoft-comffice:excel""#(cr)#(lf)          xmlns:ss=""urn:schemas-microsoft-comffice:spreadsheet"">#(cr)#(lf) <Styles>#(cr)#(lf)  <Style ss:ID=""s62"">#(cr)#(lf)   <Interior ss:Color=""#8DB4E2"" ssattern=""Solid""/>#(cr)#(lf)  </Style>#(cr)#(lf)  <Style ss:ID=""s63"">#(cr)#(lf)   <NumberFormat ss:Format=""dd/mm/yyyy\ hh:mm:ss""/>#(cr)#(lf)  </Style>#(cr)#(lf) </Styles>#(cr)#(lf) <Worksheet ss:Name=""CAM Export"">#(cr)#(lf)  <Table>#(cr)#(lf)   <Column ss:Width=""50""/>#(cr)#(lf)   <Column ss:Width=""100""/>#(cr)#(lf)   <Column ss:Width=""50""/>#(cr)#(lf)   <Column ss:Width=""100""/>#(cr)#(lf)   <Column ss:Width=""50""/>#(cr)#(lf)   <Column ss:Width=""50""/>#(cr)#(lf)   <Column ss:Width=""100""/>#(cr)#(lf)   <Column ss:Width=""100""/>#(cr)#(lf)   <Row>#(cr)#(lf)    <Cell sstyleID=""s62""><Data ss:Type=""String"">Tool</Data></Cell>#(cr)#(lf)    <Cell sstyleID=""s62""><Data ss:Type=""String"">ToolDescription</Data></Cell>#(cr)#(lf)    <Cell sstyleID=""s62""><Data ss:Type=""String"">Level</Data></Cell>#(cr)#(lf)    <Cell sstyleID=""s62""><Data ss:Type=""String"">LevelDescription</Data></Cell>#(cr)#(lf)    <Cell sstyleID=""s62""><Data ss:Type=""String"">Profile</Data></Cell>#(cr)#(lf)    <Cell sstyleID=""s62""><Data ss:Type=""String"">Person</Data></Cell>#(cr)#(lf)    <Cell sstyleID=""s62""><Data ss:Type=""String"">PersonName</Data></Cell>#(cr)#(lf)    <Cell sstyleID=""s62""><Data ss:Type=""String"">Validity</Data></Cell>#(cr)#(lf)   </Row>#(cr)#(lf)   <Row>#(cr)#(lf)    <Cell><Data ss:Type=""String"">Tool1</Data></Cell>#(cr)#(lf)    <Cell><Data ss:Type=""String"">ToolDesc1</Data></Cell>#(cr)#(lf)    <Cell><Data ss:Type=""String"">Level1</Data></Cell>#(cr)#(lf)    <Cell><Data ss:Type=""String"">LevelDesc</Data></Cell>#(cr)#(lf)    <Cell><Data ss:Type=""String"">Profile1</Data></Cell>#(cr)#(lf)    <Cell><Data ss:Type=""String"">Approver1</Data></Cell>#(cr)#(lf)    <Cell><Data ss:Type=""String"">Name1</Data></Cell>#(cr)#(lf)    <Cell sstyleID=""s63""><Data ss:Type=""DateTime"">2020-01-17T07:58:24Z</Data></Cell>#(cr)#(lf)   </Row>#(cr)#(lf)   <Row>#(cr)#(lf)    <Cell><Data ss:Type=""String"">Tool2</Data></Cell>#(cr)#(lf)    <Cell><Data ss:Type=""String"">ToolDesc2</Data></Cell>#(cr)#(lf)    <Cell><Data ss:Type=""String"">Level1</Data></Cell>#(cr)#(lf)    <Cell><Data ss:Type=""String"">LevelDesc</Data></Cell>#(cr)#(lf)    <Cell><Data ss:Type=""String"">Profile2</Data></Cell>#(cr)#(lf)    <Cell><Data ss:Type=""String"">Approver2</Data></Cell>#(cr)#(lf)    <Cell><Data ss:Type=""String"">Name2</Data></Cell>#(cr)#(lf)    <Cell sstyleID=""s63""><Data ss:Type=""DateTime"">2020-01-07T08:40:41Z</Data></Cell>#(cr)#(lf)   </Row>#(cr)#(lf)   <Row>#(cr)#(lf)    <Cell><Data ss:Type=""String"">Tool3</Data></Cell>#(cr)#(lf)    <Cell><Data ss:Type=""String"">ToolDesc4</Data></Cell>#(cr)#(lf)    <Cell><Data ss:Type=""String"">Level1</Data></Cell>#(cr)#(lf)    <Cell><Data ss:Type=""String"">LevelDesc</Data></Cell>#(cr)#(lf)    <Cell><Data ss:Type=""String"">Profile3</Data></Cell>#(cr)#(lf)    <Cell><Data ss:Type=""String"">Approver3</Data></Cell>#(cr)#(lf)    <Cell><Data ss:Type=""String"">Name3</Data></Cell>#(cr)#(lf)    <Cell sstyleID=""s63""><Data ss:Type=""DateTime"">2020-01-13T13:48:39Z</Data></Cell>#(cr)#(lf)   </Row>#(cr)#(lf)   <Row>#(cr)#(lf)    <Cell><Data ss:Type=""String"">Tool3</Data></Cell>#(cr)#(lf)    <Cell><Data ss:Type=""String"">ToolDesc4</Data></Cell>#(cr)#(lf)    <Cell><Data ss:Type=""String"">Level1</Data></Cell>#(cr)#(lf)    <Cell><Data ss:Type=""String"">LevelDesc</Data></Cell>#(cr)#(lf)    <Cell><Data ss:Type=""String"">Profile4</Data></Cell>#(cr)#(lf)    <Cell><Data ss:Type=""String"">Approver4</Data></Cell>#(cr)#(lf)    <Cell><Data ss:Type=""String"">Name4</Data></Cell>#(cr)#(lf)    <Cell sstyleID=""s63""><Data ss:Type=""DateTime"">2020-01-04T19:48:28Z</Data></Cell>#(cr)#(lf)   </Row>#(cr)#(lf)   <Row>#(cr)#(lf)    <Cell><Data ss:Type=""String"">Tool3</Data></Cell>#(cr)#(lf)    <Cell><Data ss:Type=""String"">ToolDesc4</Data></Cell>#(cr)#(lf)    <Cell><Data ss:Type=""String"">Level1</Data></Cell>#(cr)#(lf)    <Cell><Data ss:Type=""String"">LevelDesc</Data></Cell>#(cr)#(lf)    <Cell><Data ss:Type=""String"">Profile4</Data></Cell>#(cr)#(lf)    <Cell><Data ss:Type=""String"">Approver4</Data></Cell>#(cr)#(lf)    <Cell><Data ss:Type=""String"">Name4</Data></Cell>#(cr)#(lf)    <Cell sstyleID=""s63""><Data ss:Type=""DateTime"">2020-01-04T19:48:28Z</Data></Cell>#(cr)#(lf)   </Row>#(cr)#(lf)   <Row>#(cr)#(lf)    <Cell><Data ss:Type=""String"">Tool3</Data></Cell>#(cr)#(lf)    <Cell><Data ss:Type=""String"">ToolDesc4</Data></Cell>#(cr)#(lf)    <Cell><Data ss:Type=""String"">Level1</Data></Cell>#(cr)#(lf)    <Cell><Data ss:Type=""String"">LevelDesc</Data></Cell>#(cr)#(lf)    <Cell><Data ss:Type=""String"">Profile3</Data></Cell>#(cr)#(lf)    <Cell><Data ss:Type=""String"">Approver3</Data></Cell>#(cr)#(lf)    <Cell><Data ss:Type=""String"">Name3</Data></Cell>#(cr)#(lf)    <Cell sstyleID=""s63""><Data ss:Type=""DateTime"">2020-01-13T13:48:39Z</Data></Cell>#(cr)#(lf)   </Row>#(cr)#(lf)   <Row>#(cr)#(lf)    <Cell><Data ss:Type=""String"">Tool3</Data></Cell>#(cr)#(lf)    <Cell><Data ss:Type=""String"">ToolDesc4</Data></Cell>#(cr)#(lf)    <Cell><Data ss:Type=""String"">Level1</Data></Cell>#(cr)#(lf)    <Cell><Data ss:Type=""String"">LevelDesc</Data></Cell>#(cr)#(lf)    <Cell><Data ss:Type=""String"">Profile4</Data></Cell>#(cr)#(lf)    <Cell><Data ss:Type=""String"">Approver4</Data></Cell>#(cr)#(lf)    <Cell><Data ss:Type=""String"">Name4</Data></Cell>#(cr)#(lf)    <Cell sstyleID=""s63""><Data ss:Type=""DateTime"">2020-01-04T19:48:28Z</Data></Cell>#(cr)#(lf)   </Row>#(cr)#(lf)  </Table>#(cr)#(lf)  <AutoFilter x:Range=""R1C1:R1C8"" xmlns=""urn:schemas-microsoft-comffice:excel"">#(cr)#(lf)  </AutoFilter>#(cr)#(lf) </Worksheet>#(cr)#(lf)</Workbook>",
    #"Parsed XML" = Xml.Tables(Source),
    Worksheet = #"Parsed XML"{0}[Worksheet],
    Table = Worksheet{0}[Table],
    Row = Table{0}[Row],
    #"Added Custom" = Table.AddColumn(Row, "Custom", each List.Transform([Cell][Data], (x) => x[#"Element:Text"]{0})),
    Custom1 = Table.FromRows(#"Added Custom"[Custom]),
    #"Promoted Headers" = Table.PromoteHeaders(Custom1, [PromoteAllScalars=true])
in
    #"Promoted Headers"

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Hi,
Can you guide me how to create a custom function that implements this? I have not gotten so far to do so...

What do you want to use as function parameter(s)?

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Hi,

What I'd like to do is to get a whole folder imported and parsed into a big table.
I replaced the source with the actual "File.Contents('...\user.xml')" and ran the query you gave above, so that I have at least one file parsed.
Hoever, on the "Worksheet" step I encounter the following issue:

1: 1-source.png

2: 2-parsed xml.png

3: 3-worksheet.png

 

Which translates to "the field 'worksheet' was not found in the dataset."
I don't understand this as it is showin in step 2...

That's because your JSON must be different than the one I used in the query I've pasted.

If you follow the steps in the code I've pasted you will see that there is no such intermediate result than the one you've shown.

 

To retrieve Worksheet-table from the table you've pasted, you can use the following code for example:

#"Parsed XML"{[Name = "Worksheet"]}[Table]

 

And to convert it to a function, you replace the XML-string by the function parameter like so:

 

(XMLFilePath as text) =>
Source = File.Contents(XMLFilePath),
....

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

You're awesome, Imke!

What I ended up with is this:

let
    xmlfile = (XMLFilePath as text) => let
        Source = File.Contents(XMLFilePath),
        #"Parsed XML" = Xml.Tables(Source,null,65001),
        Worksheet = #"Parsed XML"{[Name = "Worksheet"]}[Table],
        Table = Worksheet{0}[Table],
        Row = #"Table"{[Name = "Row"]}[Table],
        #"Added Custom" = Table.AddColumn(Row, "Custom", each List.Transform([Cell][Data], (x) => x[#"Element:Text"]{0})),
        Custom1 = Table.FromRows(#"Added Custom"[Custom]),
        #"Promoted Headers" = Table.PromoteHeaders(Custom1, [PromoteAllScalars=true])
    in
        #"Promoted Headers"
in
    xmlfile

Additional steps to make it work:

  1. Query -> New -> Folder
  2. Create custom column - Formula:
    = Table.AddColumn(#"Source", "FullName", each [Folder Path] &[Name])
  3. Run custom function - Formula:
    = Table.AddColumn(#"Custom Column", "fxImportXML", each fxImportXML([FullName]))
  4. Expand custom column
  5. From here: Do whatever I want 🙂

 

Kudos, Imke!

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors