Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

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" ...
Need Help
Show and Tell
  • dufoq3's avatar
    dufoq3
    1 year ago

    Hi Anonymous, just write another Table.ExpandTableColumn manualy:

     

    Output:

    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"

     

dufoq3's avatar
dufoq3
Community Champion
1 year ago

Hi Anonymous, just write another Table.ExpandTableColumn manualy:

 

Output:

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"

 

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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.

 

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?

 

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.