Forum Discussion

jnickell's avatar
jnickell
Helper V
5 years ago
Solved

Dataflow is missing information from columns

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 ...
  • jnickell's avatar
    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"

     

  • jnickell's avatar
    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.