Misleading column filters in Power query...what am I missing??

[Anonymous]

This should be a pretty simple one. 

I have a table with about 10,000 rows. 

I am looking for the entry 9419 within the column NLC. 

But, as we can see above, even when the maximum number of filter values are loaded (using the 'load more' button at the bottom right of the filter dialog), 9419 is not there. 

So 9419 is not in our table...right? 

Well...it is...and by rewriting the filter statement in M, I was able to obtain it. 

So what's going on here? It seems fairly pointless to have a filter feature that does not accurately reflect what is actually contained in the table. I presume I'm using this tool in the wrong way?? Is there a simple 'point and click' solution to checking whether a given value is present in a column? Never had this issue with filters in Excel, for example.  

[AlB]

@Anonymous 

You can just use "Filters" --> Equals and enter the number you need.  Right above where you entered the number as shown in your pic.

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Anonymous]

@AlB @Jimmy801 sorry for the late reply, thank you both very much for clearing up this one. Very useful; I think I misunderstood what Power Query is trying to achieve here. I will use the filter tools for this task in future! 🙂 

[Jimmy801 · Accepted solution]

Hello @Anonymous 

this how power query works. It's a transform tool, not a tool to handle the data itself. This is only a preview, and it's only loaded a limited amount of data. The same it happens when you make an Table.ExpandTableColumn. It's reading only a limited amount of rows to analyse the columnheaders.

To filter for a number, use this GUI

as soon the whole table is loaded to Power BI, you have the whole table with all data

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy