cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
monojchakrab
Resolver II
Resolver II

Bulk change data types in selected columns

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.0IN-GJ300.00.00.00.0300.0
2.0IN-GJ1120.00.00.00.01120.0
1.0IN-GJ300.00.00.00.0300.0
1.0IN-GJ320.00.00.00.0320.0
1.0IN-GJ300.00.00.00.0300.0
1.0IN-GJ240.00.00.00.0240.0
1.0IN-GJ240.00.00.00.0240.0
1.0IN-GJ240.00.00.00.0240.0
1.0IN-GJ320.00.00.00.0320.0
1.0IN-GJ320.00.00.00.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

 

 

 

6 ACCEPTED SOLUTIONS
ronrsnfld
Solution Sage
Solution Sage

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"

View solution in original post

miguel
Microsoft
Microsoft

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!

View solution in original post

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

View solution in original post

@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

View solution in original post

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

View solution in original post

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

View solution in original post

6 REPLIES 6
miguel
Microsoft
Microsoft

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!

@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

ronrsnfld
Solution Sage
Solution Sage

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

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

Check it Out!

Mark your calendars and join us on Thursday, September 29 at 11a PDT for a great session with Ted Pattison!

Top Kudoed Authors