Data Types and Casting
Arjun drags Qty onto a card, expecting a total, and Power BI will only count the values or show the first one — never sum them. The column looks like numbers, but Power Query believes it is text. A data type is Power Query's belief about a column, and wrong beliefs cause wrong totals, odd sorting and relationships that refuse to work.
The rule
Read the header icon, then set every type on purpose.
Every column header in Power Query shows an icon for its type:
| Icon | Type | Example value |
|---|---|---|
| ABC | Text | Pune |
| 123 | Whole number | 4 |
| 1.2 | Decimal number | 27.5 |
| $ | Fixed decimal number (currency, four decimal places) | 30.00 |
| % | Percentage | 12% |
| Calendar | Date | 5 January 2026 |
| Calendar with clock | Date/Time | 5 January 2026 09:15 |
| Tick and cross | True/False | TRUE |
| ABC123 | Any — no type decided | anything |
Right after it promotes headers, Power Query usually adds a Changed Type step by guessing each column's type from a sample of the top rows. A guess can be wrong when those rows are unusual, so check every column and set types yourself: click the header icon, or use Transform → Data Type.
Changing a type is called casting. Each value is converted separately. When a value cannot be converted, only that cell shows Error (a DataFormat.Error); the other cells convert normally. The text N/A cast to Whole number is a typical example.
Dates need extra care, because the same text means different days in different countries. 05/01/2026 is 5 January in India and 1 May in the United States. Use Change Type → Using Locale, pick Date and English (India), and Power Query reads the text the way the till wrote it.
Why types matter:
- Text cannot be summed, so a text column of numbers can only be counted or shown as a first or last value.
- Text sorts character by character, so
10comes before9. - A relationship needs the same type on both key columns.
The shortcut
Scan the icons left to right and ask of each column: "What will I do with it?" Add it up → a number type. Relate on it → the same type as the key it matches. Do date arithmetic → Date. An ABC123 → never leave it.
OrderID
Shows 123, Whole number. It will be a key, so that is right.
The trap is trusting the automatic Changed Type step. It guessed from the rows it happened to see, and it runs again on every refresh.
Drills
Answer fast — trust the rule.
The Qty header shows ABC. What happens when Arjun drags Qty onto a card?
Sorted A to Z as text, in what order do 9, 10 and 100 come out?
Qty is changed to Whole number. What does a cell that held N/A become?
The till writes dates as 05/01/2026, meaning 5 January. What is the safest way to type the column?
Which header icon means no type has been decided?
Remember
- Read the header icon, then set every type on purpose.
- Ask what you will do with each column: add up, relate, or do date maths.
- Traps: trusting the automatic Changed Type step, and dates read in the wrong locale.