Dynamic query params with multi select (Cannot convert a value of type List to type Text)

[BIanon]

Hello Community,

I am working with a Direct Query model where I allow the end user to select some values in slicers and pass these slicers to a query as a query param.

I have one param with numerical Id's which is working fine with multi select:

Implemented in my primary query like this:
Categories = CategoryParameter,

SQL:   AND V.Id IN (" & Categories & ")

I'm trying to achieve the same thing for a param that contains strings.

Implemented like this:

Brand2 = "'" & Text.Combine(List.Transform(Text.Split(BrandParameter2, ","), Text.Trim), "','") & "'",

SQL:   Brand IN (" & Brand2 & ")

The Brand2 step produces this:

The preview also constructs the correct query and returns the correct result.

When I then load it in and bind a Brand columns to my Brandparameter2, I get hit by the following error if I select something in the slicer, it's working with the default value if I don't select anything:

I hope this description is detailed and understandable, if not I will try to get back with any other info you might need. 
Not sure how to fix this issue

[BIanon]

I actually tried implementing this without luck, but I must have messed something up because now I just pasted your version in and it worked as intended.

Thank you!

[Anonymous · Accepted solution]

Hi @BIanon 

Based on your description, you can refer to the following link about multiple selections in dynamic paramaters.

https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-dynamic-m-query-parameters#bind-the-...

You can try to add a new paramater in advanced editor.

e.g 

let 
   selextedvalue=if Type.Is(Value.Type(BrandParameter2), List.Type) then  
            Text.Combine({"'",Text.Combine(BrandParameter2,"','"),"'"}) else 
    Text.Combine({"'",BrandParameter2,"'"})
in
.......

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.