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 appear to find any entries when inserting "NaN" in the "Value to Find" field. Also - I cannot insert a blank value in the "Replace With" field. 

 

Thanks 

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

6 Replies

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft 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?

  • 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 

  • 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 

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi, AlB  the NaN values appear to be numeric

     

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

    Anonymous 

    Have you tried to replace values with

        NaN

        null

    Enter them directly as above, without ""

    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