Input Variable blank

Power Query
1

Hi, I have created an table in excel for user input. The idea is they can choose a year to filter down the data table in power query. It works great, but I am not sure how to make it work so that they can opt to see all years (ie have no filter) instead of one year at a time.

I have attached a word document with the screen shorts and explanation of what I am trying to achieve.

I would really appreciate it if anyone could help :slight_smile:

input variable excel question.docx (28.8 KB)

2

Hi @lvlandmark,

Give this a go.
Add an 'All" option to the YEARSELECTION table and turn the column type to text. Update the YearVariable as depicted below:

image
image758Ă—172 4.7 KB

And finally change the Table.SelectRows step into:
each List.Contains( YearVariable, [OrderDate.Year] )

I hope this is helpful

1 Like
3

Hi Melissa, thanks so much for your help. It worked almost perfectly. I amended the YearVariable as you showed it, like this

= if YEARSELECTIONTABLE{0}[Year]=“All”
then List.Transform( List.Select( YEARSELECTIONTABLE[Year], each _ <> “All”), Number.From )
else List.Transform({YEARSELECTIONTABLE{0}[Year]},Number.From)

and I updated the filter like this

= Table.SelectRows(#“Renamed Year”, each List.Contains( YearVariable, [OrderDate.Year] ))

and it worked great as long as my YEARSELECTIONTABLE contains an actual year.

When YEARSELECTIONTABLE contains “All” however, I got an error message to say "DataFormatError, We could not convert to a number. Details: “All”

So I think I need to do something more to line 2 in the statement above.

A thought that occurred to me is there is only ever one selected parameter in the table YEARSELECTIONTABLE, since this table is fed from a (data validation) list. The user selects one parameter from the drop down which might be “All” or it might be a year. so I first wondered at first if that was the issue and if I referred to another table in line 2 with all the available parameters / years that it might work.

So on line 2 of the statement I substituted YEARSELECTIONTABLE (which contains the single selected parameter, “All” or a year) with ALLYEARSTABLE (which contains all possible parameters ie “All” or 2021, 2022 etc). Both tables are formatted as text.

This was my new version:-
= if YEARSELECTIONTABLE{0}[Year]=“All”
then List.Transform( List.Select( ALLYEARSTABLE[Year], each _ <> “All”), Number.From )
else List.Transform({YEARSELECTIONTABLE{0}[Year]},Number.From)

However I got the same error message about not being able to convert All to a number. So I guess that was not the answer!!

If you have any more tips I will gratefully try them out.

4

I’d be happy to provide more support, please provide a sample PBIX or XLSX
Thanks.

Here’s my working example.
eDNA - Filter incl all option.pbix (15.8 KB)

1 Like
5

Thanks Melissa, I will download yours and take a look and see if I can get mine up and running… thx for your help so far :slight_smile:

6

I think there is an easier way by means of a parameter (in power BI), in Excel Power Query it would be the drilled down query you created.

When filtering, you can refer to you parameter or drilled down query YearVariable via this:

= Table.SelectRows([Previous step], each ([Year] = YearVariable))

In order to make this functional for a null value in your YearVariable, change the formula of the filter step to this:

= Table.SelectRows([Previous step], each YearVariable = null or ([Year] = YearVariable))

Hope this helps :slight_smile:

7

Thanks so much Melissa, I fiddled around a bit with my report and got it working as you demonstrated in your working example. I really appreciate it and thank you so much, it all worked exactly as you described.

Many many thanks.

8

Thanks so much for your suggestion Maikelshuvit, I will give this a go. In my testing I have discovered a couple of other ways I could have achieved my end game, and this might give me another option, so I will give it a go.

Many thanks for your help.

1 Like
9

Let me know if it works. Happy to help!

1 Like