Filter rows results in one row with errors in every column

[sdavidson]

Hi,

I am transforming data from an excel file.

The data isnt in the best format so one of the first things i do is filter out any rows with null or blank values. 

I know the error probably originates in the excel and to fix that would be the best solution but i dont own the excel so cant change it and have to work with what i have got. 

This filter gives me the result that I want and only shows the relevant data, however in the row after where my data ends there is another row where everey column has a #REF! error.

This means that when i try to save and apply it wont work and gives off the error: "Failed to save modifications to the server. Error returned: 'OLE DB or ODBC error: [DataFormat.Error] Invalid cell value '#REF!'.. An unexpected exception occurred. "

Any ideas why this row is there or how to actively remove it? I have tried IFERROR functions and they havent worked. I also tried just a column with "try <column name>" and filtering on that but the errors spread to this column too. 

Any help is appreciated. 

[BA_Pete]

Hi @sdavidson ,

Have you tried just removing rows that contains errors in Power Query?

Go to the Home tab > Remove Rows > Remove Errors:

Pete

[sdavidson]

Yes i tried this. It didnt do anything to remove the row

[BA_Pete · Accepted solution]

The #REF! value isn't a Power Query output, it's an Excel ouput, so you should be able to select one of the very first steps in your query (before any column transformations have been done) and find the row with this value in it, right-click on the cell value and go to Text Filters > Does Not Equal.

Pete

[sdavidson]

That worked thanks!