Imported XML leads to only one column

[Anonymous]

 I'm not familiar with xml import, but i'm facing an strange problem when triyng to import a file in PowerQuery because leads to the below structure:

After many attempts I can find the data on "row" table, but all the data stored in this xml are in only one column, there's a way to transpose the first 35 rows to headers and create a row for every 35 rows in it?

XML on excel:

[v-yueyunzh-msft]

Hi , @Anonymous 

According to your description, you are connecting to xml file and you find that the data stucture is not incorrect when the power query return.

Due to the fact that many XML documents have ragged or nested data, you may have to do extra data shaping to get it in the sort of form that will make it convenient to do analytics.

As a simple xml file that Power Query can identfy it :

As it can identify it as a table :

For this , you can refer to :
A Primer on JSON and XML Files in Power BI (iterationinsights.com)

Power Query XML connector - Power Query | Microsoft Learn

So, I think you can check whether your xml file format meets the premise of being recognized as a table.

Or you can send me part of your xml (remove sensitive data, but make sure the xml file is legal) file and I can test it locally.

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

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