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,612 views4 comments

4 comentarios


Invitado
05 dic 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

Me gusta

anthony lecoq
anthony lecoq
06 oct 2022

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

Me gusta

anthony lecoq
anthony lecoq
06 oct 2022

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


Me gusta
Brunner_BI
Brunner_BI
06 oct 2022
Contestando a

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...

Me gusta
bottom of page