Importing an XML array to an Excel column

Andrew

Try this.

1 Goto Data>Get & Transform>New Query>From File>From XML.

2 Select the XML file.

3 Ciick Edit.

4 Right click the Items column header, select Split Column>By delimiter, select Space as the delimiter and in Advanced Options select Rows under Split into.

You should now have each the items from the XML file in separate rows.

To add an index in the first column:

1 Goto Add Column

2 Select From 1 from the Index column.

3 Drag the added column over to the left of the Items column.

To return the data to Excel goto File>Close & Load.


Here's the 'M' code for the whole procedure.

let
    Source = Xml.Tables(File.Contents("C:\Downloads\readings.xml")),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"items", type text}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"items", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "items"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"items", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type1", "Index", 1, 1),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"Index", "items"})
in
    #"Reordered Columns"

Select allOpen in new window

Hi Norie, I don't seem to have the option to Get & Transform > New Query in excel.
My version of Excel is 2010.
excel.jpg

Use Data --> From Other Sources --> From XML Data Import

Norie,

Thank you for the elegant solution for importing and formatting the datasheet. It works as intended.

Nikoloz,

I was aware of the XML tools in Excel and was struggling to get them to perform the task.