Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Power Query - "Contains" not working with "List of Parameter"

1.) I've created a list, from a Table, with several values :

 

PQ_1.png

 

 

 

 

 

 

 

 

 

 

 

 

2.) I created a Parameter and added this list (1) to it. Also with a default value "EBZH":

 

PQ_2.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

3.) Then I switched to my Query and added this Parameter to the ColumnFilter, via "Text Filters -> Contains....". My Goal is, to get all Rows which are matching to one of the values from the Parameter-List :

 

PQ_3.png 

 

 

 

 

 

 

 

 

 

 

 

 

4.) A closer look into the data shows that only the default value "EBZH", from the ParameterList, was used a FILTER.

Alle the values from the List itself weren't applied to the filter:

 

PQ_4.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Any idea what I have done wrong? 

 

My Goal ist still to work with this Parameterlist for filtering, because I have 10 other Queries for which I have to use the same filter and I dont want that to do that by hand everywhere.

 

Many thanks in advance for all the support

Cheers 

Alex

 

PS: M-Query "Contains" = SQL "IN" <= thats a least what I think I understud. If that wrang then please tell my that.

1 ACCEPTED SOLUTION
Anonymous
Not applicable

"List.Contains" was the correct solution.
But in my case it works only with a List and not with Parameter !

 

1.) I still used my List "List of Basen"

2.) I applyed the "Filter" as en extra M-Query statement via "Advanced Editor" in which I refered to the "List of Basen"

#"Filtered Rows7" = Table.SelectRows(#"Filtered Rows2", each List.Contains(#"List of Basen", [Basis])),

PQ_5.png

 

This blog post helped solved my problem : 

https://eriksvensen.wordpress.com/2017/12/12/powerquery-filter-a-table-based-on-another-table-column...

View solution in original post

4 REPLIES 4
apo1979prio
Helper I
Helper I

Table.AddColumn(Step20, "VP"each if (List.Contains({"ZNLD","ZNLM"},[DOC_TYPE]) and 
    List.Contains({"500","699","800","999","PT5","PT6","PT8","PT9"},Text.Middle([#".NIF_Venda"], 33)))  then "E"
else if (List.Contains({"ZNLD","ZNLM"},[DOC_TYPE]) and 
    not(List.Contains({"500","699","800","999","PT5","PT6","PT8","PT9"},Text.Middle([#".NIF_Venda"], 33))))  then "P"
else if List.Contains({"ZTOP","ZREP","ZG2P","ZGCP"},[DOC_TYPE]) then "F"  //Frota
else null)
v-chuncz-msft
Community Support
Community Support

@Anonymous ,

 

You may just change to List.Contains in the Formula Bar.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

"List.Contains" was the correct solution.
But in my case it works only with a List and not with Parameter !

 

1.) I still used my List "List of Basen"

2.) I applyed the "Filter" as en extra M-Query statement via "Advanced Editor" in which I refered to the "List of Basen"

#"Filtered Rows7" = Table.SelectRows(#"Filtered Rows2", each List.Contains(#"List of Basen", [Basis])),

PQ_5.png

 

This blog post helped solved my problem : 

https://eriksvensen.wordpress.com/2017/12/12/powerquery-filter-a-table-based-on-another-table-column...

Anonymous
Not applicable

Sorry for the late reply.
You suggested "List" as solution. But as far as I understud List.Contains will filter en existing list for specific values.

 

But what I want is to filter a table with values from a (Parameter)List which is embedded into a parameter called "ParBasen"

XXX99.png

Cheers

Alex

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.