ExpandTableColumn doesn't expand everything if XML tag has 1 or many items

[Licantrop0]

I'm trying to expand a column (tag Property) derived from an xml table that can have 1 or more children (tags value):

<objects>
  <object name="a">
     <property>
      <value>1</value>
    </property>
  </object>
  <object name="b">
     <property>
      <value>1</value>
      <value>2</value>
      <value>3</value>
     </property>
  </object>
  <object name="c">
     <property>
       <value>3</value>
     </property>
  </object>
  <object name="d">
    <property>
     <value>1</value>
     <value>2</value>
     <value>4</value>
     <value>5</value>
    </property>
  </object>
  <object name="e">
   <property>
     <value>5</value>
   </property>
  </object>
</objects>

When I click expand on the column header, Power Bi doesn't understand that the column may have more than one children, and only expand the ones with 1 child, leaving the others as "Table":

from here, I cannot further expand...

Is this a bug? how can I workaround this?

thanks,

Luca

[Fowmy]

@Licantrop0 

These two links should provide help on the issue of expanding nested tables, please check it out.

https://blog.crossjoin.co.uk/2014/05/21/expanding-all-columns-in-a-table-in-power-query/
https://community.powerbi.com/t5/Desktop/How-to-expand-all-nested-tables-at-once-table-expandtableco...

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[Licantrop0]

Hi @Fowmy, thanks for pointing me into the right direction.

The recursive function is the right approach (even thou in my case there are just 2 levels), but I don't understand how to apply it into my sample data, can you help me?

[Fowmy]

@Licantrop0 

Can you share a sample file the same structure to test?

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[Licantrop0]

Thank you! here it is:

https://1drv.ms/u/s!Asu2W75soOnW14QFjcLUmNosj-s5ug