Power Query data type pitfalls that quietly change your numbers
Troubleshooting · By Tahir Nas · Examples run in our simulator · Published · Updated
1. Identifiers typed as numbers
Codes like 00731 lose their zeros, and long account numbers lose precision. Keep identifiers as text: you never do arithmetic on them.
2. Decimals typed as whole numbers
Whole number (Int64.Type) rounds. A quantity column with occasional fractions is quietly rounded:
Table.TransformColumnTypes(#table({"Hours"}, {{"7.5"}, {"8"}, {"6.25"}}), {{"Hours", Int64.Type}})Result
| Hours |
|---|
| 8 |
| 8 |
| 6 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
3. Numbers left as text
Text compares character by character, so "100" is less than "20", and a 'top 3' on a text column is alphabetical:
let
T = #table({"Item", "Units"}, {{"Kite", "100"}, {"Reel", "20"}, {"Line", "9"}})
in
Table.Sort(T, {{"Units", Order.Descending}})Result
| Item | Units |
|---|---|
| Line | 9 |
| Reel | 20 |
| Kite | 100 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
4. Columns left as any
Custom columns without a type, and columns combined from inputs with different types, are type any. Excel loads them cell by cell; Power BI treats them as text, so they can't be summed in visuals. Pass a type as the last argument of Table.AddColumn, and check with Table.Schema.
5. The wrong culture
Day-first dates read month-first swap days and months for days 1–12 with no error. Decimal commas read as thousands separators multiply values. Always convert with the source's culture (change type using locale).
6. Dates that are really datetimes
A column typed as datetime with times attached won't match a date column in a merge, and grouping by it gives one group per timestamp. Convert to date when the time isn't needed:
let
Log = #table({"At"}, {{#datetime(2026, 10, 5, 9, 15, 0)}, {#datetime(2026, 10, 5, 17, 40, 0)}}),
Days = Table.TransformColumns(Log, {{"At", DateTime.Date, type date}})
in
Table.RowCount(Table.Distinct(Days))Result
1
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
7. Typing too early, or twice
Typing before cleaning turns fixable text ("1,200 ") into errors; typing in several scattered steps means several places to update when a column is renamed. Clean, then type once per culture, then calculate.
Practise it in the simulator
Each task opens the exact exercise in the browser workspace and is checked against fresh data, not just the example. Free tasks need no account.
- beginner~6 minFree
Keep order numbers' leading zeros (kite shop)
Type a CSV so order numbers stay text (0007, not 7) while quantities and prices become numbers.
- intermediate~7 minFree
Read amounts written with a decimal comma (consultancy)
A German export writes 1.234,56 for one thousand two hundred thirty-four point five six. Convert it with the de-DE culture.
Related pages
Lessons
More resources
Function reference
Sources
Microsoft's official documentation for the features and functions used on this page.
- Data types in Power Query (Concept, Microsoft Learn)
- Power Query M type system (Concept, Microsoft Learn)
- Table.TransformColumnTypes (Function reference, Microsoft Learn)
- Table.Sort (Function reference, Microsoft Learn)
- Table.TransformColumns (Function reference, Microsoft Learn)
- Table.RowCount (Function reference, Microsoft Learn)
- Table.Distinct (Function reference, Microsoft Learn)
Power Query Exercises is an independent educational platform and is not affiliated with, endorsed by, sponsored by, or approved by Microsoft Corporation. All third-party trademarks belong to their respective owners.