Forum Discussion

Dicken's avatar
Dicken
Post 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 ; use TableTransformColiumns, 

 

 Table.TransformColumns( Source,{{"T", each Text.From(_),type text }} , Number.From)


but  is there a way to set   for example ; Int.64.Type as a default ?   I do find the difference between the two 
a bit confusiing.   

Richard. 

 

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.

7 Replies

  • rohit1991's avatar
    rohit1991
    Super 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
      Post Prodigy
      1 year ago

      Thanks, that's quite a clever solution, 

    • Dicken's avatar
      Dicken
      Post Prodigy
      1 year ago

      Actually it doesn't quite work, as the all end up as number, where I want the text columns as text 
      and number as Int64.  so as  transform type ; Table.TransformColumnTypes( Source,{ {"T", type text }, {"N", Int64.Type }} )

      or a default as number 
      Table.TransformColumns( Source,{ {"T", Text.From}} , Number.From)

      if not a default  this works; 
      = Table.TransformColumns( Source, {"T", Text.From}, { "N", Number.From, Int64.Type }} )

      But I have not been able to get it as a default option. 
      I shall persevere. 

      Richad. 

      • lbendlin's avatar
        lbendlin
        Super User
        1 year ago

        Please define more clearly what you are trying to accomplish. "Default" is a subjective term.

  • lbendlin's avatar
    lbendlin
    Super User
    1 year ago

    Please define what you mean by "default".  If you have disabled automatic type guessing then the column type depends on the available meta data from the source. A SQL source will give you the SQL data types, a CSV source will give you all text etc.  Default in Power Query is "ABC123" - type "any"  which is not something you want to present to Power BI.

     

    Table.TransformColumnTypes - you could argue that it should be called "SetColumnTypes" or something. All it does is attempt to apply the new type to all existing values in that column.

     

    Table.TransformColumns is infinitely more powerful and allows you to transform each individual item in that column in any which way.  It is then up to you to set an appropriate column type later.

  • dufoq3's avatar
    dufoq3
    Community 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