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.

dufoq3's avatar
dufoq3
Icon for Community Champion rankCommunity Champion
1 year ago

Hi Dicken, check this:

 

Before:

 

Define Types and Columns in DefinedTypes step:
(if you don't need i.e. Int - just delete whole line. You can also use number) - types are not case sensitive.

 

After:

 

let
    Source = Table.FromList({{1, "a", "b", "2", "c", 20.00}}, (x)=> x),
    DefinedTypes = [ //Use {} for all other columns.
    Currency = {"Column6"},
    Int = {"Column1", "Column4"},
    text = {}
  ],
    L = [ pairs = [cur = Currency.Type, int = Int64.Type, num = type number, text = type text],
    fnCorrecctType = each Record.Field(pairs, List.First(List.Select(Record.FieldNames(pairs), (x)=> Text.Contains(_, x, Comparer.OrdinalIgnoreCase)), "text")) ,
    a = Record.ToList(DefinedTypes),
    b = Record.FieldNames(DefinedTypes),
    otherColsPos = List.PositionOf(a, {}),
    otherColsType = fnCorrecctType(b{otherColsPos}),
    defColsPos = List.Difference(List.Positions(a), {otherColsPos}),
    defCols = List.Combine(List.Transform(defColsPos, (x)=> a{x})),
    otherCols = List.Difference(Table.ColumnNames(Source), defCols),
    colsWithTypes = List.Combine(List.Transform(List.Zip({ List.Transform(defColsPos, (x)=> a{x}), List.Transform(defColsPos, (x)=> fnCorrecctType(b{x})) }), (z)=> List.Transform(z{0}, (y)=> {y, z{1}}))) & List.Transform(otherCols, (w)=> {w, otherColsType} )
  ][colsWithTypes],
    ChangedType = if List.ContainsAll(Table.ColumnNames(Source), List.Combine(Record.ToList(DefinedTypes))) then Table.TransformColumnTypes(Source, List.Transform(L, (x)=> {x{0}, x{1}})) else error "WRONG COLUMN NAME: Check defined column names in 'DefinedTypes' step!"
in
    ChangedType