Back to Blog

Power BI Desktop Data Types - What Actually Matters and What Bites You Later

October 9, 2026•10 min read•Michael Ridland

A finance manager in Brisbane once sent us a screenshot of two Power BI reports that disagreed on the quarterly revenue figure by 3 cents. Three cents across about $41 million. Their CFO saw it, lost trust in the whole dashboard program, and asked for everything to go back to Excel.

The cause was a data type. One report summed a column stored as Decimal Number, the other used Fixed Decimal Number, and floating point arithmetic did what floating point arithmetic does. Nothing was "wrong" in the sense of a bug. Somebody just picked the default type without thinking about it, and the default is not always the right answer.

Data types are the least exciting part of Power BI. Nobody gets promoted for choosing Whole Number over Text on a postcode column. But nearly every model we inherit from another team has type problems somewhere, and they show up as wrong numbers, slow refreshes, broken relationships and confused report users. So here's how I think about them, based on what we've seen across a lot of client models.

Microsoft's reference is the Data types in Power BI Desktop article. It's worth bookmarking. What follows is the opinionated version.

Two places types live, and why that confuses people

The first thing to understand is that there are really two type systems in play.

Power Query (the "Transform data" window) has its own types. When you click the little icon in a column header and pick a type, you're setting the Power Query type. Then when data loads into the model, it gets converted to the model's types, which are what DAX works with.

Mostly these line up. Sometimes they don't. A few examples:

  • Percentage in Power Query becomes a Fixed Decimal Number in the model, with a percentage format applied
  • Date/Time/Timezone gets converted to Date/Time on load, and the timezone offset is applied and then thrown away
  • Duration becomes a Decimal Number representing days
  • Binary columns don't load into the model as usable data at all

The Duration one catches people. You carefully calculate "time on site" as a duration in Power Query, load it, and get 0.0416666 in your table visual. That's one hour expressed as a fraction of a day. Correct, technically. Not what anyone wanted to see. If you need durations in a report, I'd usually convert to total minutes or seconds as a Whole Number in Power Query and format it in DAX.

The Timezone one is more dangerous, and I'll come back to it.

Decimal Number vs Fixed Decimal Number

This is the one I'd tell every new Power BI developer about on day one.

Decimal Number is a 64-bit floating point number. It handles a huge range of values and is the default Power Query picks when it sees numbers with decimal places. It's also subject to the classic floating point rounding issues, where values that look exact in decimal can't be represented exactly in binary.

Fixed Decimal Number (it shows up as "Currency" in some places in the UI) stores values with exactly four decimal places of precision. Under the hood it's an integer scaled by 10,000. It doesn't have the floating point rounding problem, and it's fine for any monetary amount you'll realistically see in an Australian business.

My rule is simple. If it's money, use Fixed Decimal. Revenue, cost, GST, invoice amounts, budget figures. All of it.

The four decimal places limit means it's not suitable for everything. Exchange rates sometimes need more precision. Scientific or engineering measurements might too. Mining clients with assay grades in parts per million are a good example where we'd stick with Decimal Number. But for dollars, Fixed Decimal avoids exactly the problem that cost our Brisbane client their CFO's trust.

Whole Number and the "is this actually a number" question

Whole Number is a 64-bit integer. It's the most efficient type in the model for compression and for relationships. If you have surrogate keys, they should be Whole Numbers.

The bigger question is when a column that looks like a number shouldn't be one.

Australian postcodes are the classic. 0800 is Darwin. Load it as Whole Number and it becomes 800, which is not a postcode. Northern Territory and some ACT postcodes start with a zero, and if your sales data is national, you'll quietly lose them. Postcodes should be Text.

Same goes for ABNs (sometimes stored with spaces, and you'll never do arithmetic on them), phone numbers, BSB numbers, and most "codes" from ERP systems. If you'd never sum it or average it, think hard about whether it should be Text. Power BI will happily sum a column of ABNs if you let it, and you'll see someone drag one into a visual and get a Sum aggregation that makes no sense.

You can set the default summarisation to "Don't summarise" to stop that, and you should, but getting the type right in the first place is better.

Dates, and the Australian locale problem

Date handling is where I've seen the most hours burned.

Power BI stores dates internally as numbers: days since 30 December 1899, with the time portion as a fraction. That's why you can do arithmetic on dates in DAX. It also means the model doesn't support dates before 1 March 1900 properly. That rarely matters for business data, though we did hit it once with a heritage property register.

The real problem for Australian organisations is locale. A CSV with "03/04/2026" means 3 April to us and 4 March to a system that assumes US formatting. When Power Query auto-detects types, it uses the locale settings of the file or the Power BI Desktop instance. If someone on the team has their Windows region set to US (it happens more than you'd think, especially on laptops that came from a global IT build), the same file will parse differently on their machine.

The fix is to use Change Type > Using Locale in Power Query and explicitly pick English (Australia). That bakes the locale into the step, so it doesn't depend on whoever happens to be refreshing. We now do this by default on any text-based date column in client models. It's one extra click and it has saved us from a lot of "why is March missing" conversations.

Also check the file-level locale under File > Options and settings > Options > Current File > Regional Settings. It's per-file, which surprises people.

Date/Time/Timezone is a trap

Back to this one. When a Date/Time/Timezone column loads into the model, Power BI converts it to the local time of the machine doing the refresh, then drops the offset.

Think about what that means. On your laptop in Sydney, values convert to AEST or AEDT. When the dataset refreshes in the Power BI service, the refresh runs in UTC. So the same model produces different date values depending on where it refreshed. Transactions near midnight jump to a different day. Daily totals shift. Nobody notices for weeks.

We had a retail client whose Monday sales were consistently a little high and Sunday sales a little low in the service, but correct in Desktop. That was the cause.

My advice: never let a Date/Time/Timezone column load into the model. In Power Query, explicitly convert it to the timezone you want using DateTimeZone.SwitchZone (or convert to UTC and handle display in the report), then change the type to Date/Time yourself. Be deliberate about it. And remember daylight saving. Queensland doesn't observe it, NSW and Victoria do, WA doesn't. If you have national operations, pick one reference timezone and document it.

Type detection - leave it on, but don't trust it

By default, Power Query looks at the first 200 rows of a data source and guesses types. For unstructured sources like CSV and Excel, it adds a "Changed Type" step automatically.

You can turn this off under Options > Data Load > Type Detection. Some developers do. I prefer to leave it on and then review the step, because it saves typing on wide tables.

The problem is the 200 row sample. If your first 200 rows of a "Quantity" column are all whole numbers and row 50,000 has 2.5, the column is typed as Whole Number and that value either errors or gets truncated, depending on the operation. If an ID column is all digits for the first 200 rows and then has "A1234", you get errors on refresh.

What we do on client projects:

  1. Let auto-detection run during initial development
  2. Before the model goes anywhere near production, review every Changed Type step and confirm each type against what the source system actually allows
  3. For database sources, the types usually come from the schema, so this matters less. For files and APIs, it matters a lot

And check the errors. Power Query will show a count of errors per column in the column quality view (turn on View > Column quality). A column that's 99.9% valid and 0.1% error is a column where 0.1% of your data silently becomes null in the model.

Text, True/False and blanks

Text columns are Unicode and can be very long, but long text is expensive in the model. If someone is loading full email bodies or free-text comments into a Power BI model "just in case", ask whether anyone actually needs them in a report. Usually not. They bloat the model and they compress terribly because almost every value is unique.

If you have Y/N flags from a source system, convert them to True/False. Measures read better.

Blanks are where DAX gets interesting. BLANK is not the same as zero or an empty string, though it behaves like both in some comparisons. BLANK + 5 returns 5. BLANK = 0 returns TRUE. That's mostly helpful, because visuals hide rows with blank values, but it can hide data problems. If a measure is returning blank when you expected zero, look at whether the underlying column has nulls from a failed type conversion.

Relationships need matching types

Last practical point. Relationships between tables need the key columns to have the same type. Power BI will usually refuse to create a relationship between a Whole Number column and a Text column, or will create it and give you no matches.

The common case we see: a product key is Whole Number in the fact table (because it came from a database) and Text in the dimension (because it came from an Excel file someone maintains). The relationship either fails or returns nothing, and the report shows a single "(Blank)" row with all the sales against it. Fix it by making both sides the same type, ideally Whole Number if the values allow it.

What I'd actually do on a new model

If you're starting a Power BI model tomorrow, here's the short checklist we use:

  • Money columns as Fixed Decimal Number
  • Keys as Whole Number where possible, same type on both sides of every relationship
  • Codes, postcodes, ABNs and phone numbers as Text
  • Text dates parsed with Change Type Using Locale set to English (Australia)
  • No Date/Time/Timezone columns reaching the model
  • Durations converted to a sensible unit before load
  • Review every auto-generated Changed Type step before production
  • Column quality view on, errors at zero

None of this is glamorous, and it's not the part of a Power BI project anyone wants to talk about in a steering committee. But getting types right early is much cheaper than explaining a 3 cent discrepancy to a CFO later.

If you've got a model that's giving inconsistent numbers or refreshing slowly, our Power BI consultants do a lot of this kind of model clean-up work. And if your data platform is moving toward Fabric, the same type discipline applies in lakehouses and semantic models, which our Microsoft Fabric consultants can help with. Happy to have a look, get in touch.