How to pass multiple selected values from a slicer to Power Query via parameter?

[tiprarakras]

My datasource is a Snowflake stored procedure SP_Calc([Array])

I have a table called ENTITY_ATTR in which I am using a column as a slicer for the user to multiselect preferred values.

I have a parameter called paraEntity on which I have performed Bind to Parameter with ENTITY_ATTR's field.

The requirement is for the user to select value(s) from the slicer, which gets passed to the parameter, which in turn is passed as a parameter/argument to the stored procedure itself.

Here's the m-code for the SP:
let
Source = Value.NativeQuery(Snowflake.Databases("servername.com","warehouse_name",[Role="role_name"])
{[Name="database_name"]}[Data], "CALL DBO.SP_Calc(['"& paraEntity &"']", null, [EnableFolding=false])
in
Source

If all goes well, the stored proc which gets passed to Snowflake will look like this: CALL DBO.SP_Calc( ['selection1', selection2', 'selection3'] )

This works for single slicer selection, but if I turn it to multi select, then it gives the error "We cannot apply operator &  to types Text and List."

Please help with this problem, as it's quite urgent!

@amitchandak @ChandeepChhabra @NotGregDeckler 

[jennratten]

 Hi @tiprarakras - when you make the slicer multi-select, the value of the parameter turns into a list since it is no longer one value.  Your parameter shows it is set as type text and this is confirmed by the error message.  You will need to include some additional steps in Power Query to handle this conversion of the list selected in the report.  Here is a good example:

let
  EntityList = 
    if 
    //check to see if the parameter is a list
      Type.Is(
        Value.Type(paraEntity), 
        List.Type
      ) then 
        //if it is a list
        let
          //add single quotes around each value in the list
          AddSingleQuotes = List.Transform(
              paraEntity, 
              each "'" & _ & "'"
            ),
          //then turn it into a comma-delimited list
          DelimitedList = Text.Combine(
              AddSingleQuotes, 
              ","
            )
        in
          DelimitedList
    else 
      //if the parameter isn't a list
      //just add single quotes around the parameter value
      "'" & paraEntity & "'",
  //generate and run the query
Source = Value.NativeQuery(Snowflake.Databases("servername.com","warehouse_name",[Role="role_name"])
{[Name="database_name"]}[Data], "CALL DBO.SP_Calc(['"& EntityList &"']", null, [EnableFolding=false])
in
Source

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

[tiprarakras]

Hi @jennratten 
First of all, thank you so much for meticulously looking into this!

However I am getting the following error:

'All' is actually the default value I have set for my paraEntity parameter.
Could this be caused by queryfolding=false I have set? I need to have it as false, or power query throws a syntax error at the CALL command. 

[jennratten]

You're welcome!  It's hard to say the reason without seeing the revised script.  I don't think it would be related to query folding = false though.  Can you please send your revised script?

[tiprarakras]

Hi @jennratten 

Please find below my updated script:

let
EntityList =
if
//check to see if the parameter is a list
Type.Is(
Value.Type(paraEntity),
List.Type
) then
//if it is a list
let
//add single quotes around each value in the list
AddSingleQuotes = List.Transform(
paraEntity,
each "'" & _ & "'"
),
//then turn it into a comma-delimited list
DelimitedList = Text.Combine(
AddSingleQuotes,
","
)
in
DelimitedList
else
//if the parameter isn't a list
//just add single quotes around the parameter value
"'" & paraEntity & "'",
//generate and run the query
Source = Value.NativeQuery(Snowflake.Databases("servername.com","warehouse_name",[Role="role_name"])
{[Name="database_name"]}[Data], "CALL DBO.SP_Calc('ABCXYZ', ['"& EntityList &"'], ['All'], ['All'], 'LC', ['22-Jan'], ['All'], ['Actual'], ['All'], ['All'])", null, [EnableFolding=false])
in
Source

Just to add some context - paraEntity is just one of the parameters I am using to take create a slicer out of a table. There are more user-selectable dropdowns bound to other parameters I have created that get passed to the stored proc (but I am hardcoding their value as 'All' for the moment).

[tiprarakras]

@dufoq3 @AlienSx @BA_Pete @jennratten @Ashish_Mathur @lbendlin @rajendraongole1 @ryan_mayu @parry2k @danextian @DataNinja777 @Greg_Deckler @Uzi2019 @kpost 
Please help!