Forum Discussion

ovetteabejuela's avatar
ovetteabejuela
Impactful Individual
9 years ago
Solved

PowerQuery: Can't Replace #DIV/0! with null

I don't know why, but it seems that I can't get rid of this literal #DIV/0! coming from an excel source.

 

I already tried to force the column to a text before applying the replace value function but as soon as I Close and Apply it, it's telling me that I got errors and these errors where those line with #DIV/0! in them.

Need Help
  • MarcelBeug's avatar
    MarcelBeug
    9 years ago

    #DIV/0 and #N/A import as an error in Power Query and can not be distinguished from any other error.

     

    So you can use the Query Editor options for handling error values:

    replace error values via Transform - Replace Values - Replace Errors or

    remove rows with errors via Home - Remove Rows - Remove Errors.

     

    This works for the selected columns. In order to remove errors from the entire table, you can use the dropdown at the very upper left corner of the table, and then select Remove Errors (close to the bottom of the list).

     

    If you want to replace errors in all columns then you must select all columns first and then use the option on the Transform tab.

6 Replies

  • ovetteabejuela's avatar
    ovetteabejuela
    Impactful Individual
    9 years ago

    Just to add, I tried to filter in that column where #DIV/0! appears. I filtered with anything that contains "/" and I got this error

     

    DataFormat.Error: Invalid cell value '#DIV/0!'.
    • MarcelBeug's avatar
      MarcelBeug
      Community Champion
      9 years ago

      #DIV/0 and #N/A import as an error in Power Query and can not be distinguished from any other error.

       

      So you can use the Query Editor options for handling error values:

      replace error values via Transform - Replace Values - Replace Errors or

      remove rows with errors via Home - Remove Rows - Remove Errors.

       

      This works for the selected columns. In order to remove errors from the entire table, you can use the dropdown at the very upper left corner of the table, and then select Remove Errors (close to the bottom of the list).

       

      If you want to replace errors in all columns then you must select all columns first and then use the option on the Transform tab.

      • ovetteabejuela's avatar
        ovetteabejuela
        Impactful Individual
        9 years ago

         

        MarcelBeug

         

        replace error values via Transform - Replace Values - Replace Errors

        If you want to replace errors in all columns then you must select all columns first and then use the option on the Transform tab.

         

        That worked for me! Thanks again the nth time!

         

         

        jmdh

         

        Thanks, good to know there's that other option however if I convert to CSV it breaks the automation process I'm eliminating human-interventions.

    • jmdh's avatar
      jmdh
      Advocate IV
      9 years ago

      I tried something : convert the Sheet to CSV and it works...