Setting a default data type

[Dicken]

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. 

[dufoq3]

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

[rohit1991 · Accepted solution]

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]

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]

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

[Dicken]

Thanks, that's quite a clever solution, 

[lbendlin]

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.

[Dicken]

I mean the default ?