Nested tables causing refresh issues in PowerBI Service

[hildebolt]

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!

[lbendlin]

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.

[hildebolt]

Thanks a lot for the response!

Yep, thats a good point. Do you know how I can exclude them? For some reason I can't just remove the columns, as it still returns the same error. 

[lbendlin]

You probably didn't catch them all.  Use Value.Is to validate across columns.

[hildebolt]

Apologies if something is going over my head, and I appreciate your patience. However, I'm not sure its possible that I could have not caught them all, as one example where the error is appearing leaves two perfectly valid seeming columns:

This is the step immediately after I have connected to the PostgreSQL database and imported the table. It includes the "Value" column and a bunch of "Table" columns that appear to have been automatically generated. I then do a "group by" query that leaves me with just two columns, both seemingly valid:

I nevertheless receive an error message in PBI Service pointing to this table, saying that it cannot find the "Value" column from the first image. 

[lbendlin · Accepted solution]

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.

[hildebolt]

Ah that could well be the main issue.. Thanks a lot.