PBI desktop does not pull full data from dataflows

[LocNguyen]

Hello,

I ran into an issue today regarding pulling data from Dataflows.

I tried to import data from Dataflows into my PBi Desktop. Unfortunately, there is one column completely blank (the others columns are still good).

I already tried refreshing the dataflow again, refreshing PBI Desktop, and Clearing Permission for Dataflow; but none of them resolved my issue.

Below is the screenshot of my Dataflow. Column new_type_display is having data:

 

And here is my PBI Desktop navigator looks like when I tried importing the data. The same column is completely blank.

Has anyone else run into this issue? Did you have any other solutions?

Thanks,

[mwegener]

Hi @LocNguyen ,

did you map the data flow to a CDM (Common Data Model) standard entity?

I think the "_display" fields are looked up in another entity.

Regards,

Marcus

Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.

[LocNguyen]

@mwegener 

Yes I mapped it from Microsoft Dynamics CRM. Please see the query below:

Source = Cds.Entities("https://ebs1.crm.dynamics.com", null),
  #"Navigation 1" = Source{[Group = "entities"]}[Data],
  #"Navigation 2" = #"Navigation 1"{[EntitySetName = "psa_regions"]}[Data],
  #"Choose columns" = Table.SelectColumns(#"Navigation 2", {"new_code", "new_type_display", "psa_name", "psa_regionid"})

The column is just one of the default columns that I selected from the entity.

[mwegener]

Hi @LocNguyen 

try this

Source = Cds.Entities("https://ebs1.crm.dynamics.com", [ReorderColumns=null, UseFormattedValue=true]),

Regards,

Marcus

Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support. 

[LocNguyen]

@mwegener 

Still not working 😞

[mwegener · Accepted solution]

Hi @LocNguyen ,
I remember this issue.

You must also select the base field so that the _display field is transferred.

In your case it should be the field "new_type".

Source = Cds.Entities("https://ebs1.crm.dynamics.com", null),
  #"Navigation 1" = Source{[Group = "entities"]}[Data],
  #"Navigation 2" = #"Navigation 1"{[EntitySetName = "psa_regions"]}[Data],
  #"Choose columns" = Table.SelectColumns(#"Navigation 2", {"new_code", "new_type", "new_type_display", "psa_name", "psa_regionid"})

Or you can try to use Table.Buffer to avoid query folding.

Source = Cds.Entities("https://ebs1.crm.dynamics.com", null),
  #"Navigation 1" = Source{[Group = "entities"]}[Data],
  #"Navigation 2" = Table.Buffer(#"Navigation 1"{[EntitySetName = "psa_regions"]}[Data]),
  #"Choose columns" = Table.SelectColumns(#"Navigation 2", {"new_code", "new_type_display", "psa_name", "psa_regionid"})

Regards,

Marcus

Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.

[LocNguyen]

The Table.Buffer works great. Thanks so much, @mwegener !