Lesson 2: Data Types and Locale

Why your total is wrong before you write any DAX

Last level you saw each Applied Step build the query one change at a time. This level is about the step that most often breaks totals without anyone noticing: Changed Type.

Think of a data type as the label on a storage box. The contents can be identical, but if the box says "Text", Power BI treats 1234.50 as a word, not an amount. A word can't be added up. When you drop a text column into a visual, Power BI can only Count it, so you see "Count of Amount = 5" where you expected a sum.

The second trap is quieter. The number may become a number, just the wrong one, because Power Query read the text using the wrong regional format, or locale.

(Checked against Microsoft Learn, "Data types in Power Query" and the Table.TransformColumnTypes reference, read on 20 October 2026. Nothing in this lesson is a preview feature.)

The sample: an export from a European warehouse

orders_eu.csv is separated by semicolons, which is normal when the decimal mark is a comma:

OrderID;OrderDate;ProductCode;Qty;Amount
1001;03/04/2026;00123;2;1234,50
1002;15/04/2026;00456;1;89,99
1003;22/04/2026;00123;3;1851,75
1004;05/05/2026;00789;10;12,00
1005;30/05/2026;00456;2;179,98

The dates are UK style (day/month/year). The amounts use a comma as the decimal mark. The correct answers are: total Amount 3,368.22, total Qty 18 and 3 orders in April.

What goes wrong on a US-locale machine

Power BI Desktop reads text using your operating system's regional format, unless you override that for the file. On an English (United States) setup:

The errors are actually the lucky case, because you can see them. The silent ones are worse. Here the Amount total comes out as 336,822 instead of 3,368.22, and one April order moves into March.

Fix 1: decide the types yourself

For unstructured sources (CSV, Excel, text files), Power Query guesses types from the first 200 rows and adds a Promoted Headers step and a Changed Type step for you. That guess fails in two ways:

• A column that is numeric for 200 rows and holds N/A on row 201 turns into errors later.
• Codes such as 00123 become numbers and lose their zeros.

You can switch the guessing off. In Power BI Desktop go to File > Options and settings > Options:

• Global > Data Load > Type detection has three choices: Always detect…, Detect… according to each file's setting, and Never detect column types and headers for unstructured sources.
• Current File > Data Load > Type detection turns it on or off for this file only.

With detection off, columns arrive as Any (the ABC/123 icon) and the headers are not promoted. You add both steps yourself. If you'd rather keep detection on, select the auto-generated Changed Type step and delete it or edit it. Either way, the types are now a choice you made.

Gotcha: don't let Any reach the model. Microsoft's guidance is to set an explicit type on every column from an unstructured source.

Fix 2: Change Type with Locale

Right-click the OrderDate header and choose Change Type > Using Locale…. Pick Date as the type and English (United Kingdom) as the locale. For Amount, do the same with Fixed decimal number and a comma-decimal locale such as German (Germany). The locale only tells Power Query how to read the text. Once converted, a date is just a date.

Here is the M that the clicks produce, tidied up:

let
    Source = Csv.Document(
        File.Contents("C:\Data\orders_eu.csv"),
        [Delimiter = ";", Encoding = 65001]),          // UTF-8, semicolon separated
    Promoted = Table.PromoteHeaders(Source, [PromoteAllScalars = true]),

    // Codes are labels, not quantities: keep them as text so 00123 stays 00123
    TypedText = Table.TransformColumnTypes(Promoted,
        {{"OrderID", type text}, {"ProductCode", type text}}),

    // Read dates as day/month/year
    TypedDates = Table.TransformColumnTypes(TypedText,
        {{"OrderDate", type date}}, "en-GB"),          // third argument = culture

    // Read 1234,50 as one thousand two hundred thirty-four point five
    TypedNumbers = Table.TransformColumnTypes(TypedDates,
        {{"Qty", Int64.Type}, {"Amount", Currency.Type}}, "de-DE")
in
    TypedNumbers

The optional third argument of Table.TransformColumnTypes is the culture. Each step applies one culture to the columns it lists, which is why dates and amounts get separate steps here.

Picking types on purpose:

• Fixed decimal number (Currency.Type) for money. It holds four decimal places exactly, so you avoid floating-point rounding.
• Decimal number for measurements and ratios.
• Whole number (Int64.Type) for counts such as Qty.
• Text for IDs, postcodes and phone numbers. You never add them up, and they often carry leading zeros.
• Date rather than Date/Time when there's no time part.

Trade-off: changing the file's locale (Current File > Regional Settings) fixes every conversion at once. But it is invisible in the query, and it breaks the day a second source arrives in another format. A per-step culture shows up in the M and goes wherever the query goes.

Check your work

1. Every column header shows a deliberate type icon, and none shows ABC/123.
2. Select OrderDate, go to Add Column > Date > Month, and confirm that 03/04/2026 gives month 4.
3. In a card visual, Amount shows 3,368.22 as a Sum (not a Count), and Qty shows 18.
4. A visual by month shows 3 April orders.

Key takeaways

• A text-typed number can only be counted. Totals need a numeric type.
• The locale decides how text is read. The same text, 03/04/2026, means 4 March in en-US and 3 April in en-GB.
• Wrong-locale numbers can be silently 100 times too big. Errors are the easy case.
• Auto-detection guesses from the first 200 rows. Turn it off, or edit its Changed Type step.
• Use Change Type > Using Locale (the culture argument in M) per column, and keep IDs as Text.