This One Step Ruins Your Queries

We’ve all been there - you’re working on a query, you hit refresh to pull in new data, and… nothing. Your new data is missing, and you have no idea why 🤔. The issue is often a seemingly innocent step that you may have underestimated.

⚠️ One Step, Multiple Consequences

So what is this innocent step? Filter!

You’ve set a filter thinking it will only exclude unnecessary data. But what you’ve actually done is created a filter that was too specific.  

For example in the below screenshot, you filter the Product column to only show the hardware data. (When your query was created the data set only had two hardware types, desktop and laptop):

Please click here to download the practice file

By filtering this way what you’ve actually done is hard code “Hardware - Desktop” and “Hardware - Laptop.” So now any new data that doesn’t fit this filter won’t show up when you hit refresh. Here is the code in the formula bar showing the hardcoding:

🛡️ The Fix: Future-Proof Your Filters

The fix is quite easy, you just need a more inclusive filter.

• In your Applied Steps, delete the Filter step

• Click on the dropdown next to the column you want to filter, in this case it’s the Product column

• Don’t filter the items that you want filtered, instead go to Text Filters and select Contains, as seen in the image below:

The Filter Rows dialogue box pops up:

• In the field next to contains, type Hardware as this is what we want to be included in our filter

• This step ensures you’re not leaving out any essential data when new values are added, as seen in the image below:

Now when you hit refresh, all the new data will show up, so in this example “Hardware - AV Equipment” shows up:

 📥 Your Turn: Share Your Thoughts & Topic Wishes

Remember it's the small touches that make a masterpiece. That one filter step may not shatter your query, but it's crucial when welcoming in new data. Get it right, and you'll have a seamless stream of updated information at your fingertips. It's all about perfecting the little things for big results - keep your filters flexible and always double check them. Your future self will thank you for it! That’s my daily motto…keep my filters flexible 😊 and 👇

I would love to hear if you enjoyed today’s newsletter, please hit reply and let me know:

• 💌Loved it!

• 😐It’s okay

• 👎Do better

Also if there’s anything you would like me to cover please let me know.

If you missed last week’s newsletter, you can check it out here.

If you find value in what I’m doing and would like to support the newsletter, consider buying me a coffee. Your support fuels our journey together in uncovering the full potential of Power Query and beyond. Every coffee counts!☕

🏆 Comment of the Day

Please click here if you would like to watch the video that 999NINE99 is referring to, it shows you other sneaky Applied Steps that could ruin your queries and how to fix those errors.

👀 Sneak Peek for Next Week:

Next week's edition is one you won't want to miss. We're zeroing in on boosting your error handling capabilities. Imagine facing errors without that flicker of doubt and tackling them with confidence. That's where we're headed, to create a coding journey that's not only smooth but also quite enjoyable. Stay tuned!

Until next time, keep those queries rolling, and may your data always be clean and your transformations swift!

Happy Querying!

Thanks for reading. Until next week!

Miss Microsoft