A datatype is a classification that will identify what type odataf value a variable can have, and what kind of operations can be performed on that specific value. A datatype of a variable describes the properties and behavior of that variable, type support operations that we can perform with that variable and also the correctness and efficiency while working with that variable.
Datatypes in Power BI
In Power BI, datatypes define what kind of data or value a column can store. This helps to know what kind of data we can find in a particular column of dataset.
Based on the datatypes, Power BI understands how to display the values in each column or field and importantly, how columns interact with each other while doing calculation and transformations in Power BI. Knowing and properly defining datatypes is critical to data modeling because that will lead to appropriate data analysis and visualization.
Here is an overview of the datatypes which one shall be able to work with in Power BI.
Datatype Classification in Power BI
Power BI has 12 unique datatypes.
Number Datatypes
In Power BI, we can specify four different number datatypes.
- Whole Number – Whole number datatype is used to represent integers, e.g., 1, -5, 42.
- Decimal Number – Decimal number datatype is used to represent floating point numbers e.g. 3.14, -0.008.
- Fixed Decimal Number – Fixed decimal number datatype represents with fixed (two) decimal places. Extra decimals digits are dropped but number is not rounded e.g. 134.78, -237.25
- Percentage – Percentage datatype represents a number as a fraction of 100 e.g. 23%, 45%.
Date/Time Datatypes
In Power BI, there are 5 different date/time datatypes. Date/Time is a combination of both a date and a time. Independent formats are Date and Time. And rest of both Date/Time/Timezone and Duration convert during load into the Power BI Desktop data model.
- Date/Time – Data/Time datatype represents both a date and time value. The underlying Date/Time value is stored as a Decimal number type, e.g. 2024-07-20 12:40.
- Date – Date datatype is used to represent only the date information e.g. 2024-07-20
- Time – Time datatype is used to represent only the time information e.g 12:34:23
- Date/Time/Timezone – This datatype represents the date and time along with timezone information. e.g. 2023-09-20 13:30:45 UTC+5
- Duration – Duration datatype represents a time span or the difference between two Date/Time datatype values displayed as decimal. e.g 5 days 6 hours.
Text Datatype
As Text datatype, Alphanumeric characters are used for strings of text. This is the default datatype in Power BI. If not datatype is defined for a particular column or field, then that will be represents as Text in Power BI. e.g. “Shbytes Power BI Tutorial”.
Boolean Datatype (True/False)
Boolean datatype contains logical values that can either be True or False. e.g. True
Binary Datatype
Binary datatype represents binary format which is combination of 0(s) and 1(s) only.
- In power BI, we can use the Binary datatype to represent any data with a binary format.
- But, In the Power Query Editor, when we want to store binary data, it must be converted to another datatype before being able to import into a report. because, Binary columns aren’t supported in the Power BI data model.
Importance of Datatypes in Power BI:
- Data Interpretation – Datatypes help Power BI understand how to interpret the values in a column. This ensures correctness and accuracy of data while performing calculations and generating visualizations.
- Efficient Data Handling – Use of correct datatype optimizes the performance of Power BI models, as it reduces unnecessary memory usage and computational overhead.
- Formatting and Display – Datatypes effect the formatting of data and display in reports, ensuring that numbers, dates, and text are shown in the right format.
- Error Prevention – Incorrect datatypes can lead to calculation errors, inaccurate aggregations, and problems when creating relationships between tables.
Change Datatypes and Formats in Power BI
In Power BI, datatypes are utilized for effective categorization and manipulation of data. Sometimes we need to change the datatype of columns or fields in Power BI. Changing the datatype of a column (fields) will impact the entire dataset, queries and reports that were already generated using those columns (fields). There must be a good valid reason to change the datatype of a column (fields).
Why change the datatype in Power BI?
When we import data from different data sources in Power BI, it automatically gets internally analyzed and each column or fields are assigned with a datatype. But in actual this assigned datatype may not be useful for the intended use. For example: a number column might get assigned a Text datatype, which is not correct and will create problems in comparison, filtering of number values. OR a Percentage column is assigned as a Whole number datatype, which can lead to wrong results in calculations with that column.
In that case, we need to change the datatype of column or fields for correct data analysis and visualization in Power BI. Change in datatype can enable correct calculations, sorting, and formatting. For example this change in column datatype could be from a Text datatype to a Numeric datatype will allow to perform mathematical operations on that column OR from a Date datatype to a Date/Time datatype will allow to perform time-based analysis.
In Power BI, gaining insight on transforming datatypes will be crucial for data integrity and for good analysis and visualizations.
How to change the datatypes in Power BI?
We can change the datatypes of columns directly in Power BI Desktop by using the Power Query Editor:
While importing the data from data-source into Power Query Editor
- Click on Transform Data to open Power Query Editor.
- In Power Query Editor, select the column we want to transform (change the datatype of the column).
- In the Ribbon section, navigate to the Transform tab.
Select datatype from the “Data Type any” dropdown list.
- On select of new datatype for the column, a confirmation window will pop-up
- Confirm for the change in column datatype.
- Click “Replace current”.
- Click Close and Apply to save changes.
- Column datatype will be updated.
Data Formats in Power BI
After updating column datatype as per our requirement, we can update the column format for much better data visualization. Formatting of data also increase readability and consistency in reports. Here are some formatting options in Power BI.
Number Formats
- Decimal Places – Control the number of decimal places to display for decimal numbers.
- Thousands Separator – Format large numbers with commas or dots to separate thousands (e.g., 1,000 or 1.000).
Currency Formats
- Any numeric column can be formatted as currency to visually distinguish financial figures.
- Currency Symbols – With Currency datatype, we can select the appropriate currency symbol (e.g., $, ₹, €, ¥).
Date/Time Formats
- Format dates in various ways such as “MM/DD/YYYY“, “YYYY-MM-DD“, or even custom formats using the DAX (Data Analysis Expressions) function
FORMAT(). - Power BI allows you to display dates in both short and long date formats (e.g., “January 1, 2024” vs. “01/01/2024”).
- Time can be formatted in 12-hour or 24-hour formats (e.g., 8:00 AM or 08:00).
Text Formats
- Power BI automatically recognizes text data
- Text format can include options for uppercase, lowercase, or other transformations using DAX functions.
Conditional Formatting
Power BI allows to apply conditional formatting to data visuals, changing the background color, font color, or data bars based on the values in the data fields. Conditional formatting is available in Reports views. For example, we can highlight cells in a table with red if the values are below a certain threshold.
Summary
In this article, we learned about Datatypes and Formatting in Power BI. Following topics were discussed.