Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
This article describes an issue users may encounter where nested values are converted to a string due to improper data source privacy settings and explains how to address it.
In the past, there have been cases where a report refreshes in Power BI Desktop but fails on the Power BI service with an error like this:
We cannot convert the value "[Table]" to type Table
Cause
One of the causes of this error involves nested non-scalar values, such as tables, records, lists, and functions. When the Data Privacy Firewall buffers a data source, nested non-scalar values are converted to text values, such as "[Table]" or "[Record]".
The Power BI service now supports setting privacy levels or turning off the Firewall entirely. The errors can be avoided by configuring the data source privacy settings in the Power BI service to be non-Private.
For more recent versions of Power BI, when the Firewall buffers a nested table, record, or list, it doesn't silently convert non-scalar values to text. Instead, it shows an error:
We cannot return a value of type Table in this context
Effect on load/refresh
This change motivated by Firewall buffering also extends to Load/Refresh. The behavior of loading nested tables, records, and lists to the Power BI Model and the Excel Data Model in Power Query for Excel has changed. Before, nested items were loaded as text values, such as "[Table]" or "[Record]". Now, they're treated as errors. A null value is in the loaded table and error count increments in the load results.
Since these errors only occur during Load/Refresh, they don't appear in the Power Query Editor.
Before
- Load/Refresh with no errors.
- Loaded table contains
"[Table]","[Record]", and so forth.
After
- Load/Refresh with errors.
- Loaded table contains
null, instead of"[Table]","[Record]", and so forth.
Resolution
Are you loading a column that contains non-scalar values, for example, tables, lists, or records? If so, you should be able to eliminate the errors by removing the column.
If you can't remove the column, try to replicate the old behavior by adding a custom column and using logic like the following sample:
if [MyColumn] is table then "[Table]" else if [MyColumn] is record then "[Record]"
else if [MyColumn] is list then "[List]" else if [MyColumn] is function
then "[Function]" else [MyColumn]
Does the issue reproduce in Power BI Desktop if you set all your data source privacy settings to Private? If so, try to resolve the error by configuring the data source privacy settings in the Power BI service to be non-Private.