cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
cole_lehmkuhler Regular Visitor
Regular Visitor

Edit Query Filtering Problems

I have a large amount of data with around 800,000 rows on specific sales that were recorded. I have a column for original purchase date, quarter, year, sale price, and also division (the divisions in the division column are marine,gas,diesel, transmission, and other) I'm trying to go into the edit query and filter the rows by division going from gas to the other divisions, while also at the same time filtering the quarter so I can see sales per quarter of each division. It works fine filtering the division, however, when I try to filter the quarter as well only 2 quarters show up originally, but when I press load more quarters it only gives me 4 more quarters, but I know there are about 20 different quarters that I should be able to filter through. I also get an error message saying "[DataFormat.Error] Invalid cell value '#N/A'." (some of these sales don't have the original quarter date in which they were purchased documented so they are marked N/A) Any help on filtering this or a workaround would be great. I think the issue might be due to the fact that not all of the purchase quarter options can be viewed in the window because the amount of rows is so large, but I'm not sure. Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Edit Query Filtering Problems

@cole_lehmkuhler you first need to change data type of date column to Date which will result in rows with N/A to be error out. Then use Replace Error function and get rid of those errors with null or other date.
4 REPLIES 4
Super User
Super User

Re: Edit Query Filtering Problems

@cole_lehmkuhler you first need to change data type of date column to Date which will result in rows with N/A to be error out. Then use Replace Error function and get rid of those errors with null or other date.
cole_lehmkuhler Regular Visitor
Regular Visitor

Re: Edit Query Filtering Problems

This won't work because it will error out all of the options then because I'm not searching by purchase date. I'm searching by quarter which isn't in a date format. An example of a value that is in this column would be "Q3 2013" , but I'm only getting the filter option for Q1 2013 and Q2 2013 and all four quarters in 2012. I need to be able to filter between all quarters 2013-present

cole_lehmkuhler Regular Visitor
Regular Visitor

Re: Edit Query Filtering Problems

My mistake I misread your response the first time, and it didn't work. I just started working with it again and went back to see if anyone else had responded and reread your answer and realized you were correct. Thank You!

chaz2jerry Regular Visitor
Regular Visitor

Re: Edit Query Filtering Problems

I also had this error of invalid value "#N/A" and solved by applying Replace Error step before other operational query steps.  If I apply Replace Error at the end of the query list then it won't work because the error can occur during one of the earlier steps.