top of page
Writer's pictureBrunner_BI

Changing data types that do not break query-folding in Power Query / Power BI

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!

4,590 views4 comments

4 Comments


Guest
Dec 05, 2023

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

Like

anthony lecoq
anthony lecoq
Oct 06, 2022

Has someone tried it on SQL server 2019 or SQL server 2022 ?

Like

anthony lecoq
anthony lecoq
Oct 06, 2022

Hello, I have the same issue actually, trying to use Number.FromText but it break the folding :(


Like
Brunner_BI
Brunner_BI
Oct 06, 2022
Replying to

so you are trying to convert text to number, yes? yes according to our research (SQL Server 2016 about 2 years ago) that would break it...

Like
bottom of page