DeltaLake.Table Data Loading Issue

[MatthewBI]

Description:
We are experiencing inconsistent row loading behavior when querying Delta tables through DeltaLake.Table() in Power Query (ADLS Gen2).

Context:

1. Our Delta table contains an invoice with InvoiceID = A and InvoiceDate = 2022-11-17 00:00:00.
2. This invoice has 19 related item rows (validated directly in Databricks using Spark SQL).
3. When loading through DeltaLake.Table() in Power Query with the filter InvoiceDate > #date(2021,12,31), only 18 rows load.

Workarounds (all return 19 rows as expected):

• Changing the filter from > to >=.
• Keeping > but adding an additional filter step (e.g. InvoiceDate < Today()).
• Using a community-built connector (GitHub) with the same filter (> #date(2021,12,31)).

https://github.com/delta-io/delta/issues/1978 

Expected behavior:
DeltaLake.Table() should return all 19 rows with the filter InvoiceDate > #date(2021,12,31) — consistent with both Databricks and the github function. 

Observed behavior:
One row is missing when using DeltaLake.Table() with the > operator.

Notes:

• The missing row has InvoiceDate = 2022-11-17 00:00:00 (as all other items for this invoice).

For this reason, we are concerned of a potential issue in how DeltaLake.Table() applies filters compared to the underlying Delta engine especially because the > filter behaves as expected using the github function. 

[v-sdhruv]

Hi @MatthewBI ,

Just wanted to check if you got a chance to review the suggestion provided and were able to resolve the issue?
If you are still facing any problem, let us know.
Thank you @Shahid12523  for your input.

[MatthewBI]

The solution seems to be what we suggested ourselves and has worked. However, I am still concerned that > filtering does not apply correctly

[Shahid12523 · Accepted solution]

The missing row is due to a bug in DeltaLake.Table() filter folding — #date with > causes a precision mismatch.

How to Fixes:

Use #datetime(2021,12,31,0,0,0) instead of #date.

Or use >=.

Or apply the filter inside Power Query (not folded).

Or use the GitHub connector (works correctly).

It’s a connector bug — safe to log with Microsoft.