cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jnickell
Level: Powered On

Inconsistent Data Query Results

I'm attempting to set up a Data Flow for Powerbi.  Relying on the guides that are out there, I'm connecting to the Common Data Service and providing the URL of our Dynamics 365 Environment.

I selected the Opportunities Entity from the available options. this all seems to go fine and I'm able to map to the standard CDS Opportunity Entity type.  

I then proceed to edit the query to remove some of the columns that I do not need. 

I added one conditional column that set its value depending on the presenced of an originatingLeadID value.

All this seems to work without issue.

On several fields and in particular the "opportunityRatingCode_display" the values are not simply not present at the end of the applied transformation steps.  I know of one opportunity that I'm testing with the value should be "Medium", however at the end of my transformations, the value in the field is "null".

 

IF, however, I apply a column filter prior to my "remove column steps" to limit the result set to the single opportunity, I _will_ have the correct Opportunity Rating in the field.  (i.e. Filter opportunityID = XXX)

 

We have 2,266 opportunities in the data set at present, I'm guessing this is having some impact?  I don not have incremental refresh in my license (Power BI pro). 

I do not believe my transformations are "at fault".  They are very simple:

  1. Source
  2. Navigation
  3. Map to Standard
  4. Removed Columns (getting rid of all the "_base" columns)
  5. Removed Other Columns (selecting just the desired columns)
  6. Add Conditional column
  7. Change Type (on the conditional column to Text)
  8. Rename Columns

If I insert a filter step between 3 & 4, the Opportunity Rating field is correct both at that level and at step 8.  If I only inset the filter step at the end, the Opportunity Rating field is "null"

 

Any insight would be appreciated. Thanks!

4 REPLIES 4
jnickell
Level: Powered On

Re: Inconsistent Data Query Results

Okay a little more insight as I'm trying to look into this.  

If I add the Filter Row transformation AFTER the Conditional Column I get the "null" value.

If the filter Row comes BEFORE the conditional column, I do not loose Opportunity Rating

 

So here's the code of the Conditional column when it comes "before" the Filter Row and causes the issue

 

= Table.AddColumn(#"Removed Other Columns", "Source", each if [originatingLeadId] = null then "Direct" else if [originatingLeadId] <> null then "Lead" else "undetermined")
Community Support Team
Community Support Team

Re: Inconsistent Data Query Results

Hi @jnickell ,

Could you please share a screenshot about the issue within your app?

 

Based on the issue that you mentioned, I have made a test on my side, and don't have the issue that you mentioned. Currently, I could not reproduce your issue on my side.

 

If you would like to get further help within this issue, please consider open an thread within the Power BI forum:

https://community.powerbi.com/t5/Forums/ct-p/PBI_Comm_Forums

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
jnickell
Level: Powered On

Re: Inconsistent Data Query Results

I did post a question on the Power BI Forums, the response so far has been to open a support ticket.  
I've added some screenshots to illustrate what I'm seeing.

 

capture20190711095841439.png

 

The transformation command for the "Removed Columns" is:

Table.RemoveColumns(#"Renamed Columns", {"_r_bespoke_deisgnlevel", "_r_new_marketclassfication", "_r_opportunityRatingCode"})

capture20190711095914074.png

 

I would note that when I first went to reproduce the issue for you that I tried adding the remove columns at the end of my transformations (after the Merge queries) and the issue did not present itself there. 

 

Here's the thread I opened on the Power BI community forums

https://community.powerbi.com/t5/Desktop/M-Query-amp-Common-Data-Service-data-loss/m-p/736725#M35529...

jnickell
Level: Powered On

Re: Inconsistent Data Query Results

Unfortunately, I was unable to reproduce the issue while on the phone with support.  Bummer. 

Helpful resources

Announcements
firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

SecondImage

Power Platform Summit North America

Register by September 5 to save $200

Users Online
Currently online: 50 members 5,000 guests
Please welcome our newest community members: