Generally, when you load a table from any data source, Desktop automatically detects the data type of a column. However, there may be some situations where it might get them wrong. In such cases, use the Power BI Edit Query option to change the data type of a column.
For example, it may consider amounts, values, or even dates as the text. In these situations, you can use Power BI to change the data types of a column option. Let me show you how to Change the Data Types of a Column with an example.
How to Change Data Types of a Column in Power BI
To demonstrate this Changing the Data Types of a Column in Power BI, we are going to use the Excel table that we imported in the Enter Data article. To change the data types of any column, please click the Edit Queries option under the Home tab.
Clicking the Edit Queries option opens a new window called Power BI Power Query Editor. From the screenshot below, you can see the Yearly Income, Sales, and HireDate columns loaded as the text.
There are a couple of ways to change data types of columns
First Approach to Change Data Types of a Column
Please select the Column for which you want to change the data type. Next, click on the left corner of the column header (currently it represents ABC text). Clicking in that position opens a drop-down list of supported data types. Please select the data type that suits your data. Here, we are selecting the Whole number.
Changing the data type of a column opens the pop-up window, Please choose between Replace Current, Add New Step, and Cancel. For now, click on the Replace Current button. Now you can see the Yearly Income header is displaying 123 (whole number), not ABC (text).
Second Approach to Change Data Types of a Column in Power BI
Please select the Column name that you want to alter the data type, and click on the Data Type button under the Home tab. It shows you the drop-down list of available data types. For the demo purpose, we are selecting a Fixed Decimal Number.
It opens a new pop-up window with three buttons: Replace Current, Add New Step, and Cancel. Please Choose either Add as a New Step or Replace Current option. This time we will select the Add New Step. It means this change column type is added as a new step under the Applied Steps.
As you can see from the screenshot below, it changed the data type of the Sales column and added a new step under the Applied Steps.
Third Approach to Change Data Types of a Column in Power BI
Please select the column that you want to change the data type, and right-click on it will open the context menu. Select the Change Type and then the data type from the list. For now, we are selecting the Whole Number.
Don’t forget to hit the Close & Apply option under the Home tab to apply these changes.