Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ovetteabejuela
Impactful Individual
Impactful Individual

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.

1 ACCEPTED SOLUTION

#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.

Specializing in Power Query Formula Language (M)

View solution in original post

6 REPLIES 6
ovetteabejuela
Impactful Individual
Impactful Individual

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!'.

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

#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.

Specializing in Power Query Formula Language (M)

Hi @MarcelBeug

 

Thanks for the intro. I just realized also that is not possible to change it like that if the data type is changed in excel it forces me to change it in the excel report. It was a bit annoying but then I just delete it and it works. 


Thanks,

J.

 

@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.

Hi,

Same with #N/A...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.