by Eric Marshall

Assigning Data Types

Assigning data types is an essential step when working with Power Query in Excel or Transform Data in Power BI Desktop. Here are some useful tips on how to change data types while maximising the efficiency of your models:

Tip #1: Don’t Change Types Until the End of a Query

• A best practice when working with Power Query is to delay changing column types until the very last step of a query.

• Each time data types are changed, Power Query hard-codes one or more column names into the M code. This can cause your query to break if the columns in your changed type step are removed or renamed outside of Power Query or in a later step.
• Pushing this step to the end of your query thus makes it easier to debug your query later if columns are renamed or removed.

Tip #2: Change DateTime data to the Date Type

• When working with datetime data, consider whether your report needs to analyse data at the time level.
• If you don’t need to analyse data at the time level, change your datetime columns to the date data type. This reduces the amount of information stored in your date columns, which reduces the size of your model and makes it run more efficiently.
• If you do need to analyse data at the time level, split your datetime column into a date column and a time column to store the data more efficiently.

Tip #3: Turn Off Automatically Detect Column Types

• By default, Power Query automatically adds query steps that promote headers and change data types in your query after certain transformations are performed.
• You can modify the Power Query options settings to prevent this from happening. In Excel’s Power Query window, go to File/Options & Settings/Query Options/Current Workbook/Data Load and turn off “Automatically detect column types and headers for unstructured sources”:

• In Power BI, go to File/Options & Settings/Options/Current File/Data Load and turn off “Automatically detect column types and headers for unstructured sources”:

Want to learn more? Jump on to one of our courses

We’ve trained over 1,000 people from all types of organisations and departments in Australia and all around the world. See what attendees are saying about our courses: