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.

lbendlin's avatar
lbendlin
Icon for Super User rankSuper 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.

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

    I mean the default ?