Power Query yields different row count when filtering where value <> 0

[Mill1234]

I have the latest version of the Power Query addin installed in Excel13. 

There is a large set of data, north of 100K rows. 

The last step in the query is to filter where the value <> 0. Each time I run the query, I get a different row count. Eventually, I see the same row count, generally the highest number, and the data is accurate and ties...but I have to run it a few times before I can be confident it is the correct number of rows. The filter was set by picking the drop down and Does Not Equal 0. It was not done by unchecking zero from the picklist.

The overall query is complex with a number of appends. I tried troubleshooting by removing steps and running the query multiple times until I saw that I was getting different row counts. Every time, the issue came up at the step where the filter was set. 

It's hard to trust the data and I have not found a solution on any of the forums. Also, I am unable to share the data. 

Any help is appreciated. Thanks!

[GilbertQ]

Hi there, what I would suggest is to rather not select it from the Drop Down as that can often yield unexpected results.

What i would do is to click on the Drop down and then select Advanced. And then put in your options there?

Also ensure that the column data type is set to Number (either decimal, fixed decimal or whole number) to ensure that it recognizes the zero.

Also when you refresh your data is it coming from a live source (database)? Because if there are constant updates it could be consistently changing.

[Mill1234]

Hi guavaq, 

Thakgs for the response. I implemented your recommendation for the Advanced filter, ensuring the type is set to decimal in a prior step. I did this for all queries in the workbook. Unfortunately, I am seeing the same thing. RE: the DB question, the DB is static. 

I went through a process of creating a copy of the query, removing steps and writing to a table. I then ran the query multiple times to see if I would get a different number of row counts. If the same row count was achieved each run, then I deleted the query, made a new copy, and added in one more step from the original query, then re-ran multiple times. The point of all of that was to isolate where in the query the different row counts was occurring. It isn't happening until the last step, which happens to be the filter for non zeros. 

Any other ideas? Almost seems like it is some sort of issue/bug with PQ. When I used the Advanced filter, is there any way within the code to see if the filter was set using the Advanced filter option? I didn't see anything different. 

Thanks,

[GilbertQ]

Hi there without looking into the dataset, normally what you can do in the Advanced Editor it should show something along the lines of below when a filter is being applied.

When a filter is being applied to a text value it would be wrapped in the double quotes EG: "89", whilst if it is a number value it would just have the number EG: 89

Here is an example where I am filtering a Run Number greater than 101, where the column is set to Whole Number

#"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each [Run] < 101),