Forum Discussion
DeltaLake.Table Data Loading Issue
Description:
We are experiencing inconsistent row loading behavior when querying Delta tables through DeltaLake.Table() in Power Query (ADLS Gen2).
Context:
- Our Delta table contains an invoice with InvoiceID = A and InvoiceDate = 2022-11-17 00:00:00.
- This invoice has 19 related item rows (validated directly in Databricks using Spark SQL).
- 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.
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.
3 Replies
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.
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.The solution seems to be what we suggested ourselves and has worked. However, I am still concerned that > filtering does not apply correctly
