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