SOLVED
Home

Excel PowerQuery, entire row has Error

%3CLINGO-SUB%20id%3D%22lingo-sub-1174292%22%20slang%3D%22en-US%22%3EExcel%20PowerQuery%2C%20entire%20row%20has%20Error%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1174292%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHello%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESee%20picture%20below%2C%20how%20do%20I%20remove%20a%20complete%20row%20that%20has%20or%20shows%20'Error'.%26nbsp%3B%20I%20have%20tried%20to%20remove%20last%20row%2C%20replace%20error%20with%20value%200%2C%20transform%20a%20column%20back%20to%20text%2C%20but%20I%20can%20not%20get%20it%20to%20shift.%26nbsp%3B%20The%20source%20file%20has%20a%20formula%20that%20is%20throwing%20up%20a%20'%23n%2Fa'%20error.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22error.PNG%22%20style%3D%22width%3A%20971px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F171261iAF9F38799E554E14%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22error.PNG%22%20alt%3D%22Error%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3EError%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMany%20thanks%3C%2FP%3E%3CP%3EChris%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1174292%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1174485%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20PowerQuery%2C%20entire%20row%20has%20Error%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1174485%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F544914%22%20target%3D%22_blank%22%3E%40ChrisC365%3C%2FA%3E%26nbsp%3B-%20Right%20%2B%20Click%20%26gt%3B%20Remove%20Errors%20does%20not%20work%3F%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%226.png%22%20style%3D%22width%3A%20250px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F171278i2DBC1E17C26D7AEC%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%226.png%22%20alt%3D%226.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1174796%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20PowerQuery%2C%20entire%20row%20has%20Error%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1174796%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F303863%22%20target%3D%22_blank%22%3E%40ChrisMendoza%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20tried%20a%20new%20approach%20this%20time.%26nbsp%3B%20I%20found%20if%20you%20select%20'Remove%20Errors'%20first%20this%20seems%20to%20do%20the%20trick.%26nbsp%3B%20Before%20I%20was%20first%20change%20the%20type%20of%20formatting%20columns%3B%20change%20to%20text%2C%20decimal%20etc%2C%20and%20now%20it%20seems%20to%20have%20worked.%26nbsp%3B%20So%20thanks%20for%20your%20response.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EChris%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hi

 

Hello

 

See picture below, how do I remove a complete row that has or shows 'Error'.  I have tried to remove last row, replace error with value 0, transform a column back to text, but I can not get it to shift.  The source file has a formula that is throwing up a '#n/a' error.

 

ErrorError

 

Many thanks

Chris

2 Replies
Highlighted
Solution

@ChrisC365 - Right + Click > Remove Errors does not work?

6.png

Highlighted

@ChrisMendoza 

 

I tried a new approach this time.  I found if you select 'Remove Errors' first this seems to do the trick.  Before I was first change the type of formatting columns; change to text, decimal etc, and now it seems to have worked.  So thanks for your response.

 

Chris

Related Conversations
return max in date range based on X column #
mryno in Excel on
0 Replies
Excel help
nefroti in Excel on
1 Replies
Unhiding Multiple Columns (A-H) are hidden.
jbtaxman in Excel on
2 Replies
Excel file data recovery
Malneedi_Vamsi in Excel on
0 Replies
Can I add two data to the map?
Yuwei1003 in Excel on
1 Replies
Excel Autofill Options greyed out
Drosera_Eco61 in Excel on
1 Replies