Forum Discussion
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,
LydiaLydia,
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
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?
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
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