Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Replace NaN with blanks in Power Query

I am importing data from an excel sheet where many cells contain NaN values.    How can I replace these NaN values with blanks or null in PowerQuery?    The Replace Values function does not appea...
Need Help
  • mahoneypat's avatar
    mahoneypat
    5 years ago

    There is a function for that.  You can apply any mathematical transform to that column, and then adapt the code in the formula bar using the Number.IsNaN() function like this.

     

    = Table.TransformColumns(#"Added Custom", {{"Custom", each if Number.IsNaN(_) then null else _, type number}})

     

    Regards,

    Pat

mahoneypat's avatar
mahoneypat
Microsoft Employee
5 years ago

There is a function for that.  You can apply any mathematical transform to that column, and then adapt the code in the formula bar using the Number.IsNaN() function like this.

 

= Table.TransformColumns(#"Added Custom", {{"Custom", each if Number.IsNaN(_) then null else _, type number}})

 

Regards,

Pat

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Perfect, thanks. Extra question: is there a simple way to apply this transformation to multiple columns in a one-line command?