Last week, when I created a Power BI report for the Finance team, I received the data from SQL. After loading it into Power BI, I noticed that some fields had the wrong data type. For example, a few columns were loaded as text, but they should have been numbers.
To change the data type, Power BI gives us three options: using the Column Tools tab in both Report View and Table View, using DAX, or using the Power Query Editor.
In this tutorial, I will explain the different data types available in Power BI, how to change the data type using the Column Tools tab, and how to change the data type using DAX. I have also already created a separate blog post where I explain how to change the data type using the Power Query Editor in Power BI.
Power BI Data Types
In Power BI, different data types are available to help users manage and understand their data correctly. Here are the common data types:
- Decimal Number: This feature allows for numbers with fractional parts, and the number of decimal places is not fixed.
- Fixed Decimal Number: This feature allows a number with two decimal places.
- Whole Number: This feature allows only whole numbers without any decimal or fractional parts.
- Percentage: This feature allows the use of values to store as percentages.
- Date/Time: This feature allows storing both date and time information.
- Date: This feature allows the storage of only date information.
- Time: This feature allows the storage of only time information.
- Date/Time/Timezone: This feature allows you to store the date and time along with timezone information.
- Duration: This feature allows you to store the time span, such as 2 days 1 hour.
- Text: This feature allows storing words, sentences, or text information.
- True/False: This feature allows you to store boolean values, True or False.
- Binary: This feature allows the storage of binary data.
These are the data types available in Power BI to help you prepare and structure your data correctly before using it in reports and visualizations. Power BI.
Change the Data Type in Power BI
Now, I will tell you how to change the data type in Power BI.
Here, we have a SharePoint list (Financial Sample) that contains below columns with various data types:
| Columns | Data Types |
|---|---|
| Product | Single line of text |
| Country | Single line of text |
| Units Sold | Currency |
| Sales | Number |
| Profit | Currency |
| Gross Sales | Number |
| Sales Price | Currency |
| Segment | Single line of text |
Follow the steps below to change the data types.
- Open Power BI Desktop and load the data using the Get data option. Then, you see data in the Data pane.
- Then go to the table view on the left side.
- Select the column that you want to change. In my case, Units Sold is a Fixed Decimal Number, but I want to use Whole Numbers. You can see this in the Table tools tab.
- From the drop-down, click Whole Number to change the Units Sold column data type to Whole Number.
- A message appears asking whether you want to continue. Click yes to apply the change.