Power BI data types: choose the right type for every column
Power BI data types decide how each column is stored, calculated and displayed. When Power BI loads data, it tries to convert each source column to a type that suits storage and calculation. That guess is often right, but not always. This guide explains the main types, where you set them and the pitfalls that Microsoft documents for numbers, text and true or false values.
- Three number types
- Where to set a type
- Text and case pitfalls
Last updated
Why do Power BI data types matter?
A data type is more than a label. Microsoft explains that Power BI converts source columns into types that support efficient storage, calculations and data visualization. For example, an Excel column without fractional values becomes a whole number.
The type also affects formulas. Some DAX functions have special requirements. In many cases DAX converts a value implicitly, but in some cases it does not. If a function requires a date and the column holds text, the function will not work correctly.
So check the types early. A wrong type can lead to errors in measures, odd sort orders and relationships that fail to match. Fixing the type at the start costs far less effort than tracing a wrong total later.
The main Power BI data types at a glance
Microsoft's article describes these types, and the notes summarize what it says about each one.
| Type | What it holds | Documented note |
|---|---|---|
| Decimal number | Numbers with or without fractions | Floating point, up to 15 digits of precision |
| Fixed decimal number | Numbers with four decimal places | Useful where rounding might introduce errors |
| Whole number | Integers | No digits to the right of the decimal place |
| Date/time | A date and a time value | Supports years from 1900 to 9999 |
| Date | A date without a time | Stored as a date/time with a zero fraction |
| Text | Letters, numbers or dates as text | Practical limit near 32,000 characters |
| True/false | A Boolean value | Set Boolean columns to this type |
Set a column type in four steps
Microsoft documents more than one place to set a type, and these steps cover the usual route.
Open Power Query Editor
First open the query that loads the table. Then select the column you want to change.
Choose the type
Next select Data Type in the Transform group of the ribbon and pick the correct type from the list.
Check the model
Then load the data. In Table view or Report view, select the column and look at Data type on the Column tools tab.
Test a measure
Finally build a quick visual with the column. Confirm that totals, sorting and date functions behave as you expect.
Which number type should you choose?
Power BI supports three number types. Decimal number is the most common one, and it handles both fractions and whole values. Microsoft notes that it corresponds to how Excel stores its numbers.
However, decimal numbers are approximate. They follow the standard for floating point numbers, so tiny rounding differences can appear. As a result, a comparison that tests whether two such values are equal can return an unexpected answer.
Fixed decimal number avoids this problem for money. The decimal separator always has four digits to its right, and values past those digits are cut off. Whole number also helps when you need to control rounding. Therefore use fixed decimal for currency amounts and whole number for counts and keys.
How do Power BI data types treat text and case?
Text holds a surprise. Microsoft explains that the engine that stores and queries data in Power BI is case insensitive. It treats an uppercase letter and its lowercase form as the same value. Power Query, however, is case sensitive.
That difference can change how names look after a load. The engine keeps a dictionary of unique values for each text column. When a later row differs only by case, the engine refers to the first spelling it stored. So the capitalization in a report may not match the source.
Take extra care in DirectQuery mode with a case-sensitive source. Power BI treats two values that differ only by case as duplicates, whereas the source might not. Microsoft states that the final result is undefined in such cases.
Three conversions that happen on load
Some types exist in Power Query Editor but not in the model, so Power BI converts them, according to Microsoft.
Date/Time/Timezone
This type appears only in Power Query Editor. When the column loads into the model, it becomes a date/time column.
Duration
Duration is also limited to Power Query Editor. On load, such a column converts into a decimal number.
Binary
Binary is not supported outside Power Query Editor. Therefore convert binary columns to another type before you load them.
Power BI data types: common questions
Where do I change Power BI data types?
Microsoft names three places. In Power Query Editor, select the column and use Data Type in the Transform group. In Table view or Report view, select the column and use the Data type dropdown on the Column tools tab. You can also set types in M code through the Advanced Editor.
Why does my text change capitalization after loading?
Because the Power BI engine is case insensitive, while Power Query is case sensitive. The engine stores each unique text value once and treats a different capitalization as the same value. So later rows show the spelling that loaded first. Clean the text in Power Query if consistent capitalization matters.
Which of the Power BI data types suits currency?
Fixed decimal number is the documented choice when rounding might introduce errors. It keeps exactly four digits to the right of the decimal separator. Microsoft notes that it matches the Currency type in Analysis Services. A plain decimal number works too, but it stores approximate values.
Does DAX convert types automatically?
Often, but not always. Microsoft states that DAX attempts an implicit conversion wherever possible, and it returns an error when the data is incompatible. For example, DAX can add a number stored as text to a real number. For comparisons, however, it does no implicit conversion for Boolean or string values.
Can Power BI Agency review the column types in our model?
Yes. One of our consultants checks each column against its use in measures, relationships and visuals, then corrects the types at the right step. We work online inside your own Microsoft tenant and start with a free 30-minute assessment. Power BI Agency is a division of Sataware Technologies, independent of Microsoft.
Let's build your first dashboard together.
Tell us what you report on today. A consultant replies within 2 hours with next steps.
Trusted partners
Microsoft Solutions Partner · AWS Partner
Cloud & AI Platforms
Azure SQL, Microsoft Fabric and the data pipelines behind your reports.
AI Business Solutions
Power BI reporting on your Dynamics 365, Business Central and Power Platform data.
Security
Row-level security and Entra ID sign-in on every report and workspace.
Amazon Web Services
Power BI on your AWS data, with connectors for Redshift, Athena and S3.