none
One Parameter with Multiple Values RRS feed

  • Question

  • I have a query that filters by 3 distinct parameters, e.g. in the editor this looks like:

    CONTAINS P1 OR

    CONTAINS P2 OR

    CONTAINS P2

    P1 is assigned value A, P2 = B, P3 = C

    Instead of having 3 distinct parameters, is there a way to have one parameter containing for example comma-separated values, e.g. P1 = 'A,B,C' and my filter just has 'CONTAINS P1'

    I would ideally want to add and remove values without changing the query

    Thanks in advance!

    Thursday, November 2, 2017 3:48 AM

Answers

  • One option would be this:

    let
        Parameter1 = "Apple, Pear",    
        Source = #table({"Text"}, {{"Apples"}, {"Pears"}, {"Banana"}}),
        #"Filtered Rows" = Table.SelectRows(Source, each List.AnyTrue(List.Transform(Text.Split(Parameter1, ","), (list)=>Text.Contains([Text], Text.Trim(list)))))
    in
        #"Filtered Rows"


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Thursday, November 2, 2017 6:57 AM
    Moderator

All replies

  • One option would be this:

    let
        Parameter1 = "Apple, Pear",    
        Source = #table({"Text"}, {{"Apples"}, {"Pears"}, {"Banana"}}),
        #"Filtered Rows" = Table.SelectRows(Source, each List.AnyTrue(List.Transform(Text.Split(Parameter1, ","), (list)=>Text.Contains([Text], Text.Trim(list)))))
    in
        #"Filtered Rows"


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Thursday, November 2, 2017 6:57 AM
    Moderator
  • Hi Sham69,

    In advance, sorry I misunderstand your situation.

    Why do you want one parameter including three values in it?
    What you want is possible, but I suppose it is NOT recommended.
    Or you are using optional parameters? 

    Regards,

    Ashidacchi

    Thursday, November 2, 2017 8:37 AM