Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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

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. 

 

9419 not present.PNG

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. 

9419 present.PNG

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.  

1 ACCEPTED SOLUTION
Jimmy801
Community Champion
Community Champion

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.

Jimmy801_4-1600958554798.png

 

To filter for a number, use this GUI

Jimmy801_1-1600958200315.png

 

Jimmy801_2-1600958219018.png

 

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

Jimmy801_3-1600958309086.png

 

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

 

 

View solution in original post

3 REPLIES 3
AlB
Super User
Super User

@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 

 

SU18_powerbi_badge

Anonymous
Not applicable

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

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.

Jimmy801_4-1600958554798.png

 

To filter for a number, use this GUI

Jimmy801_1-1600958200315.png

 

Jimmy801_2-1600958219018.png

 

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

Jimmy801_3-1600958309086.png

 

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

 

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors
Top Kudoed Authors