Post

Skillwave Training
@skillwave.training

One of our Ask Ken Anything participants wanted to know how to handle Not A Number (NaN) values in a CSV exported from their resource planning system. Replacing them seemed to work, yet the query still reported errors that came up blank when clicked. 😠 @excelguru.ca tested it live in #PowerQuery...🧵

Skillwave Training
@skillwave.training

He found something interesting. 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. 😮

0 reposts0 likes

Skillwave Training
@skillwave.training

His fix is to deal with it early. 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.

Skillwave Training
@skillwave.training

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

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…