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

AlB's avatar
AlB
Icon for Community Champion rankCommunity Champion
5 years ago

Anonymous 

Yes,

 

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

 

and so forth

 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers