cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Charley Frequent Visitor
Frequent Visitor

Needless Table.TransformColumnTypes

The Query Editor seems to add needless Table.TransformColumnTypes at every opportunity. So far, I've deleted those steps until Table.TransformColumnTypes actually is needed. I've been particularly careful to do this in functions, which could be called thousands of times.

 

In other programs, we often can improve processing time by deleting unneeded code. But with query folding in M, I'm wondering whether it's worth the hassle to do that.

 

Will a plethora of unneeded Table.TransformColumnTypes steps slow processing to any degree?

 

Thanks.

 

Charley

 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Moderator v-yuezhe-msft
Moderator

Re: Needless Table.TransformColumnTypes

@Charley

I don't think the Table.TransformColumn Types step has huge impact on data processing performance.

As per this official article, when you load data into Power BI Desktop, it will attempt to convert the data type of the source column into a data type that better supports more efficient storage, calculations, and data visualization. 

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Charley Frequent Visitor
Frequent Visitor

Re: Needless Table.TransformColumnTypes

Lydia,

 

I should have been clearer. The question was about Power Query and M, not about Power BI and DAX. But under the hood, I assume, the two environments probably use the same process.

 

I've recently decided to delete irrelevant Table.TransformColumnTypes steps--particularly from functions--for two reasons.

 

First, I prefer to clean and trim data before I transform the column types. This eliminates the possibility that extraneous characters with values having an Any format will be turned into errors when they're turned into numerical values. (I'd rather perform unnecessary clean and trim steps than take the chance that numerical values will transform as errors.)

 

Second, eliminating multiple data transformations leaves me fewer steps of M code to manage, and it might speed up processing.

 

With regard to the Power BI advice, it doesn't seem to apply. Because I transform column types ONCE near the end of the query, Storage, calculation, and visualization all use the correct data types.

 

Thanks for your comments!

 

Charley

View solution in original post

4 REPLIES 4
Super User IV
Super User IV

Re: Needless Table.TransformColumnTypes

What order of magnitude of Table.TransformColumnTypes are we talking about here?

 

I can't say that I have ever noticed a difference really but haven't done extensive testing on that front.


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Charley Frequent Visitor
Frequent Visitor

Re: Needless Table.TransformColumnTypes

I've never compared the two options. Perhaps it just falls under the general heading of good housekeeping. If your program includes unneeded lines of code, remove them. That makes your code less cluttered and might even improve performance.

 

Am I the only one who removes all Table.TransformColumnTypes until they're actually needed?

Moderator v-yuezhe-msft
Moderator

Re: Needless Table.TransformColumnTypes

@Charley

I don't think the Table.TransformColumn Types step has huge impact on data processing performance.

As per this official article, when you load data into Power BI Desktop, it will attempt to convert the data type of the source column into a data type that better supports more efficient storage, calculations, and data visualization. 

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Charley Frequent Visitor
Frequent Visitor

Re: Needless Table.TransformColumnTypes

Lydia,

 

I should have been clearer. The question was about Power Query and M, not about Power BI and DAX. But under the hood, I assume, the two environments probably use the same process.

 

I've recently decided to delete irrelevant Table.TransformColumnTypes steps--particularly from functions--for two reasons.

 

First, I prefer to clean and trim data before I transform the column types. This eliminates the possibility that extraneous characters with values having an Any format will be turned into errors when they're turned into numerical values. (I'd rather perform unnecessary clean and trim steps than take the chance that numerical values will transform as errors.)

 

Second, eliminating multiple data transformations leaves me fewer steps of M code to manage, and it might speed up processing.

 

With regard to the Power BI advice, it doesn't seem to apply. Because I transform column types ONCE near the end of the query, Storage, calculation, and visualization all use the correct data types.

 

Thanks for your comments!

 

Charley

View solution in original post

Helpful resources

Announcements
Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors