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
cole_lehmkuhler
Helper II
Helper II

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
ankitpatira
Community Champion
Community Champion

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

View solution in original post

4 REPLIES 4
ankitpatira
Community Champion
Community Champion

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

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!

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.  

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

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.