Power QueryExercises

Choose types deliberately: identifiers are text

Pick types that preserve meaning, starting with the rule that identifiers are text.

~8 min read · beginner · Module 3: Data types, locale and nulls

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

Before this: Import CSV files: delimiters, encodings and headers

A type tells Power Query what a value means. Get it wrong and information disappears silently. The classic case: an order number 0007 typed as a whole number becomes 7, and the zeros are gone for good. Nothing errors, so nobody notices until a lookup fails.

A simple rule

Ask whether you would ever add, average or subtract the value. Quantities, prices and amounts are numbers. Order numbers, product codes, phone numbers, postcodes and account numbers are labels, so they're text, even when every character is a digit.

let
    Source = #table({"OrderID", "Postcode", "Qty"}, {{"0007", "02134", "3"}, {"0112", "10001", "12"}}),
    Typed = Table.TransformColumnTypes(Source, {{"OrderID", type text}, {"Postcode", type text}, {"Qty", Int64.Type}})
in
    Typed

Result

OrderIDPostcodeQty
0007021343
01121000112

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

Now the same data with OrderID and Postcode typed as whole numbers. It runs without a single error, which is exactly the danger:

let
    Source = #table({"OrderID", "Postcode", "Qty"}, {{"0007", "02134", "3"}, {"0112", "10001", "12"}}),
    Typed = Table.TransformColumnTypes(Source, {{"OrderID", Int64.Type}, {"Postcode", Int64.Type}, {"Qty", Int64.Type}})
in
    Typed

Result

OrderIDPostcodeQty
721343
1121000112

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

Number types

In the editorIn MUse for
Whole numberInt64.TypeCounts and quantities that can't have fractions
Decimal numbertype numberPrices, rates, measurements
Fixed decimal numberCurrency.TypeMoney with at most four decimal places
Texttype textIdentifiers, codes, names

Typing a value with decimals as a whole number rounds it, so "2.7" becomes 3. If a column might contain fractions, use type number.

Where to set types

Set types once, right after the data has its final headers, and again after steps that create columns (custom columns can be typed in the same step with the fourth argument of Table.AddColumn). Avoid a long series of Changed Type steps scattered through the query; each one is another place where a renamed column breaks refresh.

Where to find this in Excel

Excel for Windows
Home > Data Type, or the type icon at the left of the column header
What you see
The icon shows the column's current type; picking Text keeps leading zeros in identifiers.

Paths are for the Power Query editor in Excel for Microsoft 365, checked against Microsoft's documentation on 8 November 2026: Data types in Power Query (Microsoft Learn).

Common mistakes

  • Letting automatic detection type IDs, postcodes or phone numbers as numbers, losing leading zeros.
  • Typing a quantity column as whole number when some rows legitimately have fractions.
  • Trying to restore lost zeros with number formatting after loading; the original text is already gone.

Excel, Power BI and the simulator

  • Both hosts add an automatic Changed Type step after import unless type detection is turned off (Excel: Query Options → Data Load; Power BI: Options → Current File → Data Load).
  • Excel worksheets display numbers by cell format, so a number column can look like text; check the type icon in the column header instead.
  • In the simulator, whole numbers beyond ±9,007,199,254,740,991 raise an error: keep very long IDs as text, as you should anyway.
Functions in this lesson:Table.TransformColumnTypes

Next step

Apply this lesson in the simulator. The exercise is checked against fresh data, so the query has to work for rows it hasn't seen. It's free, with no account needed.

Practise: Keep order numbers' leading zeros (kite shop)

Practice this

Each exercise runs in the browser and is checked against fresh data, not just the example.