Power BI Quick Tip – Using Parameters in Power Query Filters

I have been working lately quite a bit in Power Query and having to shape and re-create data based on the requirements.

They had a requirement to make the data dynamic and immediately the Parameters in Power BI came to mind to create the solution.

Now as we know using Parameters in Power BI has a lot of advantages.

But one quick tip that I want to share or highlight is that your parameters can be used in quite a few places in Power Query.

And in this tip, it is using your parameters to dynamically filter data in Power Query.

By doing so, your data that loads will then be dynamically driven by what you specify in your parameter.

Example

Here is an example below.

I created a parameter called Year Version (Which is the last 2 digits of the year).

I gave it the type of Decimal Number.

I then gave it a number of 18. As shown below.

Next I went into my table and went to my column called Data Year that I wanted to dynamically filter my Data Year with my parameter.

I think clicked on the column and selected Number Filters, then Greater Than Or Equal To

This then brings up the Filter Rows Window

Now the magic of the tip is in this next section below. If you look at below, it appears to me that your ONLY option is to put in a value.

BUT if you click on the drop down you now get 3 options!

Now I clicked on the option and changed it to Parameter. I then selected my Year Version parameter from above.

And now my data only show data after and including Data Year 18. And if I change my parameter, it will then dynamically
update my filtered data.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s