Understanding Data Types in Power BI
Data types are the fundamental building blocks of any dataset in Power BI. They define the nature of the information contained within each column, facilitating data organization and analysis. Power BI supports a range of data types, each designed for a specific category of data. Let’s delve into these data types and understand their roles:
DATATYPE |
DESCRIPTION |
EXAMPLE |
---|---|---|
Decimal number |
Decimal data types are essential for storing numbers with decimal points. They are indispensable for measurements, financial figures, and other precise calculations. |
To calculate total charges of a product including tax (as a percentage), the numbers are usually not whole numbers. |
Fixed decimal number |
The decimal separator is fixed in place for Fixed Decimal Number data types. There are always four digits to the right of the decimal separator, allowing for 19 significant digits. |
Lets say we want to list the heights of all the students in a classroom. 2 decimal points of accuracy shall be enough. |
Whole number |
These data types store non-decimal numbers and are ideal for counting or indexing purposes. |
Think of product IDs, employee numbers, or serial numbers. |
Percentage |
Whether you’re analyzing financial statements or performing statistical calculations, the Percentage data type shines. It allows you to represent values as percentages, simplifying complex data analysis. |
We use percentage in marks obtained in a test, composition of a product, relative humidity, etc. |
Date/Time |
A Date/Time value is a combination of both a date and a time, and it’s internally represented as a Decimal number. This representation allows for seamless conversion between the two formats. The time component is stored as a fraction of 1/300 seconds (equivalent to 3.33 milliseconds) or as multiples of this fraction. This data type is designed to accommodate dates ranging from the year 1900 to the year 9999. |
For tracking and visualizing events or transactions with both date and time components, like order timestamps. |
Date |
Date exclusively denotes a specific date without any associated time information. When represented within the model, it appears as a Date/Time value, but in this context, the fractional part is consistently set to zero, signifying the absence of any time component. |
Used for representing calendar dates without time information, suitable for date-based analysis like daily sales. |
Time |
Time solely conveys a time of day without any reference to a specific date. When it’s converted within the model, it assumes the form of a Date/Time value, and in this particular case, the part of the value to the left of the decimal point is consistently absent, emphasizing its exclusive focus on the time component. |
Applied when you need to work with times of day independent of specific dates, like measuring response times. |
Date/Time/Timezone |
Date/Time/Timezone signifies a UTC-based date and time along with a corresponding timezone offset. When this information is loaded into the model, it transforms into a Date/Time format. It’s important to note that the Power BI model remains consistent in displaying time regardless of a user’s geographical location or regional settings. For instance, a value like 09:00 entered into the model in the India will appear as 09:00 no matter where the report is accessed or viewed. |
Useful for working with date and time data that includes timezone information, valuable for international reporting. |
Duration |
Duration indicates a specific length of time, and upon import into the model, it transforms into a Decimal Number type. In this numerical format, it becomes versatile, enabling you to perform addition and subtraction operations with Date/Time values, yielding accurate results. Additionally, Decimal Number representations of durations are conducive to being incorporated into visualizations that emphasize magnitude, providing a convenient way to showcase time-related data. |
Appropriate for measuring the length of time intervals, like time spent on tasks or project durations. |
Text |
The Text data type is the repository for textual information, accommodating letters, numbers, and special characters. In Power BI, it’s commonly used for labels, descriptions, and textual data. The practical maximum limit for string length is approximately 32,000 Unicode characters, based on Power BI’s underlying Power Query engine, and its limits on text data type lengths. |
Whether you’re working with product names, customer addresses, or employee names, the Text data type is your go-to choice. |
True/False (Boolean) |
Boolean data types can have one of two values: True or False. They’re the foundation of conditional expressions and allow you to filter data based on specific conditions. |
For representing binary, yes/no, or true/false data, often used in filtering or flagging records. |
Binary |
The Binary data type is used to store binary data, such as images, documents, or any non-textual information. While it may not be suitable for direct analysis, it serves as a storage mechanism for non-text data and is often used for purposes like document management and retrieval. |
Utilized for storing binary data, like images, documents, or other non-text data in a binary format. |
Data Type Detection in Query Editor
- When you load data into Power BI, you can access the Query Editor by selecting “Edit Queries” during the data import process.
- Inside the Query Editor, you can see the data types assigned to each column. Power BI typically tries to detect the data type automatically, but you can modify it if needed.
Data Type Icons in Data View
- In the Data View section of Power BI, columns are represented with icons that reflect their data types.
- For example, a calendar icon represents Date data, a clock icon represents Time data, a text icon represents Text data, and so on. These icons make it easy to identify the data type at a glance.
Column Information in Fields Pane
- In the Fields pane, you can see the list of fields (columns) used in your report.
- Hovering over a field name will display a tooltip with additional information, including the data type.
Data Type Formatting in the Modeling Tab
- When you select a column in the Fields pane and navigate to the Modeling tab, you can format the data type explicitly. This is especially useful when you want to change the detected data type to something more suitable for your analysis.
Data Type Functions and Transformations
- Power BI provides functions in DAX (Data Analysis Expressions) and Power Query for data type transformations. You can use these functions to explicitly convert or work with data types in calculations and transformations.
Data Profiling
- In Power BI’s Query Editor, you can use the “Profile” feature to get insights into the distribution of data within a column, which can help you verify and understand data types.
Power BI Data Types
Data is the lifeblood of modern business, and harnessing its potential is crucial for informed decision-making. Microsoft Power BI is a powerful business intelligence tool that empowers organizations to turn raw data into actionable insights. To make this transformation, Power BI employs a variety of data types, each serving a unique purpose in the world of data analysis and visualization.