Dataflow is missing information from columns

[jnickell]

I have a dataflow pointed to Dynamic CRM via the CDS model.  I created the dataflow in PBI Desktop and used copy/paste for the advanced editor to create the dataflow on the Power BI service.  

When viewed in the online Power Query editor I have values in these columns. Specifically I'm looking ath the State and Status columns.  The query saves and refreshes without issue.

I have another PBI Desktop file pointed to this Dataflow. I'm receiving no errors, but these two columns in particular have NO values. No record in the dataset has a value for these columns.  

Could anyone shed some light on what I'm seeing?  

Here's a screenshot of the service Query editor (right) and the consuming PBI desktop files's PBI Query editor. The same rows are in view in both. 

Appreciate any advice or pointers.  I have refreshed the consuming PBI file and am in the process of refreshing the Dataflow as well. 

I opened a support ticket and am waiting on the initial troubleshooting session, but in the mean time I seem to be able to reproduce this within the PBI desktop file that I'm creating the M code for the dataflow.

In "M" the preview shows data in the columns in question. In Report builder the data is not there.  So it doesn't appear to be specific to Dataflows.

[jnickell · Accepted solution]

Worked with Microsoft support today and got the cause narrowed down, but not explained.  

I am using the CDS's ability to return the _display column along with the "code" column.  
If I use Power Query and remove the "code" column the corresponding "_display" column will not show any values.  

Interestingly it seems that if I "only" have the "_display" columns, the values are preserved.  

They are escalating.  Below is the M code. The "State Code", "Status Code" columns are the ones of most interest right now, but it appears to affect other columns of the same origin as well. 

let
  Source = Cds.Entities("https://<your>.crm.dynamics.com", null),
  #"Navigation 1" = Source{[Group = "entities"]}[Data],
  #"Navigation 2" = #"Navigation 1"{[EntitySetName = "activitypointers"]}[Data],
  #"Added CreatedOn" = Table.AddColumn(#"Navigation 2", "CreatedDate", each Date.From(DateTime.From([createdon])), type date),
  #"Added ModifiedOn" = Table.AddColumn(#"Added CreatedOn", "ModifiedDate", each Date.From(DateTime.From([modifiedon])), type date),
  #"Added ActivityEndDate" = Table.AddColumn(#"Added ModifiedOn", "ActivityEndDate", each DateTime.Date(if [actualend] = null then [scheduledend] else [actualend]), type date),
  #"Lang-Added ActivityType" = Table.AddColumn(#"Added ActivityEndDate", "ActivityType", each if [activitytypecode] = "phonecall" then "Phone Call" else
	if [activitytypecode] = "task" then "Task" else
	if [activitytypecode] = "email" then "Email" else
	if [activitytypecode] = "fax" then "Fax" else
	if [activitytypecode] = "letter" then "Letter" else
	if [activitytypecode] = "incidentresolution" then "Case Resolution" else
	if [activitytypecode] = "opportunityclose" then "Opportunity Close" else
	if [activitytypecode] = "appointment" then "Appointment" else
	if [activitytypecode] = "orderclose" then "Order Close" else
	if [activitytypecode] = "quoteclose" then "Quote Close" else
	if [activitytypecode] = "quoteclose" then "Quote Close" else
	if [activitytypecode] = "serviceactivity" then "Service Activity" else
	if [activitytypecode] = "campaignactivity" then "Campaign Activity" else
	if [activitytypecode] = "campaignresponse" then "Campaign Response" else
	if [activitytypecode] = "bulkoperation" then "Bulk Operation" else
	if [activitytypecode] = "recurringappointment" then "Recurring Appointment" else
	[activitytypecode], type text),
  #"Lang - Renamed Columns" = Table.RenameColumns(#"Lang-Added ActivityType", {{"activityid", "Activity"}, {"activitytypecode", "Activity Type Code"}, {"ActivityType", "Activity Type"}, {"actualdurationminutes", "Actual Duration"}, {"actualstart", "Actual Start"}, {"actualend", "Actual End"}, {"CreatedDate", "Created Date"}, {"createdon", "Date Created"}, {"instancetypecode", "Instance Type Code"}, {"instancetypecode_display", "Instance Type"}, {"isregularactivity", "Is Regular Activity"}, {"ModifiedDate", "Modified Date"}, {"modifiedon", "Last Updated"}, {"ownerid", "Owner"}, {"owningbusinessunit", "Owning Business Unit"}, {"owninguser", "Owning User"}, {"prioritycode", "Priority Code"}, {"prioritycode_display", "Priority"}, {"scheduleddurationminutes", "Scheduled Duration"}, {"scheduledend", "Due Date"}, {"scheduledstart", "Start Date"}, {"senton", "Date Sent"}, {"statecode", "State Code"}, {"statecode_display", "State"}, {"statuscode", "Status Code"}, {"statuscode_display", "Status"}, {"subject", "Subject"}, {"regardingobjectid", "Regarding"}, {"ActivityEndDate", "Activity End Date"}}),
  #"Removed Columns" = Table.RemoveColumns(#"Lang - Renamed Columns", {"community", "community_display", "description", "Instance Type Code", "ismapiprivate", "isworkflowcreated", "Priority Code", "seriesid", "serviceid", "slaid", "slainvokedid", "sortdate", "stageid", "State Code", "Status Code"})
in
  #"Removed Columns"

[jnickell · Accepted solution]

This was the final response I got from Microsoft Support

When using the Common Data Service connector, to receive the _display columns the value they are based on, must also be returned in as part of the query. The fields can be hidden at the data model level if you don’t want it to show on the report. As the solution, to keep the original columns in your query and hide the fields or try using the new “Common Data Service (Beta)”/”Dataverse” connector.

[jnickell · Accepted solution]

Worked with Microsoft support today and got the cause narrowed down, but not explained.  

I am using the CDS's ability to return the _display column along with the "code" column.  
If I use Power Query and remove the "code" column the corresponding "_display" column will not show any values.  

Interestingly it seems that if I "only" have the "_display" columns, the values are preserved.  

They are escalating.  Below is the M code. The "State Code", "Status Code" columns are the ones of most interest right now, but it appears to affect other columns of the same origin as well. 

let
  Source = Cds.Entities("https://<your>.crm.dynamics.com", null),
  #"Navigation 1" = Source{[Group = "entities"]}[Data],
  #"Navigation 2" = #"Navigation 1"{[EntitySetName = "activitypointers"]}[Data],
  #"Added CreatedOn" = Table.AddColumn(#"Navigation 2", "CreatedDate", each Date.From(DateTime.From([createdon])), type date),
  #"Added ModifiedOn" = Table.AddColumn(#"Added CreatedOn", "ModifiedDate", each Date.From(DateTime.From([modifiedon])), type date),
  #"Added ActivityEndDate" = Table.AddColumn(#"Added ModifiedOn", "ActivityEndDate", each DateTime.Date(if [actualend] = null then [scheduledend] else [actualend]), type date),
  #"Lang-Added ActivityType" = Table.AddColumn(#"Added ActivityEndDate", "ActivityType", each if [activitytypecode] = "phonecall" then "Phone Call" else
	if [activitytypecode] = "task" then "Task" else
	if [activitytypecode] = "email" then "Email" else
	if [activitytypecode] = "fax" then "Fax" else
	if [activitytypecode] = "letter" then "Letter" else
	if [activitytypecode] = "incidentresolution" then "Case Resolution" else
	if [activitytypecode] = "opportunityclose" then "Opportunity Close" else
	if [activitytypecode] = "appointment" then "Appointment" else
	if [activitytypecode] = "orderclose" then "Order Close" else
	if [activitytypecode] = "quoteclose" then "Quote Close" else
	if [activitytypecode] = "quoteclose" then "Quote Close" else
	if [activitytypecode] = "serviceactivity" then "Service Activity" else
	if [activitytypecode] = "campaignactivity" then "Campaign Activity" else
	if [activitytypecode] = "campaignresponse" then "Campaign Response" else
	if [activitytypecode] = "bulkoperation" then "Bulk Operation" else
	if [activitytypecode] = "recurringappointment" then "Recurring Appointment" else
	[activitytypecode], type text),
  #"Lang - Renamed Columns" = Table.RenameColumns(#"Lang-Added ActivityType", {{"activityid", "Activity"}, {"activitytypecode", "Activity Type Code"}, {"ActivityType", "Activity Type"}, {"actualdurationminutes", "Actual Duration"}, {"actualstart", "Actual Start"}, {"actualend", "Actual End"}, {"CreatedDate", "Created Date"}, {"createdon", "Date Created"}, {"instancetypecode", "Instance Type Code"}, {"instancetypecode_display", "Instance Type"}, {"isregularactivity", "Is Regular Activity"}, {"ModifiedDate", "Modified Date"}, {"modifiedon", "Last Updated"}, {"ownerid", "Owner"}, {"owningbusinessunit", "Owning Business Unit"}, {"owninguser", "Owning User"}, {"prioritycode", "Priority Code"}, {"prioritycode_display", "Priority"}, {"scheduleddurationminutes", "Scheduled Duration"}, {"scheduledend", "Due Date"}, {"scheduledstart", "Start Date"}, {"senton", "Date Sent"}, {"statecode", "State Code"}, {"statecode_display", "State"}, {"statuscode", "Status Code"}, {"statuscode_display", "Status"}, {"subject", "Subject"}, {"regardingobjectid", "Regarding"}, {"ActivityEndDate", "Activity End Date"}}),
  #"Removed Columns" = Table.RemoveColumns(#"Lang - Renamed Columns", {"community", "community_display", "description", "Instance Type Code", "ismapiprivate", "isworkflowcreated", "Priority Code", "seriesid", "serviceid", "slaid", "slainvokedid", "sortdate", "stageid", "State Code", "Status Code"})
in
  #"Removed Columns"