Post

One of our AKA participants wanted to know how to handle Not A Number (NaN) values in an exported CSV. Replacing them seemed to work, yet the query still reported errors that came up blank when clicked. 😠 I tested it live in #PowerQuery and found something interesting. 🧵

Ken Puls, FCPA, MS MVP

@excelguru.ca

18h

Set the column to Decimal Number and NaN becomes a true "not a number" value; set it to Whole Number and it becomes an error. Replace Errors catches the error but not the NaN, and there's no built-in function to replace NaN. 😮 My fix – for any type of replacements – is to deal with it early.

Ken Puls, FCPA, MS MVP

@excelguru.ca

Right after promoting headers but before setting data types, replace "NaN" as text. If it has already been converted, change the column to text, replace the values, then convert it back. 🧭Takeaway: Clean up problem values before you set data types, not after.

26/10/26, 5:26 pm2 reposts3 likes

Ken Puls, FCPA, MS MVP

@excelguru.ca

18h

This is the kind of hands-on troubleshooting AKA members get every session, using the messy data they actually work with. Subscribe today at skw-t.com/aka, or get access included with my #SelfServiceBI (skw-t.com/ssbi) or #Excel Fundamentals (skw-t.com/excelfun) Academies through @skillwave.training

skw-t.comAsk Ken AnythingGet access to an expert who will help you build real world solutions. The Ask Ken Anything (AKA) sessions are live, interactive coaching calls that are held twice each month. They’re designed to give…