Power QueryExercises

DataFormat.Error: We couldn't convert to Whole Number

By Tahir Nas · Examples run in our simulator · Updated

DataFormat.Error: We couldn't convert to Whole Number.

A value couldn't become an Int64 (whole number): it contains letters, units or a format the culture doesn't read. Each failing cell becomes an error; the rest of the column converts.

Common causes

How to fix it

  1. Clean first: Text.Select([Qty], {"0".."9"}) keeps the digits.
  2. Use try Int64.From(x) otherwise null to flag bad rows without failing.
  3. Convert with the source culture when separators differ.

Find the step that fails

Minimal example

Raises the error:

Int64.From("12 pcs")

Fixed:

try Int64.From("12 pcs") otherwise Int64.From(Text.Select("12 pcs", {"0".."9"}))

Both snippets are run by the Power Query Exercises simulator on every build: the first must raise DataFormat.Error, the second must run.

Practise avoiding it

Guides

Related lessons

Similar errors