Reply
Surm
Helper III
Helper III

XML Hierarchical data expanding error in Power Query

Hello!

I am having problems with consuming an XML file that has hierarchical data.

 

This is the structure of the XML file. I want to read the columns shown in green. Note that "LAST_DAY_UPDATED" has multiple values, therefore, the rows will need to be expanded so that each record contains one of the "LAST_DAY_UPDATED".

Note that the dates are two levels below the "Individual" level

 

Surm_3-1725605976196.png

 

 

This is the source XML file

https://scsanctions.un.org/resources/xml/en/consolidated.xml

 

This is the column that needs to be expanded, shown in Power BI Desktop.

Surm_0-1725605238329.png

When expanded, the field data appears as Tables.

Note that there is no option to further expand "Values" column eventhough cells are shown as "Table" 

 

Surm_1-1725605345017.png

 

When clicked on a particular cell, it shows the underlying data, as below

 

Surm_2-1725605369512.png

 

As a result, I am unable to expand the dates.

 

Any advice on how to fix this error and explanation on why Power Query behaves this way?

 

 

1 ACCEPTED SOLUTION
dufoq3
Super User
Super User

Hi @Surm, just write another Table.ExpandTableColumn manualy:

 

Output:

dufoq3_0-1725610318701.png

let
    Source = Xml.Tables(Web.Contents("https://scsanctions.un.org/resources/xml/en/consolidated.xml")),
    INDIVIDUALS = Source{0}[INDIVIDUALS],
    INDIVIDUAL = INDIVIDUALS{0}[INDIVIDUAL],
    #"Expanded LAST_DAY_UPDATED" = Table.ExpandTableColumn(INDIVIDUAL, "LAST_DAY_UPDATED", {"VALUE"}, {"VALUE"}),
    #"Expanded VALUE" = Table.ExpandTableColumn(#"Expanded LAST_DAY_UPDATED", "VALUE", {"Element:Text"})
in
    #"Expanded VALUE"

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

View solution in original post

2 REPLIES 2
dufoq3
Super User
Super User

Hi @Surm, just write another Table.ExpandTableColumn manualy:

 

Output:

dufoq3_0-1725610318701.png

let
    Source = Xml.Tables(Web.Contents("https://scsanctions.un.org/resources/xml/en/consolidated.xml")),
    INDIVIDUALS = Source{0}[INDIVIDUALS],
    INDIVIDUAL = INDIVIDUALS{0}[INDIVIDUAL],
    #"Expanded LAST_DAY_UPDATED" = Table.ExpandTableColumn(INDIVIDUAL, "LAST_DAY_UPDATED", {"VALUE"}, {"VALUE"}),
    #"Expanded VALUE" = Table.ExpandTableColumn(#"Expanded LAST_DAY_UPDATED", "VALUE", {"Element:Text"})
in
    #"Expanded VALUE"

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

@dufoq3 ,

Do you have a solution to expanding the "DESIGNATION" column?

This is the problem: 

"DESIGNATION" xml element sometimes has 1 child, sometimes 2 or more children.
After expanding the column, it shows the expanded value when there is 1 child, otherwise shows "[Table]". There is no icon on the column header to Expand again.

Surm_0-1726812948934.png

 

I tried your previous trick and manually added another Table.ExpandTableColumn, however, it produces the error below. Looks like when the column is a simple text, it fails this time because it is not a table?

Surm_1-1726813104425.png

 

Are you able to explain why this error occurs in the first place? Is this a bug in Power Query or is this how it is supposed to work?

To fix this, I suppose an "IF" condition is required, but I am not sure how to write it.

avatar user

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)