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

Hi Anonymous 

How is the NaN shown exactly in power Query? Can you show a pic? Or better, share the pbix you are working with?

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