Start preparing for your Next Exam | Use coupon – TOGETHER | Avail 30% discount

Evaluating and Transforming Column Data Types

  1. Home
  3. Evaluating and Transforming Column Data Types

As you may know, that when a table is imported from any data source, the Power BI Desktop automatically starts scanning the first 1,000 rows (default setting). Thereby it tries to detect the type of data in the columns. But there are situations when Power BI Desktop does not detect the correct data type. However, whenever incorrect data types occur, you will encounter performance issues.

Moreover, there is a higher chance of getting data type errors when you are dealing with flat files, like comma-separated values (.CSV) files and Excel workbooks (.XLSX). Since data entered manually into the worksheets could lead to mistakes. On the other hand, in databases, the data types are predefined when tables or views are created.

One of the best practice for you is to evaluate the column data types in Power Query Editor before loading the data into a Power BI data model. So, in case you discover that a data type is incorrect, you can change it. Also, you may wish to apply a format to the values in a column and change the summarization default for a column.

Effects of Incorrect Data Types

Some of the issues that can arise when Power BI does not detect the correct data type are –

  • Firstly, Incorrect data types will prevent you from creating certain calculations, deriving hierarchies, or creating proper relationships with other tables.
  • Secondly, one of the issues with having an incorrect data type applied on a date field. It is the inability to create a date hierarchy, which would allow you to analyze your data on yearly, monthly, or weekly basis. But, it is considered best to use a date table and turn off the auto date/time to get rid of the auto-generated hierarchy.

How to Change the column data type?

There are two options for you to change the data type of a column – Power Query Editor and Power BI Desktop Report view by using the column tools. Also, it is considered best to change the data type in the Power Query Editor before you load the data.

FIRST METHOD – Changing the column data type in Power Query Editor

  • Firstly, in Power Query Editor, you can change the column data type by selecting the column that has the issue. Then select Data Type in the Transform tab, and select the correct data type from the list.
  • Now, the other method is to select the data type icon next to the column header and then select the correct data type from the list.
Source: Microsoft
  • Once you make the change in Power Query Editor, in the column data type is saved as a programmed step. This step is referred to as Changed Type which will be iterated each time you refresh the data.
  • Lastly, when you have completed all steps to clean and transform your data, select Close & Apply to close Power Query Editor to apply the changes to your data model.

Once done, your data will be in well organized and arranged for analysis and reporting.

SECOND METHOD – Changing the column data type in Power BI Desktop

In Power BI Desktop, when you load data, it attempts to convert the data type of the source column. Further the data type should supports more efficient storage, calculations, and data visualization. now, in Power BI Desktop, you can ascertain and define a column’s data type in the Query Editor, or in Data View or Report View.

For more visit: Power BI Desktop Data Types

Microsoft Exam DA-100 Free Practice Test
Menu