Forum Discussion
Dataflow is missing information from columns
- 5 years ago
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" - 5 years ago
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.
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.