在Excel中使用Power Query M实现二维XML的转换

Question

我有一个包含元素列表和元数据列表的XML文档：

<?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>

我想将其转换为：

¦   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 ¦

我正在尝试使用Microsoft Power Query M，但我的知识非常有限，我需要使用excel表中的数据。我用来生成XML的工具并没有提供不同的结构。提前谢谢你！

Answer 1

假设这些数据是一致的，我认为这应该可以很好地工作。在将xml文件加载到Power Query之后，只需添加一个自定义列，在其中转置(Table.Transpose)每个“表”(这是您的XML记录)。然后展开自定义列并根据需要进行清理。

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"