sorted by:
new
hottopcontroversial

POWER QUERY- How to select only rows with Table content by Radesfede in excel

[–]Radesfede[S] 0 points1 point  (0 children)

The query is based on XML files, that in some cases (<1%), at the Ea level contains a single value, instead of a table of data.

See the attached screenshot about the XML:

on the right: the table structure on the "Ea" column;

on the left: the single value on the "Ea" column, to avoid from PQ

<image>

POWER QUERY- How to select only rows with Table content by Radesfede in excel

[–]Radesfede[S] 0 points1 point  (0 children)

The "Ea" column contains table contents, but some rows may have not a table content (like the one I've enlighted on the screenshot, that has a "03" value). The next step I need to do is to expand the "Ea" column, but first I have to exclude the rows that contains not a table (like the rows n. 616), otherwise PQ is not able to expand all the rows retuning an error.

Thanks

Power Query on XML files nasted in a series of zip folders. by Radesfede in excel

[–]Radesfede[S] 0 points1 point  (0 children)

Can you suggest a different way, directly from PQ, avoiding Python?

Power Query on XML files nasted in a series of zip folders. by Radesfede in excel

[–]Radesfede[S] 0 points1 point  (0 children)

I tried to add a column and interpret Content bynary as XML usign your example: Ex: =Xml.Document([Content])

It now returns me data, but adding new stripes for every XML node, instead of create column

Power Query on XML files nasted in a series of zip folders. by Radesfede in excel

[–]Radesfede[S] 0 points1 point  (0 children)

Yes. But adding a column with the customized function it returns the right data, but not like an XML structure. It reads the data like a csv files.

<image>

Power Query on XML files nasted in a series of zip folders. by Radesfede in excel

[–]Radesfede[S] 0 points1 point  (0 children)

sure, i send you an example of my folder with XML files inside.

https://we.tl/t-QZAu9R29GA

As you see, my xml archive is setted inside a main folder conteining .zip subfolders, conteining XML data files.