Power Query中展开XML单元格内嵌套表格的问题求助

问题说明 #

• 需使用Power Query导入并展开联合国综合制裁名单的XML文件
• 展开INDIVIDUAL表格后，单元格内出现大量嵌套表格
• 尝试过同类问题的解决方案但未生效，查询设置中记录了空步骤

现有代码 #

源查询 #

let
Source = Xml.Tables(Web.Contents("https://scsanctions.un.org/resources/xml/en/consolidated.xml")),
#"Expanded ENTITIES" = Table.ExpandTableColumn(Source, "ENTITIES", {"ENTITY"}, {"ENTITY"}),
#"Expanded INDIVIDUALS" = Table.ExpandTableColumn(#"Expanded ENTITIES", "INDIVIDUALS", {"INDIVIDUAL"}, {"INDIVIDUAL"})
in
#"Expanded INDIVIDUALS"

引用查询 #

let
    Source = #"UN Source",
    #"Expanded ENTITY" = Table.ExpandTableColumn(Source, "ENTITY", {"DATAID", "VERSIONNUM", "FIRST_NAME", "UN_LIST_TYPE", "REFERENCE_NUMBER", "LISTED_ON", "COMMENTS1", "LIST_TYPE", "LAST_DAY_UPDATED", "ENTITY_ALIAS", "ENTITY_ADDRESS", "SORT_KEY", "SORT_KEY_LAST_MOD", "NAME_ORIGINAL_SCRIPT", "SUBMITTED_ON"}, {"ENTITY.DATAID", "ENTITY.VERSIONNUM", "ENTITY.FIRST_NAME", "ENTITY.UN_LIST_TYPE", "ENTITY.REFERENCE_NUMBER", "ENTITY.LISTED_ON", "ENTITY.COMMENTS1", "ENTITY.LIST_TYPE", "ENTITY.LAST_DAY_UPDATED", "ENTITY.ENTITY_ALIAS", "ENTITY.ENTITY_ADDRESS", "ENTITY.SORT_KEY", "ENTITY.SORT_KEY_LAST_MOD", "ENTITY.NAME_ORIGINAL_SCRIPT", "ENTITY.SUBMITTED_ON"}),
    #"Expanded INDIVIDUAL" = Table.ExpandTableColumn(#"Expanded ENTITY", "INDIVIDUAL", {"DATAID", "VERSIONNUM", "FIRST_NAME", "SECOND_NAME", "THIRD_NAME", "UN_LIST_TYPE", "REFERENCE_NUMBER", "LISTED_ON", "COMMENTS1", "DESIGNATION", "NATIONALITY", "LIST_TYPE", "LAST_DAY_UPDATED", "INDIVIDUAL_ALIAS", "INDIVIDUAL_ADDRESS", "INDIVIDUAL_DATE_OF_BIRTH", "INDIVIDUAL_PLACE_OF_BIRTH", "INDIVIDUAL_DOCUMENT", "SORT_KEY", "SORT_KEY_LAST_MOD", "NAME_ORIGINAL_SCRIPT", "FOURTH_NAME", "GENDER", "TITLE", "SUBMITTED_BY"}, {"INDIVIDUAL.DATAID", "INDIVIDUAL.VERSIONNUM", "INDIVIDUAL.FIRST_NAME", "INDIVIDUAL.SECOND_NAME", "INDIVIDUAL.THIRD_NAME", "INDIVIDUAL.UN_LIST_TYPE", "INDIVIDUAL.REFERENCE_NUMBER", "INDIVIDUAL.LISTED_ON", "INDIVIDUAL.COMMENTS1", "INDIVIDUAL.DESIGNATION", "INDIVIDUAL.NATIONALITY", "INDIVIDUAL.LIST_TYPE", "INDIVIDUAL.LAST_DAY_UPDATED", "INDIVIDUAL.INDIVIDUAL_ALIAS", "INDIVIDUAL.INDIVIDUAL_ADDRESS", "INDIVIDUAL.INDIVIDUAL_DATE_OF_BIRTH", "INDIVIDUAL.INDIVIDUAL_PLACE_OF_BIRTH", "INDIVIDUAL.INDIVIDUAL_DOCUMENT", "INDIVIDUAL.SORT_KEY", "INDIVIDUAL.SORT_KEY_LAST_MOD", "INDIVIDUAL.NAME_ORIGINAL_SCRIPT", "INDIVIDUAL.FOURTH_NAME", "INDIVIDUAL.GENDER", "INDIVIDUAL.TITLE", "INDIVIDUAL.SUBMITTED_BY"}),
    #"Expanded INDIVIDUAL.DESIGNATION" = Table.ExpandTableColumn(#"Expanded INDIVIDUAL", "INDIVIDUAL.DESIGNATION", {"VALUE"}, {"INDIVIDUAL.DESIGNATION.VALUE"}),
    #"Expanded INDIVIDUAL.NATIONALITY" = Table.ExpandTableColumn(#"Expanded INDIVIDUAL.DESIGNATION", "INDIVIDUAL.NATIONALITY", {"VALUE"}, {"INDIVIDUAL.NATIONALITY.VALUE"}),
    #"Expanded INDIVIDUAL.LAST_DAY_UPDATED" = Table.ExpandTableColumn(#"Expanded INDIVIDUAL.NATIONALITY", "INDIVIDUAL.LAST_DAY_UPDATED", {"VALUE"}, {"INDIVIDUAL.LAST_DAY_UPDATED.VALUE"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded INDIVIDUAL.LAST_DAY_UPDATED",{"INDIVIDUAL.DATAID", "INDIVIDUAL.VERSIONNUM", "INDIVIDUAL.FIRST_NAME", "INDIVIDUAL.SECOND_NAME", "INDIVIDUAL.THIRD_NAME", "INDIVIDUAL.UN_LIST_TYPE", "INDIVIDUAL.REFERENCE_NUMBER", "INDIVIDUAL.LISTED_ON", "INDIVIDUAL.COMMENTS1", "INDIVIDUAL.DESIGNATION.VALUE"}),
       
        Transform=Table.TransformColumns(#"Expanded INDIVIDUAL.LAST_DAY_UPDATED", {{"INDIVIDUAL.NATIONALITY.VALUE", each if Value.Is(_, type table) then _ else #table({"Item"}, {{_}} ) }} ),
        ColumnsToExpand = List.Distinct(List.Combine(List.Transform(Table.Column(Transform, "INDIVIDUAL.NATIONALITY.VALUE"), each if _ is table then Table.ColumnNames(_) else {}))),
        Expanded = Table.ExpandTableColumn(Transform, "INDIVIDUAL.NATIONALITY.VALUE", ColumnsToExpand, ColumnsToExpand)

in Expanded

内容的提问来源于stack exchange，提问作者Bee Hoof