What are Data Types in Power BI?
In Power BI, data types refer to the classification of values or columns in a dataset based on the kind of data they represent. Power BI automatically assigns data types to columns when you load or import data into the Power BI Desktop. Properly defining data types is crucial for accurate data analysis and visualization. Some common data types in Power BI include Text, Whole Number, Decimal Number, Date/Time, Boolean, Currency, Percentage, Duration, Whole Number/Fixed Decimal Number, and Binary.
Table of contents
- Data types in Power BI are the classification of values in a dataset based on the data they represent. Power BI usually infers data types but may require manual adjustments.
- Power BI automatically assigns data types to columns when you load or import data into the Power BI Desktop.
- Maintain consistent data types for accurate analysis.
- Use Power Query Editor or Report View for data type adjustments and cleaning.
- Data Profiling: Useful for understanding data types and quality before analysis.
How to Add Data Types in Power BI?
Power BI generally infers data types automatically. However, you can manually set data types during the data loading process. You can add data types in one of the following ways
- Data Types in Power BI Query Editor
- Using the Data or Report View in Power BI Desktop
Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)
If you want to learn Excel and VBA professionally, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.
Using Power BI Query Editor
To add data types in the Power BI Query Editor, choose the column and select Data Type under the Transform tab, as shown below. Choose the data type as appropriate for your underlying datasets/columns.
Using the Data or Report View in Power BI Desktop
To add data types using the data or report View in Power BI Desktop, choose the column, and select the data type from the drop-down menu for the Data type section under the Column tools tab of the ribbon.
Let’s look at each of the data types in Power BI using one of the ways highlighted above.
#1 – Number Types
You can specify three different number types in Power BI Desktop, which include Decimal number, Fixed decimal number, and Whole number.
|• Can handle numbers with fractional values and whole numbers.
• It supports 64-bit floating point numbers and both positive/negative numbers
• It can represent precisions up to 15 digits
|Fixed decimal number
|• Has a fixed location for a decimal separator that allows four digits to the right and 19 digits of significance
• You can specify both positive and negative values, with the maximum being 922,337,203,685,477.5807
|• It represents a 64-bit integer value and has no digits to the right of the decimal place
• You can specify 19 digits of positive and negative whole numbers with the largest number (2^63 – 1)
To add a number type, choose Data type as one of the options, i.e., Whole number, Decimal number, or Fixed decimal number, from the drop-down menu in Data View or Report View.
Alternatively, you can also add data types in Power BI Query Editor by choosing from the dropdown menu.
#2 – Date/Time Type
You can add five different Date/Time types in Power BI Desktop which include Date/Time, Date, Time, Date/Time/Timezone, and Duration.
|• Represents both date and time values
• Supports date between 1900 and 9999 and time in milliseconds (3.33 ms)
|Represents only date with no time values
|Represents only time with no date
|This data type represents a UTC date/time with a timezone offset
|Represents a duration or length of time and stores as a decimal number format
To add a Date/Time type, choose Data type as one of the options, i.e., Date/time, Date, or Time, from the drop-down menu in Data View or Report View.
Note that Power Query Editor supports additional data types such as Date/Time/Timezone and Duration compared to Power BI Desktop Data or Report View.
#3 – Text Data Type
Text data type is a unique data type in Power BI. It is a Unicode character data string and supports multiple formats, such as letters, numbers, or even dates specified in a text format.
The maximum string length limit for string supported is 32000 Unicode characters. The text data type is case-sensitive and supports multiple functions that can provide flexibility to perform any data operations.
To add a Text data type, choose Data type as Text from the drop-down menu in Data View or Report View.
Alternatively, choose Text from the drop-down menu.
#4 – True/False Data Type
This data type represents a Boolean value of True or False of an expression. Power BI evaluates the expression and automatically sets the value as True or False as per the evaluation.
To add a True/False data type, choose Data type from the drop-down menu in Data View or Report View.
Alternatively, you can add all data types in Power BI Query Editor by choosing from the dropdown menu.
#5 – Blank/Null Data Type
Blank or Null data type is primarily a data type in Power BI DAX (Data Analysis Expression). By using DAX functions such as BLANK you can create blank values. Similarly, you can validate the blank values by using the ISBLANK function.
Now, let’s look at some of the examples of the data types.
Example #1 – Identify Column Data Types in Power BI
In this example, we will demonstrate how to identify the column data types in Power BI. For this demo purpose, we will use the Global Salaries in Cybersecurity dataset which comprises salaries from the infosec-jobs.com salary survey.
To identify the column data types, follow the steps outlined below:
Step 1: Load the Global Salaries in Cybersecurity dataset to Power BI using the Get data option.
Step 2: Once the data is loaded, navigate to the Fields pane to view the loaded dataset.
Step 3: Select any of the columns from the Fields pane. This will navigate you to the Column tools tab. You will see the data type of the selected column under the Data type header.
Similarly, you can also identify the column by navigating to Power Query Editor and selecting the column.
Example #2 – Automatically Detect Data Types in Power BI
In this example, we will demonstrate how to automatically detect the data types in Power BI. For this demo purpose, we will use Yale College Admissions statistics between 1986 and 2022.
To automatically detect data types, follow the steps highlighted below:
Step 1: Load the Yale College Admissions dataset to the Power BI and click on Transform Data.
It will take you to the Queries tab in the Power Query Editor window.
Step 2: Navigate to the Transform tab and choose the Detect Data Type option from the ribbon. It will ensure that Power BI automatically detects the data type for all the fields for that table and assigns the relevant values.
Example #3 – Disable Automatic Data Type Detection
Here, we will show how to disable automatic data type detection using the same example as Example 2.
Step 1: Open Power BI Desktop and select the dataset to load in Power BI using the Get data option.
Step 2: Click on the Data Type Detection drop-down, choose the Do not detect data types option, and click on Load.
This will disable the automatic data type detection in Power BI.
How to Change Power BI Data Types?
To change the Power BI Data Types, follow the steps below
Step 1: Change the Power BI Data Types using the Power Query Editor
- Select the table for which you want to change the data types.
- Right-click on the table and click on the Edit Query option.
- In the Power Query Editor window, choose the fields for which you want to change the data types and select the target data type you want to change by choosing the Data Type option from the drop-down menu under the Transform tab.
- Repeat it for all the columns you want to change the data type.
- Click on Close & Apply in the Home tab.
Step 2: Change the Power BI Data Types using Power BI Data or Report View
- Select the field you want to change the data type.
- Choose the target data type you want to change by choosing the Data type option from the drop-down menu under the Column tools tab.
Changing Multiple Data Types at One Time in Power BI
If you want to change data types for all columns in a table, you can do so by selecting the table and using the Detect Data Type option in Power Query Editor.
To change the multiple data types once in Power BI, follow the steps below:
Step 1: Select the data to be loaded to the Power BI, and click on the Transform Data option to open the dataset in the Power Query Editor tool. Alternatively, if there is any existing dataset in Power BI, you can use the Edit Query option in the Fields pane.
Step 2: In the Power Query Editor, choose the table and click on the Detect Data Type option under the Transform tab.
Power BI will automatically attempt to detect and set appropriate data types for each column.
Step 3: Review the detected data types, and if needed, manually adjust any columns by selecting them and choosing the correct data type from the Data Type drop-down menu.
Step 4: Resolve any warnings or errors that may occur during the data type detection and conversion process.
Step 5: Click on Close & Apply to apply the changes.
Important Things to Note
- Use the Data Profiling option to analyze data types before loading.
- Incorrect data types may affect calculations and visualizations.
- Most data type changes can be made in the Power Query Editor or Report View in Power BI Desktop
Frequently Asked Questions (FAQs)
In Power BI, you can concatenate different data types using various functions and operators. The approach may vary based on the specific requirements and the types of data you are working with. Some of these functions include & operator, CONCATENATE, or CONCATENATEX functions.
Setting the correct data types in Power BI is important for multiple reasons:
• Valid data types in Power BI are necessary to ensure that the data is interpreted and processed accurately.
• Power BI can optimize queries and calculations based on data types, leading to better performance. Using the correct data type allows for more efficient storage and processing of data.
• The correct data type enables proper filtering and sorting of data. If a column is treated as text when it should be numeric, the sorting may not be accurate, and filtering may not work as expected.
• The visualization components in Power BI, such as charts and graphs, use data types to display information accurately. Using the wrong data type may result in misleading visualizations.
If Power BI encounters unrecognized data types, it may automatically assign a default data type, usually “Text.” It’s important to review and assign valid data types in Power BI to ensure accurate analysis and reporting.
Power BI doesn’t have a feature for creating custom data. However, it does allow you to define custom calculations and columns using the Data Analysis Expressions (DAX) language. You can create calculated columns or measures using DAX to derive values based on your specific requirements.
It is recommended that you always refer to the latest Power BI documentation or community forums for any updates or new features.
This has been a guide to Data Types in Power BI. Here we learn how to add, change different data types, with examples and points to remember. You can learn more from the following articles –