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
- Units or text in the cell: "12 pcs", "n/a", "-".
- Decimal values in a whole-number column read with the wrong culture.
- Empty strings instead of null.
How to fix it
- Clean first: Text.Select([Qty], {"0".."9"}) keeps the digits.
- Use try Int64.From(x) otherwise null to flag bad rows without failing.
- Convert with the source culture when separators differ.
Find the step that fails
- Open the query in the Power Query editor (Excel: Data → Queries & Connections → Edit; Power BI Desktop: Transform data).
- Click the steps in Applied Steps from the top. The first step that shows the error is where it starts; later steps only repeat it.
- Read that step's formula in the formula bar (View → Formula Bar if it's hidden) and compare it with the columns and values the previous step produced.
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
- 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
Split a timestamp into its date and hour (door access)
Each event has a text timestamp like 2026-03-04T14:30:00. Add a real Date and the Hour of day.
- beginner~5 minFree
Make a mixed number/text code column all text (garden centre)
Product codes arrive as a mix of numbers (1001) and text (A-17, 0042). Make every code text.
- beginner~5 minFree
Build a date from year, month and day columns (deliveries)
A legacy export stores dates as three number columns. Combine them into one real date.
Guides
- Power Query data type pitfalls that quietly change your numbers
- Change type using locale: dates and numbers from other countries