Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
jnickell
Helper V
Helper V

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 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. 2020-11-17 15_17_02-Power BI.png

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.

2 ACCEPTED SOLUTIONS
jnickell
Helper V
Helper V

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"

 

View solution in original post

jnickell
Helper V
Helper V

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.

View solution in original post

2 REPLIES 2
jnickell
Helper V
Helper V

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
Helper V
Helper V

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"

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors