Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
monojchakrab
Resolver III
Resolver III

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
Super User
Super User

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
Community Admin
Community Admin

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
Community Admin
Community Admin

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
Super User
Super User

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors