Data Privacy Broken: We cannot convert the value "[Record]" to type Record

[jdunmall]

Hi all,

Some general comments after pursuing a case with Microsoft that took about 8 weeks to resolve. Hopefully this is helpful to someone else. Here is what I've learned:

1. Data Privacy in the desktop client can be turned off: "Ignore the Privacy Levels and potentially improve performance" but there is no equivalent setting in the service. It is always turned on. If you want to mimick what the service does, be careful ignoring privacy levels... and then forgetting you've done so later if you run into an error like I did.

2. There are particular combinations of commands that can case your refresh to fail with this error:

We cannot convert the value "[Record]" to type Record.

This is actually a symptom of how the data privacy functionality is implemented. You'll see this error on a refresh request online and you may not see it locally if data privacy is turned off in the desktop client. If you turn it back on, the error will appear in the desktop client as well. 

This error is a result of how the data privacy implementation currently works. In my case, I am connecting to a CRM instance via ODATA and also to an XLS on a SharePoint site. CRM is full of unexpanded column data. I want to expand the columns, and then merge with data from the XLS. Since these are from two different data sources, and with data privacy on, Power BI will buffer the data from each source independently. That buffering code cannot handle structured cell values like records or lists. The buffering is done just before the data is combined with a statement like Table.NestedJoin. If you attempt to expand the record in a column after that first NestedJoin, it will fail because the record is replace with the text "[Record]" due to the incomplete implementation, which clearly cannot be expanded.

To fix this error, make sure all ExpandRecordColumn statements appear before your first data combination statement. Hopefully this saves some folks time.

Also, Microsoft is working on this and I've shared the obvious feedback about the error text itself and the incomplete implementation.

Finally, the documentation on data privacy is poor. Apart from the points above, which are not documented to my knowledge, the specifics for how data privacy actually works is a bit of a mystery. Hopefully this page gets more documentation attention in the future.

-Jeff

[Itisaduck2]

Hi, well 2024 and this Error can still occure, and it give very little information as to what the error is.

In my case this error appeared after i appended 2 queries that were from seperate sources.

My initial table was expanded JSON packages from a Cosmos database and the appended table came from an SQL database.

The 2 sources had different privacy settings, and when I changed them to the same, it could load without issue.

So thanks a lot to the OC on this thread, big help!!

[gonzalopiskorz]

Hi jdunmall, I'm having the same issue and I can't find a solution yet.

What do you mean with this: "To fix this error, make sure all ExpandRecordColumn statements appear before your first data combination statement. Hopefully this saves some folks time."

Which are the steps should I follow. Can you explain more about your solution?

Thanks in advance.

[BenWatt]

This saved me, I can tell you that!

[maghan]

When you combine data from two sources, there’s a risk of leaking data from one source to the other. To let the user, control this, implemented is a “data privacy” feature which lets you control this data flow by classifying sources as “private”, “public” or “organizational”. Data can flow public -> any and organizational -> organizational, but in no other directions. At runtime, we partition a user query based on these classifications, and we insert explicit steps on the partition boundaries.

The Power BI service decides to simplify things by not asking to assign a privacy classification to each source. Instead, it just marks them all as “private”. This means that a query which combines (say) “CRM” and “SharePoint” data gets partitioned in the way described, and the data from “CRM” gets buffered before merging it with the “SharePoint” data.

However, in most cases there are a couple of things you can do from the service side via the gateways. With the Personal Gateway you can choose the “fast combine” option, and the On-Premises Data Gateway allows you to define the privacy level for a data source. This is used for scheduled refresh.

[armyguy255a]

Mark,

Thanks for the help! This absolutely solved my issue. I was having a similar issue except it was happening to Tables instead of Records. I shuffled my Table.ExpandTableColumn and Table.ExpandRecordColumn statements directly under the source and it solved my issue. See below.

let
    Source = OData.Feed(url...),
    #"Expanded Links" = Table.ExpandTableColumn(Source, "Links", {"SourceWorkItemId", "TargetWorkItemId", "LinkTypeName", "TargetWorkItem"}, {"Links.SourceWorkItemId", "Links.TargetWorkItemId", "Links.LinkTypeName", "Links.TargetWorkItem"}),
    #"Expanded Links.TargetWorkItem" = Table.ExpandRecordColumn(#"Expanded Links", "Links.TargetWorkItem", {"WorkItemId", "Title", "WorkItemType", "State"}, {"Links.TargetWorkItem.WorkItemId", "Links.TargetWorkItem.Title", "Links.TargetWorkItem.WorkItemType", "Links.TargetWorkItem.State"}),
    ...
    ...
    ...
in
    #"Removed Columns"

Phil Dieppa

Microsoft TWI Fellow

United States Army, CW3

[chaz2jerry]

Thanks everyone, I have similar error and this fixed it for me.

[BellTech]

My error is We cannot convert the value "[Record]" to type Record.. The exception was raised by the IDataReader interface.

This is so confusing.  My workbook utilizes D365 CRM data utilizing an O'Data feed.  There are several static Excel tables that are set to NOT refresh.  I have tried to recreate my static tables as native PBI tables, but when I try to replace the Excel tables in my merge commands with the PBI tables, I cannot get the data to resolve.  It seems like this change is causing the data to refresh and then it is getting caught in whatever error is causing this whole thing.

Is this saying that even though my model utilizes Dynamics 365 data and several static Excel tables that are set to NOT REFRESH that I need to use a gateway?  If I need to use a gateway, where do I install it if the data lives on D365 azure server?

My client is incredibly frustrated!  They were without refreshes for 4-5 days a couple of weeks ago due to some kind of system error.  Prior to that error, this workbook refreshed without error.   I cannot even manually refresh their data!  I need to get them up and running as quickly as possible or they are going to move away from PBI!

crmadmin@belltechlogix.onmicrosoft.com

Sharon Woloshin

317-885-0716 x16

swoloshin@sbsgroupusa.com