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.

Dicken's avatar
Dicken
Icon for Post Prodigy rankPost 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
Icon for Super User rankSuper User
1 year ago

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