Bulk change data types in selected columns

If the data type for the column can be defined as number depending on the first row of each column, you could use List.Accumulate to generate a transform list depending on whether that first entry could be converted to a number successfully.

eg:

    #"Columns with Numbers" = List.Accumulate(
        Table.ColumnNames(#"Previous Step"),
        {},
        (state, current)=>state & 
            {if Record.Field(
                    try (Number.From(Table.Column(#"Previous Step",current){0})),"HasError")
                        then null 
                        else {current,  type number}}),

    #"Type Transform List" = List.RemoveNulls(#"Columns with Numbers"),
    #"Change Type" = Table.TransformColumnTypes(#"Previous Step", #"Type Transform List")


in
    #"Change Type"

Hey!

You can use something like:

let
 myTable = <this is where my table goes>,
 Columns = Table.ColumnNames(myTable),
 ColumnCount = List.Count( Columns),
 DesiredType = type number,
 ListForNewTypes = List.Zip( { Columns, List.Repeat( {DesiredType}, ColumnCount)}),
 TableWithNewDataTypes = Table.TransformColumnTypes(myTable, ListForNewTypes)
in
 TableWithNewDataTypes

Hope this helps!

Hey ronrsnfld - thanks for the quick revert.

I will try this out....but what I want to achieve is actually as follows :

1. Select certain columns with the wrong data type - This is the challenge as we need to select a list of only the chosen columns which require the data type change

2. Then convert the data types in the columns selected in #1 to the desired data type, in bulk 

I am not sure I understand your M code fully, but it does not seem to be doing that...or is it? Apologies for my ignorance of M

Appreciate

miguel - Thanks for the leg-up.

I will try this out...looks like this will do the job.

Question :

Table.ColumnNames - will select all columns in the table, right? what happens :

1. If only certain columns are to be chosen and not all

2. If all the columns do not need to transformed to a single, uniform data type but can have different types - like a numeric column could be a whole number and another one could be a decimal?

But this is an elegant solution

Thanks and appreciate

You can modify the step called "ColumnNames" to only select the columns of your choosing. You define the logic that selects the columns that need to go through this. You can even override that code and pass your own list of column names.

Number 2 is a fundamentally different requirement, so a different approach would be needed.

at that point you can just pass a table that contains the columns and the types that you want them to have and construct a new logic around that

monojchakrab Perhaps I misunderstood when you indicated you had a problem with numeric data.

My code

• Checks the cell in the first row of each column in your table
• If that cell is a number, then make the data type = number
• If not, leave the data type as is.