Forum Discussion
query folding and change of column data type
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
Community Championif 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
Advocate IIThanks 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.