Forum Discussion
Expanding tables varies each time
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
Super UserHi 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
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
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