Data incorrect with Incremental refresh on PBI service, but correct on PBI desktop

[MikeKKK]

I am setting up Incremental Refresh and checking the result against the full data refresh result on PBI service. It comes to my attention that there is data discrepancy , and it always happens on 1st day of the month. However, this disrepancy does not happen on PBI desktop

Example:

PBI service:  1Feb20 data incorrect

PBI Desktop: no data discrepancy

I suspect it has something to do with query folding on the datekey 

The datekey in the fact table comes with Shift 1 and Shift 2, for example on Christmas Day there will be 2 keys 202012251 and 202012252

Thus, in PowerQuery date filter step is

= Table.SelectRows(#"Changed Type",

each [Shift_SK] >= Number.FromText(Date.ToText(DateTime.Date(RangeStart),"YYYYMMDD")&"1")

and

[Shift_SK] < Number.FromText(Date.ToText(DateTime.Date(RangeEnd),"YYYYMMDD")&"2") )

The DateKey is later transformed to Date type

Incremental Refresh Configuration:

Thanks in advance for any help.

[MikeKKK]

Hi @gtacchini 

Thanks for your response. 

 I do not have access to modify the data source (to create a new column and so). 

I am not able to find a method to transform shift (integer type) to date (e.g. 202012251 to 25Dec2020) that does not break the query folding. I thought the incremental refresh will still work since the RangeStart and RangeEnd that filter the dates are query folding enabled?

[gtacchini]

Hi @MikeKKK, 

First thing I would comment on is, why do you want to use incremental refresh if you don't have query folding? Without query folding on your entire query, you don't get any benefits with incremental refresh. When you refresh a query without folding, the service needs to cache all the info to then apply the next stepts. I would strongly recommend you to create properly formatted columns on your data source if possible.

Next try this: Troubleshoot incremental refresh in Power BI - Power BI | Microsoft Docs

Using SQL Server profiler you can check the query sent to your data source in power BI service, there we can verify how your query is being interpreted, I would guess that using those steps after the "Grouped Rows" step breaks the query. You will not be able to see the incremental refresh query on Power BI desktop as incremental refresh is only executed on the service. On the first refresh you get all your data, then on subsequent ones you only refresh the new content based on your filter column. 

I hope this is helpful to you. I await more detail to help you further!!