Filtering in Power Query
Last week I was teaching a Power BI Workshop. On the final day while attendees were building their own solutions, one participant named Linda says to me: "Melissa, Power Pivot is missing some data." We proceeded to trace back through her Power Query operations to determine where the data got eliminated.
She shows me the final Power Query and points to the column header and says "See, there's no filters here. So why didn't it end up in Power Pivot?" Since this was a very interesting observation from a very bright gal, I thought I'd share it.
To illustrate, first let's set up a quick little list of Dept Numbers and Names in the first step of our Query Editor:
In the second step, let's add a filter to remove one Dept Number.
In our third step after the filter, let's do something. Anything. For grins I merged two columns to produce a concatenated column of Dept Name - Number. Really though, this third step only exists to prove a point that there is no longer an indication in the column header that a filter occurred on this column in a previous step:
I often say that each step in the Query Editor displays the state of the data at that point in time. Since the filter occurred on a previous step, the rows filtered out are now really gone in the next step - the state of the data changed. This is why there's no longer an indication a filter occurred in a previous step. Power Query behavior isn't like Excel where the rows are still there just hidden; rather, the rows are truly filtered out and won't progress further along in the steps or into Power Pivot.
So, having said all that, the two main takeaways are:
- Filters in Power Query aren't controlling what you see on the screen; they truly exclude the data from progressing any farther.
- There's not a visual indication in Power Query on steps after a filter operation has occurred, so keep an eye out for that if you are doing some data exploration as you build your steps.