Incremental refresh > no data > Empty partitions

[jeroen_guns]

Hi,

Trying incremental refresh on one datawarehouse tabel. Yet the result is no data is loaded and available.

I've load a facttabel with these load date/times. In powerBI desktop everything look fine and 8 million records are imported.

2022-03-28 00:00:00.000
2022-03-29 00:00:00.000
2022-03-30 00:00:00.000
2022-03-31 00:00:00.000
2022-04-01 00:00:00.000
2022-04-04 00:00:00.000

= Table.SelectRows(HCS_vw_Feit_verrichtingenTest_incremental_load, each [Loaddate] > RangeStart and [Loaddate] < RangeEnd)

I've then uploaded it to the powerBI service and refreshed multiple times. Yet there is no data available in the dataset/report.
If I use the xmla endpoint and look at it in SSMS I see that the partitions have been made but they contain no data. Number of rows is zero.

The native query looks ok
from [HCS].[vw_Feit_verrichtingenTest_incremental_load] as [_]
where [_].[Loaddate] > convert(datetime2, '2022-03-28 00:00:00') and [_].[Loaddate] < convert(datetime2, '2099-01-01 00:00:00')

Aad if I view the json it also looks ok.
{
"createOrReplace": {
"object": {
"database": "Test_feit_DWH_icremental load",
"table": "HCS vw_Feit_verrichtingenTest_incremental_load",
"partition": "2022Q20405"
},
"partition": {
"name": "2022Q20405",
"mode": "import",
"source": {
"type": "policyRange",
"start": "2022-04-05T00:00:00",
"end": "2022-04-06T00:00:00",
"granularity": "day"
}
}
}
}

So Why do I not have any Data after implementing incremetal refresh. Anyone have an idea?

[GilbertQ · Accepted solution]

Hi @jeroen_guns 

The reason is that you need the start to be >= and the end to be < 

This happens because when the data is passed it does it for your example as the following when sent via PBI it would be: 
where [_].[Loaddate] > convert(datetime2, '2022-03-28 00:00:00') and [_].[Loaddate] < convert(datetime2, '2022-03-29 00:00:00')

Based on your example if you could change it to the following:

= Table.SelectRows(HCS_vw_Feit_verrichtingenTest_incremental_load, each [Loaddate] >= RangeStart and [Loaddate] < RangeEnd)

[golubovalex]

After few days of deep dive into potential query folding problems here is the answer for my issue, simple and straightforward 🙂 Thank you!

[jeroen_guns]

The difference an equal sign can make 😅

[GilbertQ]

Yeah those small things make a big difference!!