Search code examples
powerquerynested-tablexml-import

Properly expand xml table nested within cell (Power Query)

I would appreciate your help on importing and expanding an XML file, with Power Query.

The XML link is here:

UN Consolidated Sanctions List

After expanding the table named ”Individual” I get a lot of tables nested within cells.

Nested Table

I tried to apply the solution offered here:

table-within-a-cell-unable-to-expand

... but nothing happens, although three steps are recorded in query settings:

Empty steps

The code in the Advanced Editor is this:

Source Query:

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"

And in the referenced query:

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

Thank you.

Solution

  • Its hard to figure out even what the output should look like with all the different nested tables.

    Does this work? If not provide sample desired output

    let Source = Xml.Tables(Web.Contents("https://scsanctions.un.org/resources/xml/en/consolidated.xml")),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Attribute:dateGenerated", type datetimezone}}),
INDIVIDUALS = #"Changed Type"{0}[INDIVIDUALS],
INDIVIDUAL = INDIVIDUALS{0}[INDIVIDUAL],
#"Changed Type1" = Table.TransformColumnTypes(INDIVIDUAL,{{"DATAID", Int64.Type}, {"VERSIONNUM", Int64.Type}, {"FIRST_NAME", type text}, {"SECOND_NAME", type text}, {"THIRD_NAME", type text}, {"UN_LIST_TYPE", type text}, {"REFERENCE_NUMBER", type text}, {"LISTED_ON", type text}, {"COMMENTS1", type text}, {"SORT_KEY", type text}, {"SORT_KEY_LAST_MOD", type text}, {"FOURTH_NAME", type text}, {"GENDER", type text}, {"SUBMITTED_BY", type text}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"DATAID", "VERSIONNUM", "FIRST_NAME", "SECOND_NAME", "THIRD_NAME", "UN_LIST_TYPE", "REFERENCE_NUMBER", "LISTED_ON", "COMMENTS1", "SORT_KEY", "SORT_KEY_LAST_MOD", "FOURTH_NAME", "GENDER", "SUBMITTED_BY"}, "Attribute", "Value"),

Transform=Table.TransformColumns(#"Unpivoted Other Columns", {{"Value", each if Value.Is(_, type table) then _ else #table({"Item"}, {{_}} ) }} ),
ColumnsToExpand = List.Distinct(List.Combine(List.Transform(Table.Column(Transform, "Value"), each if _ is table then Table.ColumnNames(_) else {}))),
Expanded = Table.ExpandTableColumn(Transform, "Value", ColumnsToExpand, ColumnsToExpand)
in Expanded

    For the other one

    let Source = Xml.Tables(Web.Contents("https://scsanctions.un.org/resources/xml/en/consolidated.xml")),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Attribute:dateGenerated", type datetimezone}}),
ENTITIES = #"Changed Type"{0}[ENTITIES],
ENTITY = ENTITIES{0}[ENTITY],
#"Changed Type1" = Table.TransformColumnTypes(ENTITY,{{"DATAID", Int64.Type}, {"VERSIONNUM", Int64.Type}, {"FIRST_NAME", type text}, {"UN_LIST_TYPE", type text}, {"REFERENCE_NUMBER", type text}, {"LISTED_ON", type text}, {"COMMENTS1", type text}, {"SORT_KEY", type text}, {"SORT_KEY_LAST_MOD", type text}, {"NAME_ORIGINAL_SCRIPT", type text}, {"SUBMITTED_ON", type text}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"DATAID", "VERSIONNUM", "FIRST_NAME", "UN_LIST_TYPE", "REFERENCE_NUMBER", "LISTED_ON", "COMMENTS1", "SORT_KEY", "SORT_KEY_LAST_MOD", "NAME_ORIGINAL_SCRIPT", "SUBMITTED_ON"}, "Attribute", "Value"),
Transform=Table.TransformColumns( #"Unpivoted Columns", {{"Value", each if Value.Is(_, type table) then _ else #table({"Item"}, {{_}} ) }} ),
ColumnsToExpand = List.Distinct(List.Combine(List.Transform(Table.Column(Transform, "Value"), each if _ is table then Table.ColumnNames(_) else {}))),
Expanded = Table.ExpandTableColumn(Transform, "Value", ColumnsToExpand, ColumnsToExpand)
in Expanded