I have a large dataset with 50M+ rows. Database is on sql server 2019.
In Power Query, all but the last step shows the native query. The last step is converting the value (for some reason Power query picks up the number as decimal) to whole number. When I right-click on this step it shows the native query option disabled.
Why is Decimal to Whole number is preventing Power Query from showing native query? What is the way to achieve native query in this situation?
My intention is to configure incremental load on this table.
Changing datatypes frequently breaks query folding and is mentioned in the docs. Here is a good read: https://blog.crossjoin.co.uk/2021/06/29/data-type-conversions-for-sql-server-sources-and-query-folding-in-power-query/
Connectors are regularly updated to include more native DB features and enable better folding.
EDIT - Don't do this - see David's comment for why this is not efficient
If PQ folds the conversion to, say, select cast(dt as date) d, ... from t and then the user filters on the projected column d, will result in SQL query like select ... where cast(dt as date) > '2022-01-01', which can't use indexes or partitions, and will have to convert the dt column for each row to compare it with the filter value. – David Browne - Microsoft 1 hour ago
According to this, you could try decimal to text and then extract the text before the decimal point to avoid breaking the query fold.