Power QueryExercises

Power Query data type pitfalls that quietly change your numbers

Troubleshooting · By Tahir Nas · Examples run in our simulator · Published · Updated

Jump to the practice tasks

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.

21.75 hours became 22. Use type number (decimal) when fractions are possible.

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

ItemUnits
Line9
Reel20
Kite100

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

"9" sorts above "20" and "100" as text. Typed as numbers, Kite would be first.

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.

Two timestamps, one day.

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.

Lessons

More resources

Function reference

Sources

Microsoft's official documentation for the features and functions used on this page.

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.