How to Check Data Type in Power BI

Getting your data types right in Power BI is often the difference between a report that works perfectly and one that keeps throwing errors. If Power BI thinks your revenue figures are text or your dates are just plain numbers, none of your calculations or visualizations will work as expected. This guide will walk you through exactly how to check and change data types so your reports are accurate, reliable, and error-free.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

What Are Data Types (and Why Should You Care)?

In simple terms, a data type tells Power BI what kind of data is in a column. Is it a number? A piece of text? A date? This classification is critical because it dictates how Power BI can interact with the data. You can perform mathematical calculations on numbers (like summing up sales), but you can’t sum up a list of customer names (which are text). Lining this up correctly is foundational to building anything useful in Power BI.

Getting your data types right is essential for three main reasons:

• Accurate Calculations: To get a correct total for sales, Power BI must recognize the Sales column as a number. If it’s seen as text, your SUM function will fail.
• Proper Relationships and Filtering: For creating relationships between tables (a core feature of Power BI), matching columns need to have compatible data types. Correct date types allow you to use time-based filters like "This Quarter" or "Last Month."
• Report Performance: Using the most efficient data type for your data can make your reports load and refresh faster. Storing a simple year value as a Whole Number instead of a Date/Time field, for example, makes the data model leaner.

Common Power BI Data Types

You'll frequently encounter these data types inside Power BI:

• Decimal Number: For numbers with decimal points. Ideal for things like sales figures, prices, or percentages (e.g., 19.99, 0.75).
• Fixed Decimal Number: Similar to Decimal Number but with fixed precision up to four decimal places. Typically used for financial data to avoid rounding inconsistencies.
• Whole Number: For integers without any decimal points. Perfect for counts, ID numbers, or quantities (e.g., 50, 1034, 2024).
• Text: For strings of characters, or alphanumeric data. Use this for names, product descriptions, categories, or any qualitative information.
• Date: Contains only the date part (e.g., 10/25/2024).
• Date/Time: Contains both the date and time (e.g., 10/25/2024 09:30:00 AM).
• Time: Contains only the time part (e.g., 09:30:00 AM).
• True/False: A boolean type that can only be one of two values. Great for flags like "IsActive" or "In Stock?".

How to Check Data Types in Power BI

There are two primary places in Power BI Desktop to check the data type of a column. While both work, one is definitely the preferred method for building clean, stable reports.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Method 1: The Power Query Editor (Recommended)

The Power Query Editor is the data transformation engine of Power BI. It’s where you should always check and fix any data type issues. Making changes here corrects the problem at an early stage, before the data is even loaded into your Power BI data model. This is the cleanest and most efficient approach.

Here’s the step-by-step process:

1. From the main Power BI Desktop window, go to the Home tab on the ribbon and click Transform data. This will open the Power Query Editor.
2. In the left-hand Queries pane, select the table you want to inspect.
3. Look at the column headers in the main data preview window. Next to each column graph, you'll see a small icon that represents the current data type.

These icons give you a quick visual cheat sheet:

• 123: Whole Number
• 1.2: Decimal Number
• ABC: Text
• Calendar icon: Date
• Calendar with a clock: Date/Time
• True/False: Boolean
• ABC/123: Any type (this is one you’ll want to fix!)

Seeing that your "Revenue" column has an ABC icon is an immediate red flag that you should correct.

Method 2: The Data View

The Data View (sometimes called Table View) is accessible from the main Power BI Desktop interface. It offers a quick way to inspect your data after it has been loaded from Power Query.

Here’s how to check types here:

1. In the main Power BI window, click the small table grid icon on the very left-hand navigation pane. This will take you to the Data view.
2. Select the table you want to explore from the Fields pane on the right.
3. Click on the header of the column you wish to check.
4. A new ribbon called Column tools will appear at the top. In this ribbon, you’ll see a direct "Data type" dropdown that displays the column’s current type.

While this is a fine way to quickly spot-check a data type, you should almost always return to the Power Query Editor to make any changes.

A Practical Guide to Changing Data Types

Now that you know how to find the data type, let’s go over how to change it. Unsurprisingly, the Power Query Editor is the best place for this task.

Changing Types in the Power Query Editor

Fixing an incorrect data type is simple once you’re in the right place.

1. Open the Power Query Editor (Home > Transform data).
2. Navigate to the column that needs to be corrected.
3. Click on the data type icon (e.g., the ‘ABC’ on your number column) directly in the column header.
4. A dropdown menu will appear with all available data types. Select the correct one (e.g., choose Decimal Number for your Revenue column).

Alternatively, you can select the column and use the Data Type dropdown menu available in both the Home and Transform tabs in the Power Query ribbon.

When you change a data type, Power Query adds a new "Changed Type" step to the Applied Steps pane on the right. This keeps your data transformation process organized and transparent.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

When to use "Using Locale..."

Have you ever had a date column with a format like DD/MM/YYYY (e.g., 25/10/2024) that Power BI fails to recognize? This often happens because Power BI's default settings might expect a US-based MM/DD/YYYY format. The "Using Locale..." option is the perfect fix.

Instead of just picking Date from the list, click the data type icon and select Using Locale.... This opens a new window where you can specify both the data type you want (Date) and the original format of the data (the "locale").

For a DD/MM/YYYY date, you might choose the Locale "English (United Kingdom)." This tells Power Query exactly how to interpret the source data correctly, preventing errors and ensuring all your dates are converted properly.

Common Data Type Issues and Fixes

Sometimes changing a data type doesn't go smoothly. Here are a few common hiccups and how to resolve them.

Problem: My Numbers Are Being Treated as Text

This is easily the most common issue. You load a CSV file, and your Sales or Quantity columns are left-aligned and have an ‘ABC’ icon. This often happens if there are non-numeric characters within the column - a currency symbol ($), a comma, or even an accidental space in a single cell.

The Fix:

1. Go into the Power Query Editor.
2. Before changing the data type, use the Replace Values tool (on the Transform tab) to remove any offending characters. For example, replace all instances of ‘$’ with nothing.
3. Once the column is clean of non-numeric data, you can safely change the data type to Whole Number or Decimal Number.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Problem: Power BI Shows "Error" After Changing a Data Type

If you try to convert a text column to a number type and see a bunch of cells turn into "Error," it means Power BI couldn't convert at least one of the values. This usually happens when a cell contains text that can’t be removed via simple replacement, like "N/A" or "Pending."

The Fix: You need to handle these errors. Right-click the column header and choose Replace Errors. You could, for instance, replace all errors with 0 or null, depending on what makes the most sense for your analysis. Only after cleaning up these errors will your data be ready for calculations.

Final Thoughts

Checking and setting the correct data types in Power BI is a fundamental step for creating accurate, reliable, and efficient reports. By using the Power Query Editor to manage your data types, you build a solid foundation that prevents calculation headaches and visualization woes down the line.

While mastering these data prep steps is essential for building robust reports, we know manually fixing data types can pull you away from what's really important - finding insights. At Graphed, we automate this entire process for you. We connect securely to your marketing and sales tools like Google Analytics and Salesforce, handle all the tedious data cleaning and modeling behind the scenes, and allow you to build dashboards simply by describing what you want to see - no wrestling with data types required.