Power BI Data Types are one of the biggest differences between working with Power BI and working with data in Excel. While most people are familiar with converting text to numbers in Excel, Power BI takes it to the next level. We’ll explain the differences between the different data types in Power BI and explain why it’s important to accurately define a data type for every column.
We’ll also take a look at how Power BI can automatically detect data types for each column, and how to manually adjust them.
Let’s dive in!
Table of Contents
What are Power BI Data Types?
Data types define the kind of data that each column of a dataset contains. They tell Power BI how to present the values within a column, and more importantly they dictate how Power BI calculations and transformations interact with each column.
There are 12 unique data types in Power BI.
The following table explains each one and provides an example of what it would look like in practice.
| Data Type | Description | Example |
|---|---|---|
| Decimal Number | Allows numbers with fractional parts. The number of decimal places is not fixed. | 123.456 |
| Fixed Decimal Number | A number with two decimal places. Extra decimals are dropped and the number is not rounded. | 123.45 |
| Whole Number | Supports only whole numbers, without any decimal or fractional parts. | 123 |
| Percentage | Represents a number as a fraction of 100. | 25% |
| Date/Time | Stores both the date and time information. | 2023-09-22 13:45 |
| Date | Stores only the date information. | 2023-09-22 |
| Time | Stores only the time information. | 13:45:00 |
| Date/Time/Timezone | Stores the date and time along with timezone information. | 2023-09-22 13:45:00 UTC+5 |
| Duration | Represents a time span or the difference between two Date/Time data types displayed as a decimal. | 3 days 2 hours |
| Text | Contains alphanumeric characters, including text strings. | “Hello, World!” |
| True/False | Contains Boolean values, True or False. | True |
| Binary | Used to store binary data, must be converted to another data type before being able to import into a report. | [Binary Data] |
And there is a modify called Using Locale. This feature is used to convert the display formatting. For example, a date in the United States would commonly be presented as 12/31/2023 and 31.12.2023 in Norway. The data is technically the same but it is formatted differently.
Microsoft provides documentation that further explains the nuance of each data type.
Why Data Types are Important in Power BI
Data types are not only important for presentation of data but they determine how Power BI allows you to interact with each column. For example, you can add or apply other mathematical operations to two columns with numeric data types. You can also concatenate or combine two columns that are defined as text data types. Power BI will return an error if you attempt to apply a mathematical formula to a number column and a text column.
Another common need for data types is that dates are treated in a unique way. When calculating the difference between two dates, Power BI needs to know that columns should be treated as dates to apply time intelligence. Time intelligence is how a system knows that there are a specific number of days, weeks, and months in a year. Without this, it would return incorrect math when performing calculations across time periods.
Because each data type is treated differently, it’s important to make sure that the correct data types are defined in your data model to avoid errors and ensure that calculations are accurate.
How to Identify Column Data Types in Power BI
There are two ways that you can identify which data type a column is. The first in the report view of Power BI. The far right column is a list of all of the available columns that you can drag and drop into visualizations.
The icons to the left of each column name provide an indication of what the data type or source of the column is.
Numeric columns will have a SUM or Sigma Sign notating that they are a number.
Date columns will have a calendar next to them.
DAX measures are represented with a calculator.
The lack of an icon indicates that the column is text.
This is the first indication of the data type assigned to each column.
The second way to tell, and to get more detailed information is by using the Power Query Editor.
Right click on any column in the report view and select Edit Query to launch the Power Query Editor.
At the top of each column, there is a small icon to the left of the column name that notates the data type of each column.
In the example above, we have Attribute, Whole Number, Decimal Number, and Fixed Decimal.
Now let’s look at how to change data types in Power BI.
Automatically Detecting Data Types in Power BI
By default, Power BI will automatically detect the data types of incoming datasets. Power BI samples the data and makes its best guess as to what the data type should be in each column.
Most people won’t think about it, but it shows up under Applied Steps of the Power Query Editor after importing data.
At times, the automatically detected data types will be incorrect or it will cause an error when the titles of incoming columns change.
You can delete the automatically detect data types by clicking the X next to Changed Type.
To tell Power BI to Auto Detect Data Types, click on the Detect Data Type button from the Transform section of the Power Query Editor.
Power BI will make its best guess at to what the data type is. For the most part, it is accurate and at least a good starting point.
You can also manually adjust data types.
How to Disable Automatic Data Type Detection
If you find yourself constantly deleting columns, or renaming columns of incoming data it can cause a lot of data type errors. Or at times you prefer to have manual control over the entire data import process.
Automatic detection of data types in Power BI can be disabled by navigating to:
File > Options and Settings > Options
You can disable Detect Column Types and Headers for Unstructured Sources in the Global Data Load section.
After disabling this feature, you will need to manually tell Power BI the data type of each column or apply the Detect Type step.
How to Change Power BI Data Types
The easiest way to modify a data type in Power BI is to right click on the top of the column name in the Power Query Editor, select Change Type from the Contextual Menu, and select the new data type. Power BI will create a new applied step and the data type will be updated after you close and apply the Power Query steps.
The screenshot below shows how to Change Type by Right Clicking on a Column Name in Power Query.
The other option is to use the Data Type button after selecting a column on the Home tab of Power Query. It works the same, but we don’t think it’s as fast as right clicking.
Note: The Power BI report builder section gives you the ability to update the formatting of numbers and text. However, these formatting changes such as increasing or decreasing the number of decimals are only formatting changes. They update how Power BI presents data in visuals, but do not alter the data type.
Data type updates are normally done within the Power Query Editor.
It is possible to temporarily update or cast a different data type within DAX formulas.
Changing Multiple Data Types at One Time in Power BI
Power BI supports the ability to multi-select columns in the Power Query Editor. Users can either hold down SHIFT or CTRL while left clicking on column names. Each method works slightly differently. Holding SHIFT allows you to select a range of columns, and CTRL lets you select individual columns.
After selecting multiple columns you can right click on any of them, choose Change Type and the new desired data type.
Multi-Selecting is not supported for every function in Power Query but is available for most. Give it a try, and see how much time it will save.
This method also collapses multiple change data type applied steps into a single one.
Converting and Casting Data Types using Power BI DAX Formulas
If you don’t want to permanently update a data type in Power BI by making a change in the Power Query editor there are a few DAX formulas to be aware of. These help when temporarily changing data types for one off calculations.
- Text to Number: NewColumn = VALUE(‘Table'[TextColumn])
- Number to Text: NewColumn = FORMAT(‘Table'[NumberColumn], “General Number”)
- Text to Date: NewColumn = DATEVALUE(‘Table'[TextColumn])
Typically we recommend changing data types in Power Query as it’s more efficient and happens during the data load instead of when a user interacts with a visualization or slicer but these should help in a pinch.
Conclusion
Data types are an essential part of Power BI. They are also a staple of modern data manipulation. They’re a big part of SQL databases and most modern systems will requirement that each column has a defined type. Luckily, Power BI makes it easy to auto detect columns as a starting point, and also makes it easy to right click on a column name and adjust the column data type manually.