According to Microsoft's documentation, changing a column data type will break query folding.
Not all changes in data types will break query folding
This does not have to be the case though. It depends on your data source.
I tested the following Power Query data types:
text, decimal, whole number, date and datetime.
These transformations were possible and would not break query-folding:
Decimal -> text
Whole number -> decimal
Whole number -> text
Date -> datetime
Date -> datetimezone
Date -> decimal
Date -> text
Datetime -> date
Datetime -> datetimezone
Datetime -> decimal
Datetime -> text
Text -> all transformations would break query-folding
My database was running on SQL Server 2016, other versions might behave differently.
If you have had any experience with this feel free to comment!
The below talks about an approach to prevent this https://pawarbi.github.io/blog/powerquery/m/queryfolding/2022/06/01/query-folding-data-types-changes-powerbi.html
Has someone tried it on SQL server 2019 or SQL server 2022 ?
Hello, I have the same issue actually, trying to use Number.FromText but it break the folding :(