query folding and change of column data type

[Anonymous]

Hello,

I need to concatenate numeric columns in my table using Add.Column, like:

= Table.AddColumn(Custom1, "UniqueID", each (Text.From([CaseID])&Text.From([SolutionID])), Int64.Type)

I thought using the "Int64.Type" at the end of the step should format the new column as whole number automatically, however it seems that they are just recognized as text. When I then manually convert the column to "whole number" query folding doesn't happen anymore. Is there an error on my side or a workaround to ahve query folding happen even after a column data type is changed?

[Brunner_BI]

There are some changes to data types that allow and some that will break query-folding.

I actually wrote a blog post about this (valid for SQL Server 2016)

https://www.brunner.bi/post/changing-data-types-that-do-not-break-query-folding-in-power-query-power...

[Stachu · Accepted solution]

if I understand the language specification correctly Type.Int64 is just an extension of type number. See here:

https://docs.microsoft.com/en-us/powerquery-m/power-query-m-language-specification, (see page 46):

The M type system consists of the following kinds of types:
• Primitive types, which classify primitive values (binary, date, datetime,
datetimezone, duration, list, logical, null, number, record, text, time, type)
and also include a number of abstract types (function, table, any, and none)

I would try using 'type number' instead of Type.Int64, hopefully it works (it may be limitation of Direct Query though, which I assume you are using, since you mentioned folding).. You can then change format in DAX separately from the type

[Jon_vB]

Thanks for this!  When I first changed from 'int64.type' to 'type number', it did NOT resolve the issue. I had some old non-numeric values that caused errors and that (I guess) also blocked query folding.   Once I restricted my query to IsNumeric = 1 on the SQL side, your fix worked.   Though - also - once that was resolved, I didn't need the Transform column at all --- Power Query could then guess correctly that the data type was numeric (it didn't get "integer").    So, maybe Power Query got an updgrade since this issue was first raised here.

[suebayes]

You've just helped my query folding go from red to blue in the dataflow window

[Anonymous]

Thanks, luckily that was an easy fix for my issue!