Forum Discussion

Dicken's avatar
Dicken
Icon for Post Prodigy rankPost Prodigy
1 year ago
Solved

Setting a default data type

Hi,    If you use Tabel.TransformColumnTypes; i.e;    Table.TransformColumnTypes(  Source,  {{"T", type text }, { "N", Int64.Type }} )     but this does not allow for a default setting , so ; u...
General Comment
Need Help
  • rohit1991's avatar
    rohit1991
    1 year ago

    hi Dicken ,

    To set a default data type dynamically in Power Query, use Table.TransformColumns with try...otherwise to apply a transformation to all columns:

    let
    Source = YourTable,
    SetDefaultType = Table.TransformColumns(
        Source, 
        List.Transform(
            Table.ColumnNames(Source),
            each {_, each try Number.From(_) otherwise _, Int64.Type}
        )
    )
in
    SetDefaultType

    This applies Int64.Type to numeric columns while leaving others unchanged, avoiding manual column-by-column specification.

rohit1991's avatar
rohit1991
Icon for Super User rankSuper User
1 year ago

hi Dicken ,

To set a default data type dynamically in Power Query, use Table.TransformColumns with try...otherwise to apply a transformation to all columns:

let
    Source = YourTable,
    SetDefaultType = Table.TransformColumns(
        Source, 
        List.Transform(
            Table.ColumnNames(Source),
            each {_, each try Number.From(_) otherwise _, Int64.Type}
        )
    )
in
    SetDefaultType

This applies Int64.Type to numeric columns while leaving others unchanged, avoiding manual column-by-column specification.

Dicken's avatar
Dicken
Icon for Post Prodigy rankPost Prodigy
1 year ago

Thanks, that's quite a clever solution,