Differing rows between the online Dataflow and Desktop Power Query Editor

[Dayna]

Hi All,

I've got a really strange issue... I have a dataflow called 'Accounts' which I'm loading into my report, I've recently made a few changes to the dataflow by adding key fields, etc. and enabling incremental loading (a date field for modified records is one of the fields I've recently added). 

Now, in the report, within the Power Query Editor, I'm bringing back two rows which is causing errors on my join (as it's a unique reference). But, when I look in the data flow, there's only one record! 

I've done a refresh preview, and loaded in the table as a separate query, but it's still bringing back two rows whereas the service only has one.

To expand, I've also refreshed the data in the service. My next step is to remove the incremental laoad and manually refresh the report, but wondering if perhaps I need to clear the desktop cache, and if so, how?

Many thanks,

Dayna

[Dayna · Accepted solution]

I ended up raising a ticket with Microsoft for this and in summary, they think something went wrong with the dataflow merge and the resolution was to recreate it, as when exported to JSON, it was fine. 

[parry2k]

@Dayna ha! I missed that dataflow part, seems like this is something to do with the time zone. Do you have a schedule refresh set on this or you are doing the refresh manually.

[Dayna]

I do have a schedule refresh, think it sets the timezone to be UTC...! Could this be the problem? 

[v-kkf-msft]

Hi @Dayna ,

1. What is the version of Desktop you are using?

2. Are the two rows you are loading in Desktop duplicate values? If they are duplicate, is the date of this duplicate row the archived date (i.e. last 5 years) or the incremental refreshed data (i.e. last 3 months)? As far as I know, even with incremental refresh, what you see in the dataflow are still the data between RangeStart and RangeEnd, not all date. So the possible reason for the inconsistent data seen in the dataflow and Desktop is that the archived date and incremental refreshed date set for the incremental refresh override the parameters range.

3. What is your data source? Is this situation for all data sources or for one?

4. If your data does not contain sensitive data, could you share the .json file of your dataflow?

Best Regards,
Winniz

[parry2k]

@Dayna make sure when you configured the incremental refresh your filter on rangestart and rangeend are not overlapping. This is the common issue I have seen, check filter data section at this post Configure incremental refresh and real-time data for Power BI datasets - Power BI | Microsoft Docs

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Dayna]

Hello,

I did this originally, then I saw somewhere on the forums that as PowerBI Premium capacity, I could do this in the dataflow. I simply had to go into the dataflow and on the table, select 'Incremental Refresh', all my date/time fields were available in the dropdown so I set the period to retain and the period to refresh.

If I've done it this way, do I still need to apply a filter?

So what I've done to most is set this to retain the last 5 years of data, but refresh the last 3 months.

https://community.powerbi.com/t5/Desktop/How-to-configure-incremental-refresh-in-Power-BI/td-p/10361...

Many thanks,
Dayna