Expanding nested tables in an xml file

[PeterJohnsonZA]

Can anyone point me in the direction of a connector or some other method of reliably drilling down through an xml file and extracting tables and data from it.?

I'm a newbie at this but I have an xml file that I would like to be able to unpack and analyze etc. I don't know very much about XML schema and I've been going around in circles. The general answer seems to be that there is no easy way to do this because in the file in question sometimes a table is empty and sometimes it's not. 

Any help or guidance would be appreciated. 

[Anonymous]

Hi @PeterJohnsonZA ,

Power Query has a XML connector. Power Query XML connector - Power Query | Microsoft Docs

You could expand the table by clicking the expand button.

If you're going to expand a lot of columns at once, this blog may help you.

Chris Webb's BI Blog: Expanding All Columns In A Table In Power Query Chris Webb's BI Blog (crossjoi...

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

What if after expanding, one of the columns still lists "Table" for the results because it was a sub folder of the expanded data? It doesn't give the option to expand further/again. I have some data i need to change the formatting to text for before loading it and the only way I know how to drill down gives me one row of results rather than doing it for every line of the report for that field.

Using your screenshot provided as further explanation, it's like Division would also have columns that need expanded but they don't show on the list there and don't populate another Expand option for that data after clicking OK. Any advice? 

[Pete_Murphy_71]

I am having this problem too, nested tables in a table.

so I expand everything, but then see some cells have data, e.g. a name or text, but other cells have a nested table in them [Table] .

So some cells in the column have single line data, but other cells seem to have a list of data... I have been struggling with this one for a couple of days now...

[Rgowda]

Hey 

did you got the solution for the mentioned 

problem

[Anonymous]

I was able to get the data I needed ultimately, but only thanks to a coworker that wrote some Python code to get it for me. Good luck!