Getting an error when refreshing the data in Power BI services

[Amal]

Getting this error when refreshing the datasets in Power BI online. We cannot convert the value "[Record]" to type Record

I'm getting the data from Dynamics CRM online, and not getting any errors when refreshing the data in desktop version. 

[Anonymous]

Hi! 

I am also struggeling with refreshing a report with datasource Dynamics 365 CRM. My issue started with the august version of the desktop client and I am also able to refresh the report in my desktop client.

Are you using OData to connect?

Regards,

Nora

[v-yuezhe-msft]

@Amal,

I am unable to reproduce this issue. What steps do you take for the activitypointers table in Query Editor of Power BI Desktop?

Regards,
Lydia

[Amal]

Hi Lydia,

This is the query for the activitypointers table

let
Source = OData.Feed("https://ia.api.crm6.dynamics.com/api/data/v8.2/", null, [Implementation="2.0"]),
activitypointers_table = Source{[Name="activitypointers",Signature="table"]}[Data],
#"Filtered Rows" = Table.SelectRows(activitypointers_table, each ([statecode] = 1 or [statecode] = 3) and ([activitytypecode] = "appointment" or [activitytypecode] = "phonecall")),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"modifiedon", "lastonholdtime", "isbilled", "prioritycode", "_sendermailboxid_value", "activityadditionalparams", "_transactioncurrencyid_value", "exchangeweblink", "versionnumber", "_owningbusinessunit_value", "_modifiedonbehalfby_value", "regardingobjectid_entitlement", "_slainvokedid_value", "postponeactivityprocessinguntil"}),
#"Removed Columns1" = Table.RemoveColumns(#"Removed Columns",{"deliveryprioritycode", "ismapiprivate", "traversedpath", "_serviceid_value", "instancetypecode", "_createdonbehalfby_value", "seriesid", "leftvoicemail", "isworkflowcreated", "senton", "processid", "community", "exchangeitemid", "utcconversiontimezonecode", "deliverylastattemptedon", "timezoneruleversionnumber", "stageid", "onholdtime", "sortdate", "isregularactivity", "exchangerate", "_slaid_value", "regardingobjectid_new_interactionforemail", "regardingobjectid_entitlementtemplate", "regardingobjectid_bookableresourcebooking", "regardingobjectid_bookableresourcebookingheader", "regardingobjectid_knowledgebaserecord", "regardingobjectid_contract", "CreatedActivity_BulkOperationLogs", "activity_pointer_socialactivity", "activity_pointer_recurringappointmentmaster", "activity_pointer_email", "sendermailboxid", "activity_pointer_quote_close", "ActivityPointer_QueueItem", "regardingobjectid_salesorder", "ownerid", "activity_pointer_BulkOperation", "ActivityPointer_CampaignActivityItems", "sla_activitypointer_sla", "regardingobjectid_invoice", "owningbusinessunit", "regardingobjectid_knowledgearticle", "regardingobjectid_quote", "activity_pointer_campaignresponse", "modifiedonbehalfby", "activity_pointer_service_appointment", "activity_pointer_activity_mime_attachment", "activity_pointer_BulkOperation_logs", "regardingobjectid_bulkoperation", "serviceid", "activity_pointer_task", "activity_pointer_opportunity_close", "regardingobjectid_incident", "activity_campaignresponse", "activity_pointer_campaignactivity", "activity_pointer_order_close", "regardingobjectid_campaign", "activity_pointer_incident_resolution", "activity_pointer_letter", "activitypointer_connections2", "slakpiinstance_activitypointer", "owningteam", "regardingobjectid_campaignactivity", "ActivityPointer_BulkDeleteFailures", "slainvokedid_activitypointer_sla", "activitypointer_connections1", "ActivityPointer_AsyncOperations", "activitypointer_activity_parties", "regardingobjectid_ia_accountprofile", "regardingobjectid_la_recurringprocess", "activity_pointer_fax", "createdonbehalfby"}),
#"Expanded regardingobjectid_account" = Table.ExpandRecordColumn(#"Removed Columns1", "regardingobjectid_account", {"accountid", "name"}, {"regardingobjectid_account.accountid", "regardingobjectid_account.name"}),
#"Expanded regardingobjectid_contact" = Table.ExpandRecordColumn(#"Expanded regardingobjectid_account", "regardingobjectid_contact", {"fullname", "contactid"}, {"regardingobjectid_contact.fullname", "regardingobjectid_contact.contactid"}),
#"Expanded regardingobjectid_ia_callcycle" = Table.ExpandRecordColumn(#"Expanded regardingobjectid_contact", "regardingobjectid_ia_callcycle", {"ia_calltype", "ia_callcycleid", "ia_name", "ia_Organisation", "ia_Contact"}, {"regardingobjectid_ia_callcycle.ia_calltype", "regardingobjectid_ia_callcycle.ia_callcycleid", "regardingobjectid_ia_callcycle.ia_name", "regardingobjectid_ia_callcycle.ia_Organisation", "regardingobjectid_ia_callcycle.ia_Contact"}),
#"Expanded owninguser" = Table.ExpandRecordColumn(#"Expanded regardingobjectid_ia_callcycle", "owninguser", {"fullname","ia_sourcedepartment"}, {"owninguser.fullname","owninguser.ia_sourcedepartment"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded owninguser",{{"regardingobjectid_ia_callcycle.ia_calltype", "Call Cycle Type"}, {"regardingobjectid_ia_callcycle.ia_name", "Call Cycle Name"}, {"regardingobjectid_contact.fullname", "Contact Name"}, {"regardingobjectid_account.name", "Account Name"}, {"owninguser.fullname", "TM Name"}, {"regardingobjectid_ia_callcycle.ia_callcycleid", "CallCycle_ID"}, {"regardingobjectid_account.accountid", "Account ID"}, {"regardingobjectid_contact.contactid", "Contact ID"}}),
#"Filtered Rows1" = Table.SelectRows(#"Renamed Columns", each ([owninguser.ia_sourcedepartment] = 2)),
#"Merged Queries" = Table.NestedJoin(#"Filtered Rows1",{"Call Cycle Type"},CallCycleTypes,{"Value.Value"},"CallCycleTypes",JoinKind.LeftOuter),
#"Expanded CallCycleTypes" = Table.ExpandTableColumn(#"Merged Queries", "CallCycleTypes", {"Value.Label.UserLocalizedLabel.Label"}, {"CallCycleTypes.Value.Label.UserLocalizedLabel.Label"}),
#"Renamed Columns1" = Table.RenameColumns(#"Expanded CallCycleTypes",{{"CallCycleTypes.Value.Label.UserLocalizedLabel.Label", "Call Cycle Type Name"}}),
#"Expanded regardingobjectid_ia_callcycle.ia_Organisation" = Table.ExpandRecordColumn(#"Renamed Columns1", "regardingobjectid_ia_callcycle.ia_Organisation", {"accountid"}, {"regardingobjectid_ia_callcycle.accountid"}),
#"Expanded regardingobjectid_ia_callcycle.ia_Contact" = Table.ExpandRecordColumn(#"Expanded regardingobjectid_ia_callcycle.ia_Organisation", "regardingobjectid_ia_callcycle.ia_Contact", {"contactid"}, {"regardingobjectid_ia_callcycle.ia_Contact.contactid"}),
#"Merged Queries7" = Table.NestedJoin(#"Expanded regardingobjectid_ia_callcycle.ia_Contact",{"CallCycle_ID"},Opportunity,{"Call Cycle (ia_callcycle)"},"Opportunity",JoinKind.LeftOuter),
#"Expanded Opportunity" = Table.ExpandTableColumn(#"Merged Queries7", "Opportunity", {"Est. Revenue", "Sales Stage", "opportunityid"}, {"Opportunity.Est. Revenue", "Opportunity.Sales Stage", "Opportunity.opportunityid"}),
#"Added Custom" = Table.AddColumn(#"Expanded Opportunity", "StartDate", each DateTimeZone.ToLocal([scheduledstart])),
#"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"StartDate", type datetimezone}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type", "Created Date", each DateTimeZone.ToLocal([createdon])),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"Created Date", type date}}),
#"Added Custom4" = Table.AddColumn(#"Changed Type1", "Comp_AccountID", each if [Account ID]<>null then [Account ID] else [regardingobjectid_ia_callcycle.accountid]),
#"Added Custom2" = Table.AddColumn(#"Added Custom4", "Comp_ContactID", each if [Contact ID]<>null then [Contact ID] else[regardingobjectid_ia_callcycle.ia_Contact.contactid]),
#"Merged Queries1" = Table.NestedJoin(#"Added Custom2",{"activityid"},appointments,{"activityid"},"appointments",JoinKind.LeftOuter),
#"Expanded appointments" = Table.ExpandTableColumn(#"Merged Queries1", "appointments", {"Appointment_Type_Name", "ia_smartnotes", "Smart Objective"}, {"appointments.Appointment_Type_Name", "appointments.ia_smartnotes", "appointments.Smart Objective"}),
#"Duplicated Column" = Table.DuplicateColumn(#"Expanded appointments", "activityid", "activityid - Copy"),
#"Renamed Columns2" = Table.RenameColumns(#"Duplicated Column",{{"activityid - Copy", "activityid_Phone Call"}}),
#"Merged Queries2" = Table.NestedJoin(#"Renamed Columns2",{"activityid_Phone Call"},phonecalls,{"activityid"},"phonecalls",JoinKind.LeftOuter),
#"Expanded phonecalls" = Table.ExpandTableColumn(#"Merged Queries2", "phonecalls", {"ia_smartnotes", "Phone_Call_Type_Name", "Smart Objective"}, {"phonecalls.ia_smartnotes", "phonecalls.Phone_Call_Type_Name", "phonecalls.Smart Objective"}),
#"Added Custom3" = Table.AddColumn(#"Expanded phonecalls", "Call Type", each if [appointments.Appointment_Type_Name]<>null
then [appointments.Appointment_Type_Name]
else if [phonecalls.Phone_Call_Type_Name]<>null
then [phonecalls.Phone_Call_Type_Name]
else [Call Cycle Type Name]),
#"Added Custom5" = Table.AddColumn(#"Added Custom3", "Smart Notes", each if [appointments.ia_smartnotes]<>null
then [appointments.ia_smartnotes]
else [phonecalls.ia_smartnotes]),
#"Added Custom6" = Table.AddColumn(#"Added Custom5", "Smart Objective", each if [appointments.Smart Objective]<>null
then [appointments.Smart Objective]
else [phonecalls.Smart Objective]),
#"Merged Queries3" = Table.NestedJoin(#"Added Custom6",{"Comp_AccountID"},Accounts,{"accountid"},"Accounts",JoinKind.LeftOuter),
#"Expanded Accounts" = Table.ExpandTableColumn(#"Merged Queries3", "Accounts", {"Facility Type", "Head Office", "Region", "State Code","Account Name","City"}, {"Accounts.Facility Type", "Accounts.Head Office", "Accounts.Region", "Accounts.State Code","Accounts.Account Name","Accounts.City"}),
#"Merged Queries4" = Table.NestedJoin(#"Expanded Accounts",{"Comp_ContactID"},Contacts,{"contactid"},"Contacts",JoinKind.LeftOuter),
#"Expanded Contacts" = Table.ExpandTableColumn(#"Merged Queries4", "Contacts", {"Account Region", "Account State Code", "Contact Role", "Full Name"}, {"Contacts.Account Region", "Contacts.Account State Code", "Contacts.Contact Role", "Contacts.Full Name"}),
#"Added Custom7" = Table.AddColumn(#"Expanded Contacts", "Facility Type", each if [Accounts.Facility Type]<>null and [Accounts.Head Office]="Yes"
then "Head Office " & [Accounts.Facility Type]
else [Accounts.Facility Type]),
#"Added Custom8" = Table.AddColumn(#"Added Custom7", "State", each if [Accounts.State Code]<>null
then [Accounts.State Code]
else [Contacts.Account State Code]),
#"Added Custom9" = Table.AddColumn(#"Added Custom8", "Region", each if [Accounts.Region]<>null
then [Accounts.Region]
else [Contacts.Account Region]),
#"Renamed Columns3" = Table.RenameColumns(#"Added Custom9",{{"Contacts.Contact Role", "Contact Role"}}),
#"Added Custom10" = Table.AddColumn(#"Renamed Columns3", "URL", each if [activitytypecode]="phonecall"
then "https://ia.crm6.dynamics.com/main.aspx?etc=4210&extraqs=formid%3dccad621c-0628-4c81-9839-f9b18fbcc95..."
else "https://ia.crm6.dynamics.com/main.aspx?etc=4201&extraqs=formid%3d549a8aa3-c349-4ab9-8bac-4873fa9a7a5...")
in
#"Added Custom10"

[Amal]

Hi,

I have finally found the issue. I was doing a merge on a custom column(composite account id), but as soon as I add a step to expand any columns on the merge table I’m getting the refresh error on the web version (But Power BI desktop version is fine). 

[SimenJ]

Interesting! Do you expand the columns with a prefix or not?

-Simen

[Amal]

Hi Simen,

Yes, I'm expanding with the prefix. But I also tried without the prefix. 

[Amal]

Hi Simen,

My actual issue was related to data privacy since I’m using multiple data sources. Used below link to solve my issue.

https://community.powerbi.com/t5/Service/Data-Privacy-Broken-We-cannot-convert-the-value-quot-Record...