Expanding tables varies each time

[Phantom25472]

Hi,

In PBI we import a xml-file, trough a Power Query we setup some colums filled with data.

Some columns need to be expanded, because they are tables.

The next times we import the same xml-file, just with updated data, but no difference in columns of anything.

But this time Power Query decides to show some columns not as "table", they are directly expanded.

This gives the error that some data cannot be converted to "table".

To solve this we delete the step where we need to expand the column. And things work again.

What we struggle with is: based on what does Power Query decide wether to show a column as a table or not?

Because we wanted to automate things trough Power Automate, but if every time we provide new xml-files, power query decides to change the "tables", the automated proces will not work.

[BA_Pete · Accepted solution]

Hi @Phantom25472 ,

Power Query doesn't "decide" anything. It does what you tell it to do upon the data that you provide.

So, there's two places to look for a resolution:

-1- What you've told it to do:

You've told it to expand table columns. This appears to be a legitimate request and I assume you used a button in the GUI to tell it to do this, therefore I will assume that the code is also correct. So probably not the issue.

-2- The data you've provided:

It sounds as if the data that is being provided to Power Query is not consistent in its nesting of tables.

Check the data as soon as the nested table column comes into PQ, before any steps are performed on it, to see whether any values in your nested table column aren't actually nested tables at all, but maybe 'null', a scalar value, or maybe a list or record. Based on the limited info I have available, this would be my guess as to where the issue is.

Pete

[Phantom25472]

Here is the file from yesterday:

This is the file from 2 weeks ago:

So you see it is different.

Could it be because in the file from yesterday both columns contain all data, and the file from 2 weeks ago might contain some "null" data along with filled data?

[BA_Pete]

It looks to me as if the data in your XML is being generated in a different format. Power Query isn't performing any transformations at all at this preview stage, so I'd suggest going back into the XMLs or their sources and checking why different output formats are being generated.

Pete

[Phantom25472]

Here is the difference in the source-files:

As you see, it is the same format if the data is filled.

Only difference is when some "column" doesn't contain any data, it will say <voertuigstalling xsi:nil="true"/> so it wil give a "null" when the column will be expanded.

Could this be te cause?

If so, I'll have to prevent the export to write <voertuigstalling xsi:nil="true"/> and make it write <voertuigstalling>empty</voertuigstalling>(or other entry) So it appears as it contains data?

[BA_Pete]

Unfortunately, I don't speak XML, but I understand what you're saying.

It does seem as though your proposed solution would work as long as it generated an empty table every time rather than a null cell.

Pete