Needless Table.TransformColumnTypes

[Charley]

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

[v-yuezhe-msft · Accepted solution]

@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

[Greg_Deckler]

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.

[Charley]

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?

[v-yuezhe-msft · Accepted solution]

@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

[Charley · Accepted solution]

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