CODEWITHSUNDEEP
excelxmlpowerquery
Mamphir
Mamphir

Reputation: 325

Transforming 2D XML using Power Query M in Excel

I have an XML doc that contains a list of elements with list of metadata:

<?xml version="1.0"?>
<report>
    <project>
        <meta-data>360333</meta-data>
        <meta-data>SomeName</meta-data>
        <meta-data>SomeDescription</meta-data>
        <meta-data>22.11.2019 11:25</meta-data>
        <meta-data>11.12.2019 10:49</meta-data>
    </project>
        <project>
        <meta-data>360333</meta-data>
        <meta-data>SomeName</meta-data>
        <meta-data>SomeDescription</meta-data>
        <meta-data>22.11.2019 11:25</meta-data>
        <meta-data>11.12.2019 10:49</meta-data>
    </project>
        <project>
        <meta-data>360333</meta-data>
        <meta-data>SomeName</meta-data>
        <meta-data>SomeDescription</meta-data>
        <meta-data>22.11.2019 11:25</meta-data>
        <meta-data>11.12.2019 10:49</meta-data>
    </project>
        <project>
        <meta-data>360333</meta-data>
        <meta-data>SomeName</meta-data>
        <meta-data>SomeDescription</meta-data>
        <meta-data>22.11.2019 11:25</meta-data>
        <meta-data>11.12.2019 10:49</meta-data>
    </project>
</report>

I would like to transform this in to:

¦   ID   ¦    Name  ¦   Description   ¦       Start      ¦       Stop       ¦
-----------------------------------------------------------------------------
¦ 360333 ¦ SomeName ¦ SomeDescription ¦ 22.11.2019 11:25 ¦ 11.12.2019 10:49 ¦
¦ 360333 ¦ SomeName ¦ SomeDescription ¦ 22.11.2019 11:25 ¦ 11.12.2019 10:49 ¦
¦ 360333 ¦ SomeName ¦ SomeDescription ¦ 22.11.2019 11:25 ¦ 11.12.2019 10:49 ¦
¦ 360333 ¦ SomeName ¦ SomeDescription ¦ 22.11.2019 11:25 ¦ 11.12.2019 10:49 ¦

I am trying to use Microsoft Power Query M, but my knowledge with that is very limited and i need to use the data in excel table. The tool that i am using to generate XML does not provide different structure. Thank you in advance!

Upvotes: 0

Views: 672

Answers (1)

Wedge
Wedge

Reputation: 1826

Assuming this data is consistent I think this should work fine. After you load the xml file into Power Query, just add a custom column where you transpose (Table.Transpose) each of the "tables" (which are your XML records). Then you expand the custom column and clean up as needed.

let
    Source = Xml.Tables(File.Contents("YourFile.xml")),
    Table = Source{0}[Table],
    #"Added Custom" = Table.AddColumn(Table, "Custom", each Table.Transpose([#"meta-data"])),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Column1", "Column2", "Column3", "Column4", "Column5"}, {"Column1", "Column2", "Column3", "Column4", "Column5"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"meta-data"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Column1", "ID"}, {"Column2", "Name"}, {"Column3", "Description"}, {"Column4", "Start"}, {"Column5", "Stop"}})
in
    #"Renamed Columns"

Upvotes: 1

Related Questions