Transform columns / type

[Dicken]

Hello, 
can anyone explain why when using transform columns if the function is used on it's own,
the declared column type also changes, but if the full syntax is used, it just the column values are changed which is what 
i would expect. 

value and type ;

let
  Source = #table(
    type table [sometext = Any.Type, anumber = Any.Type, aint = Any.Type],
    {{"cat", 10.5, 12}}
  ),
  Custom1 = Table.TransformColumns(Source, {"anumber", Number.From} ) 
in
  Custom1

but just value; 

let
  Source = #table(
    type table [sometext = Any.Type, anumber = Any.Type, aint = Any.Type],
    {{"cat", 10.5, 12}}
  ),
  Custom1 = Table.TransformColumns(Source, {"anumber", each Number.From(_)})
in
  Custom1

and as a default again  column and type ;

= Table.TransformColumns(Source, {}, Text.From )

so i can see ( i think )  why the full syntaxt operates only on the column values, and that is what it's supposed to to 
and      each text.form(_)     operates on each value, but what happens  when just the function used.   ? 

Not a problem just interesting how it works. 

Richard. 

[m_dekorte · Accepted solution]

Hi @Dicken,

When you write:

Table.TransformColumns(Source, {"anumber", Number.From})

you are passing Power Query the built-in Number.From function directly. Because that function has a known return type of number, Power Query can often detect that and update the column’s type automatically.

When you write:

Table.TransformColumns(Source, {"anumber", each Number.From(_)})

you are creating a new inline anonymous function. Even though it calls Number.From, that function itself is untyped. As a result, only the values are converted.

To ascribe type explicitly when using each, pass the new column type as third argument:

Table.TransformColumns(Source, {"anumber", each Number.From(_), type number})

When you write:

ascribeNum = (x) as number => Number.From(x),
transform = Table.TransformColumns(Source, {"anumber", ascribeNum})

you are creating a new function with a return type of number. Like when passing the built-in Number.From function directly, Power Query can often detect that and update the column’s type.

I hope this is helpful.

[Mohamed32]

Here’s a Great question — this behavior in Power Query can be a bit confusing at first.

When you use Transform Columns with a function only (e.g. each Text.Upper(_)), Power Query may automatically re-evaluate and update the column’s data type based on the result of that function. This happens because Power Query tries to infer the new type after transformation.

However, when you use the full syntax and explicitly define the type (e.g. {"ColumnName", each Text.Upper(_), type text}), you are telling Power Query to apply the transformation but keep (or enforce) a specific data type.

So the difference comes down to:

Without type → Power Query infers the resulting data type

With type → You control the resulting data type explicitly

That’s why in the second case only the values change, while the data type remains consistent as expected.

[Ahmedx · Accepted solution]

In the third parameter you can specify the column type
Why does Power Query behave this way? Is it because of "lazy evaluation"

[Dicken]

regarding  adding a column type ;   you can pre build that as ; 

{ "atext", each Text.From(_), Text.Type }

then if you have multiple   use zip; so ; 

= let f = { each Text.From(_), each Number.From(_),  each Int64.From(_), each Logical.From(_)}, 
tipe = { Text.Type, Number.Type, Int64.Type, Logical.Type }
in 
List.Zip( { Table.ColumnNames( Source) , f, tipe } )

then 
= Table.TransformColumns( Source, ziplist )

[Ahmedx]

If you are studying column typing, here are the options

let
  lst = List.LastN(Table.ColumnNames(Source), 2), 
  Source = #table(
    type table [sometext = Any.Type, anumber = Any.Type, aint = Any.Type], 
    {{"cat", 10.5, 12}}
  ), 
  Custom1 = Table.TransformColumns(
    Source, 
    List.Transform(lst, (x) => {x, Number.From, type number})
  )
in
  Custom1

----------------
let
  lst = List.LastN(Table.ColumnNames(Source), 2), 
  Source = #table(
    type table [sometext = Any.Type, anumber = Any.Type, aint = Any.Type], 
    {{"cat", 10.5, 12}}
  ), 
  Custom1 = Table.TransformColumns(
    Source, 
    List.Transform(lst, (x) => {x, (x) as nullable number => Number.From(x)})
  )
in
  Custom1

[Dicken]

Thanks for all replies  i have saved m_dekoets reply, to  as I may need to read a few times. 
have never notices, transform cols accepts a  data type, as to lazy evaluation,  thtat is something i''m beggining  to underdandn.  so thanks to all who repiied.  

[m_dekorte · Accepted solution]

Hi @Dicken,

When you write:

Table.TransformColumns(Source, {"anumber", Number.From})

you are passing Power Query the built-in Number.From function directly. Because that function has a known return type of number, Power Query can often detect that and update the column’s type automatically.

When you write:

Table.TransformColumns(Source, {"anumber", each Number.From(_)})

you are creating a new inline anonymous function. Even though it calls Number.From, that function itself is untyped. As a result, only the values are converted.

To ascribe type explicitly when using each, pass the new column type as third argument:

Table.TransformColumns(Source, {"anumber", each Number.From(_), type number})

When you write:

ascribeNum = (x) as number => Number.From(x),
transform = Table.TransformColumns(Source, {"anumber", ascribeNum})

you are creating a new function with a return type of number. Like when passing the built-in Number.From function directly, Power Query can often detect that and update the column’s type.

I hope this is helpful.