Forum Discussion
Nested tables causing refresh issues in PowerBI Service
I have recently started having issues with automatic refresh in PBI Service, all with the same approximate error message:
Expression.Error: The column 'xxxx' of the table wasn't found.. xxxx. . The exception was raised by the IDbCommand interface. Table: xxxx
I am using multiple tables from a PostgreSQL database that are connected with each other, and appear to feature nested tables automatically when I connect to them in PBI, e.g.:
As far as I can tell, the errors seem to relate to me deleting these "nested tables" (as I understand them) from the query. Unfortunately keeping them in is not an option, because I need to pivot certain values and it will not allow me to do that while the table contains nested tables. I have removed these before and didn't have any issues, but in the last week I keep getting these same error messages.
On other occasions it has pointed to tables that are referenced in "nested values" (not sure what the difference here is, and would be grateful for any more information on why these are being included in the first place!):
Same issue, if I simply delete these from the query, it throws up the error in PBI Service.
Also, the dashboards still refresh fine in the Desktop, but they don't refresh in PBI Service. Does anyone have any idea what might be going on here? Many thanks in advance!
When your group by includes "all rows" that will carry these columns around. Instead of group by consider using Table.SelectColumns to only pick the columns you need.
6 Replies
Power Query can handle all kinds of data types (including nested types) but at the end of a query (especially if you want to load that into Power BI) all columns must have one of the allowed types (plus variant, but that is not desirable)
No other column types are permitted. Either exclude the table and value types, or convert/flatten them into permitted column types.
