Forum Discussion
query folding and change of column data type
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?
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
5 Replies
- Stachu
Community Champion
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
suebayesResolver I
You've just helped my query folding go from red to blue in the dataflow window
Jon_vBAdvocate II
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.
- Brunner_BI
Impactful Individual
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)
