In this blog post, we delve into the world of data types in Power BI and Excel, their importance, and how to manage them effectively.
Note
screenshots of Power Query in this blog are from inside Power BI, but the user interface in Excel is similar.
Data analysis and visualization are essential aspects of modern business intelligence, and Power BI has become a popular tool for handling these tasks efficiently. One critical factor that significantly impacts the performance and accuracy of your Power BI reports is the data types assigned to your columns in Power BI or Excel.
Why Data Types Matter in Power BI
The correct data types in Power BI and Excel are crucial for proper analysis and visualization. When you load data into Power BI Desktop, the software automatically attempts to convert the source data into the most efficient data types for storage, calculations, and visualizations. Using the right data type not only ensures better performance but is also crucial for accurate calculations using Data Analysis Expressions (DAX) later in your report.
Let’s understand the importance of data types in Power BI and Excel with an example. Suppose you have a date column, but Power BI has assigned it a text data type. If you try to create a DAX measure that requires a date data type, it will not calculate properly. Correct data types are also essential for optimal report performance. For instance, if you assign a decimal data type to a column that only contains whole numbers, it will use more storage space unnecessarily.
Exploring Data Types in Power BI
Power Query is extremely flexible in the number of data types it can handle.
Decimal Number
The most common number type, and can handle numbers with fractional values and whole numbers.
Percentage
Represents Decimal Number, which is displayed in the Percentages format. Range from 0 to 1.
Whole Number
Represents a 64-bit (eight-byte) integer value. Because it's an integer, it has no digits to the right of the decimal.
Date/Time
Represents both a date and time value. The underlying value can be converted between the two types.
Text
A Unicode character data string, which can be letters, numbers, or dates represented in a text format.
Time
Represents just a time with no date portion. A Time converts into the model as a Date/Time value.
There is a wide variety of data types in Power BI and Excel beyond the ones presented above, including Binary and True/False data types. For a comprehensive list of data types in Power BI and how to use them effectively, check out this article from the Microsoft Power BI Documentation.
When you load your data into Power BI, you can easily see the data types assigned to each column. Power BI displays small icons at the top left corner of each column in the Power Query Editor, indicating the data type. By clicking on these icons, you can explore and understand the assigned data type.
Changing Data Types in Power BI
It’s essential to ensure that Power BI has correctly assigned data types to each column. If needed, you can easily change the data types in a few simple steps.
Method 1: Changing Data Types from Data Preview
In the Power Query Editor, go to the Data Preview section.
Click on the small icon at the top left corner of the column whose data type you want to change.
A list of available data types will appear. Choose the appropriate data type, such as changing a decimal to a whole number.
Power BI will create a new “Changed Type” step in the Applied Steps section, indicating the data type conversion.
Need help getting started with Power BI?
Our Introduction to Power BI webinar covers several topics from connecting and transforming data, to creating stunning visualizations and sharing them across your organization.
Method 2: Changing Data Types from the Home Tab
While in the Power Query editor, navigate to the Home tab.
Select the column whose data type you want to change by clicking on the column header. The column header for the selected column should be highlighted with a different colour from the other columns.
Look for the Data Type option under the Transform section.
Choose the desired data type and Power BI will add a new step for the data type conversion.
Method 3: Detecting Data Types
In some cases, Power BI might not automatically detect the correct data type for a column. In this case, you will see an “Undefined Data Type” icon – which will display as ABC 123 – next to the column name. You can use the Detect Data Type option to help Power BI understand the data.
While in the Power Query Editor, navigate to the Transform tab.
Select the column with an undefined data type (shown by the “Undefined Data Type” icon).
Click on Detect Data Type button from the Transform tab.
Power BI will automatically identify and assign the correct data type to the column.
The Wrap Up
Data types play a crucial role in Power BI’s performance, accuracy, and report creation. Ensuring that the correct data types are assigned to each column in your datasets will lead to better overall performance and accurate calculations. Power BI provides multiple ways to change data types, giving users the flexibility to manage their data effectively.
Remember to review and validate data types before loading your data into Power BI to avoid any unexpected issues in your analysis and visualization journey.
Need Help Getting Started With Power BI?
Our Microsoft Certified consultants can help with the implementation of Power BI in your organization
![[How To] Append Queries in Power Query (Power BI & Excel)](https://goanalyticsbi.com/wp-content/uploads/2023/10/Appending-Queries-300x157.png)
![[How To] Import Data from SQL Server into Power BI](https://goanalyticsbi.com/wp-content/uploads/2023/01/Blog-Feature-Importing-data-from-SQL-Server-to-Power-BI-300x157.png)