Dynamic filtering of rows using a parameter

[diamond]

I have defined a parameter called State which may contain no values or may contain a list of state codes (one or more state codes) based on user selection. I want to filter rows from another table (which has state as one its fields) based on value in this State List (parameter). Please note if there are no state codes in the state list then all rows (no filter) should be returned from this other table otherwise only those rows that match each state-code from the list of state-codes should be returned from this other table. I tried multipe options - native query (could not get the syntax for referencing parameters) and PowerQuery without any success. Please help!

[MarcelBeug]

The following 3 queries work:

StateList:

= {"State1","State3"}

States:

= #table(type table[State = text],{{"State1"},{"State2"},{"State3"},{"State4"}})

Result:

let
    SelectionTable = Table.FromColumns({StateList}),
    #"Merged Queries" = Table.NestedJoin(States,{"State"},SelectionTable,{"Column1"},"Selection",JoinKind.Inner),
    #"Removed Columns" = Table.RemoveColumns(#"Merged Queries",{"Selection"}),
    Custom1 = if List.IsEmpty(StateList) then States else #"Removed Columns"
in
    Custom1

[diamond]

Thank you for the quick response. Please note in my case StateList is a parameter which can be empty or have multiple values (list). Your solution works when StateList is a List. Can you please update the solution to work when StateList is a parameter? 

I get the following error when StateList is a parameter - 

Expression.Error: We cannot convert the value null to type List.
Details:
Value=
Type=Type

Please see the extract of my Powerquery below where State is the parameter and can be empty or have multiple values in it.

let2
SelectionTable = Table.FromColumns({State}),
Source = Sql.Databases("myazuredatasourcename"),
MyDB = Source{[Name="MyDB"]}[Data],
dbo_tblClinics = MyDB{[Schema="dbo",Item="tblClinics"]}[Data],
#"Merged Queries" = Table.NestedJoin(dbo_tblClinics,{"State"},SelectionTable,{"Column1"},"Selection",JoinKind.Inner),
#"Removed Columns" = Table.RemoveColumns(#"Merged Queries",{"Selection"}),
Custom1 = if List.IsEmpty(State) then dbo_tblClinics else #"Removed Columns"
in
Custom1

[MarcelBeug]

You may try and adjust Custom1 step as follows:

Custom1 = if State is null then dbo_tblClinics else #"Removed Columns"

It is strange that you have a parameter that may have a list as a value: this is not something that can be defined via the User Interface.

This code:

{"State1","State2"} meta [IsParameterQuery=true, Type="Any", IsParameterQueryRequired=true]

Looks like this in the User Interface:

How did you define parameter State?

[diamond]

Here is my "State" parameter (screenshot below). The 2nd screenshot refers to the "StateList" which is derived from a database query.

Essentially the user should be able to choose one or more states or not choose a state from the state parameter list. This will drive  (cascading dropdown) the database query (clinics applicable for given state) for my 2nd parameter which will eventually drive my final query. If the user does not choose any parameter values for state and/or clinic then there is no filter applied to my final query and all rows are returned. I hope this helps explain my problem situation. Thanks again for your help.

[MarcelBeug · Accepted solution]

Thanks for the explanation. As far as I know, it is not possible to select multiple parameter values from the list.
This is also confirmed in this topic.

Aren't you looking for a slicer visual with the option to select all items?

In that case you don't need a separate statelist, but you can just use the "State" column from your data.

[diamond]

Here is my "State" parameter (screenshot below). The 2nd screenshot refers to the "StateList" which is derived from a database query.

Essentially the user should be able to choose one or more states or not choose a state from the state parameter list. This will drive  (cascading dropdown) the database query (clinics applicable for given state) for my 2nd parameter which will eventually drive my final query. If the user does not choose any parameter values for state and/or clinic then there is no filter applied to my final query and all rows are returned. I hope this helps explain my problem situation. Thanks again for your help.