Forum Discussion

Dicken's avatar
Dicken
Icon for Post Prodigy rankPost Prodigy
4 months ago
Solved

Transform columns / type

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...
Need Help
Show and Tell
  • m_dekorte's avatar
    m_dekorte
    4 months ago

    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.

  • Ahmedx's avatar
    Ahmedx
    4 months ago

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

Ahmedx's avatar
Ahmedx
Icon for Super User rankSuper User
4 months ago

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

Dicken's avatar
Dicken
Icon for Post Prodigy rankPost Prodigy
4 months ago

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's avatar
    Ahmedx
    Icon for Super User rankSuper User
    4 months ago

    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