Bulk change data types in selected columns

[monojchakrab]

Hey good people,

I am trying to bulk transform the columns with numeric data but received in wrong data type for a table as below :

Item QuantityOrder Shipped From (State)Price before discountTotal DiscountSeller Share Bank Offer SharePrice after discount (Price before discount-Total discount)

1.0	IN-GJ	300.0	0.0	0.0	0.0	300.0
2.0	IN-GJ	1120.0	0.0	0.0	0.0	1120.0
1.0	IN-GJ	300.0	0.0	0.0	0.0	300.0
1.0	IN-GJ	320.0	0.0	0.0	0.0	320.0
1.0	IN-GJ	300.0	0.0	0.0	0.0	300.0
1.0	IN-GJ	240.0	0.0	0.0	0.0	240.0
1.0	IN-GJ	240.0	0.0	0.0	0.0	240.0
1.0	IN-GJ	240.0	0.0	0.0	0.0	240.0
1.0	IN-GJ	320.0	0.0	0.0	0.0	320.0
1.0	IN-GJ	320.0	0.0	0.0	0.0	320.0

But Table.Transform is taking too long and with such long column names, it is very likely, there will be several typos. 

I also tried Table.Tolist, to first get a list of the columns, but it generates a list of all the columns and not just the columns I need.

Is there a function or M code which does the trick?

Thanks and appreciate

[ronrsnfld · Accepted solution]

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"

[miguel · Accepted solution]

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!

[monojchakrab · Accepted solution]

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

[monojchakrab · Accepted solution]

@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

[miguel · Accepted solution]

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

[miguel · Accepted solution]

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!

[monojchakrab · Accepted solution]

@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

[miguel · Accepted solution]

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

[ronrsnfld · Accepted solution]

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"

[monojchakrab · Accepted solution]

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

[ronrsnfld · Accepted solution]

@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.