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.
//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:
<?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:
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:
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:
Any help is appreciated!
Solved! Go to 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
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 🙂
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:
2:
3:
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:
= Table.AddColumn(#"Source", "FullName", each [Folder Path] &[Name])
= Table.AddColumn(#"Custom Column", "fxImportXML", each fxImportXML([FullName]))
Kudos, Imke!
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.