Troubleshooting nested values returned as text in Power BI - Table and Record errors explained

The refresh worked on Friday. The report opened in Power BI Desktop on Monday without a complaint. Then the scheduled refresh in the Power BI service failed with a message that talks about converting a value of "[Table]" to a Table type, and the person who built the report has no idea what that means. The numbers on the dashboard are a day old, and the email from the finance team is already in the inbox.

We get this call often from Australian organisations whose Power BI models pull from more than one source. The error is confusing because the report looks fine on the developer's machine. The difference is not the data. It is the way Power Query handles columns that contain tables, records or lists, and the way the service treats those columns when it refreshes.

Microsoft's troubleshooting article on nested values covers the cause and the fix, and it is a useful reference. This post explains what is going on underneath, where the behaviour has changed recently, and the fixes we use in practice.

What "nested" means here

A nested value is a value inside a cell that is not a simple number, date or piece of text. It is a table, a record, a list or a function. These show up in Power Query when you expand a column that came back from an API, when you call a function that returns a table, or when a merge produces a column holding a whole table for each row.

You can see them in the Power Query editor as cells that display Table, Record or List rather than a value. In the editor they often look harmless. You can keep working, add steps, and the preview loads. The problem only appears when the data reaches the model.

Why the service sees a problem Desktop does not

The main cause is the data privacy firewall. This is a Power Query feature that stops data from one source leaking into another source during evaluation. To enforce that, it sometimes buffers a data source, which means it loads the whole thing into memory before passing it on.

When the firewall buffers a data source, nested non-scalar values have to be converted to something it can handle. Historically, Power Query converted them to text. A nested table became the literal string "[Table]", a record became "[Record]", and so on. The report would refresh without errors, but the column would be full of meaningless text, and any measure that relied on those values would produce odd results.

Modern versions of Power BI handle this differently. When the firewall buffers a nested table, record or list, it no longer silently converts it to text. It raises an error instead. You will see something like:

We cannot return a value of type Table in this context

That is a better behaviour, because a silent conversion is far harder to notice. It does mean that a report which used to refresh quietly may now fail, and that is what the Monday morning email is about.

The change that catches people out at load time

The same logic extends to loading and refreshing. Nested tables, records and lists loaded into the Power BI model, or into the Excel data model from Power Query in Excel, used to come through as text. Now they are treated as errors.

In practice, that means a refresh can complete with errors. The loaded table contains null where the nested value used to be, and the error count in the load results goes up. The bit that makes this hard to track is that these errors only show up during load or refresh. They do not appear in the Power Query editor, so the preview you checked looks fine.

If you have a report that loaded cleanly for months and suddenly shows nulls, this is a likely cause. Check the load results, not just the preview.

The privacy setting that fixes most cases

Microsoft's guidance is to make sure the data source privacy settings are configured correctly in the Power BI service. The service now supports setting privacy levels for cloud data sources, or turning the firewall off entirely. Setting the relevant source to non-Private avoids the buffering that causes the problem in most cases.

I want to be direct about the trade-off here, because it is not free. Privacy levels exist to stop data from one source being sent to another. Setting a source to non-Private tells Power Query it can combine that source with others without the firewall getting involved. For a source that holds ordinary business data, that is usually fine. For a source that holds personal information, customer records or anything under a regulatory obligation, you need to think about it first. Under the Australian Privacy Principles, the question is not only whether the report works but whether the data is handled in a way you can defend.

Our practice is to check the privacy level on each source as part of the build, not as a last-minute fix. Write down what each source contains and why its level is set the way it is. When someone asks six months later why a source is non-Private, the answer should be in the documentation, not in someone's memory.

To test whether the privacy setting is the cause, Microsoft suggests setting all your data source privacy settings to Private in Power BI Desktop and seeing whether the error reproduces. If it does, the problem is the firewall, and the fix is the service setting. If it does not, look elsewhere.

The fix that usually works best

If you can avoid nested values altogether, do. That is the cleanest answer and the one we recommend first.

Look at the column that is causing the problem. If it is a table, record or list that you do not actually need in the model, remove the column. Most of the time this is an expansion step that was added during development and never trimmed. Remove it and the error goes away.

If you do need the data, you cannot just remove the column. In that case, add a custom column that converts the nested value to something the model can handle. Microsoft's sample logic checks the type of the value and returns a text label for each case, falling back to the original value when it is a scalar. It looks like this:

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]

This restores the old behaviour in a controlled way. It is a workaround, though, not a design. The text labels are not useful to anyone, and you are carrying the nested value through the query only to throw it away. If you find yourself writing this in several places, it is a sign the query needs to be restructured. Expand the nested data properly, or split it into its own query.

Our opinion on nested data in Power Query

Nested values usually come from one of two places. Either an API returns JSON with objects inside objects, or a merge brings in a whole table that nobody expanded. In both cases, the best long-term fix is to expand the data explicitly at the point it enters the query. Pick the fields you need, rename them, and set their types. It is more typing up front, and it makes the query readable to the next person who opens it.

The other place nested values come from is the habit of clicking the expand button and then unticking most of the columns. It works, but it leaves a trail of steps that behave differently depending on the data. A step that expands a column might return a table on one refresh and a record on the next if the source changes shape. Be specific about what you expand, and the query becomes much easier to trust.

What to check when the refresh still fails

If you have removed the nested columns, set the privacy levels, and the refresh still fails, work through a short checklist.

Confirm the error is in the refresh and not in the editor. The editor preview can hide the problem. Look at the refresh history in the service and the load results in Desktop.

Check whether the source has changed. An API that added a field, or a database view that started returning a nested structure, will break a query that worked before. Compare the column list against the last successful refresh.

Look for merges and expansions that run after a filter. A query that filters rows first and then expands a column can behave differently from one that expands first. Reordering steps sometimes fixes the problem.

If none of that helps, reduce the query to the minimum needed to reproduce the error. Start by removing steps from the end until the refresh works. The last step you removed is usually the cause.

Where this fits

Nested values are one of those problems that rarely show up in a demo and almost always show up in production. They tend to appear when a report moves from a developer's laptop to a scheduled refresh, which is exactly the point where the data has to behave consistently without someone watching it.

If your Power BI reports are refreshing from several sources and you are seeing errors you cannot explain, it is usually worth a proper review of the queries and the privacy settings rather than a series of quick patches. Our Power BI consultants do this kind of review regularly, and our Microsoft Data Factory consultants can help where the cleaning belongs upstream in the pipeline rather than in the report. Start by listing every source and its privacy level, then decide which nested values you genuinely need. That single exercise solves more of these problems than any one fix.

Source: Microsoft Learn, "Troubleshoot nested values returned as text" (https://learn.microsoft.com/en-us/power-bi/connect-data/service-troubleshooting-nested-values)