Results 1 to 3 of 3

Thread: Removing errors does not work

  1. #1
    Seeker Bane's Avatar
    Join Date
    May 2020
    Posts
    5
    Articles
    0
    Excel Version
    2016

    Removing errors does not work



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi! I cannot remove certain errors from my dataset in Power Query. I tried removing errors, I tried replacing errors, I tried removing last row (which happens to be the row where the error is), nothing works (see attachment to get a peek at how the dataset looks). Is this simply a bug or is there a workaround?
    Attached Thumbnails Attached Thumbnails Click image for larger version. 

Name:	Removing errors not working.jpg 
Views:	9 
Size:	97.8 KB 
ID:	9771  

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,373
    Articles
    45
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    This is a really common occurrence when you accidentally trigger an error during a change type step, then filter on the column that contains the error. What happens is that the row with the error shows errors all the way across, and the data set gets truncated.

    Go back to your FilteredRows1 step and record the row number where your data set ends. Then go back to the (previous) Changed Type step. Check that row to see where the error is.

    You need to remove/replace your errors at this point, THEN do your filter and that should resolve the issue.

    Hope that helps,
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Seeker Bane's Avatar
    Join Date
    May 2020
    Posts
    5
    Articles
    0
    Excel Version
    2016
    Thank you!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •