Forum Discussion

tiprarakras's avatar
tiprarakras
Regular Visitor
2 years ago

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

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 

General Comment
Need Help
Tutorial Requests

5 Replies

  • jennratten's avatar
    jennratten
    Super User
    2 years ago

     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's avatar
      tiprarakras
      Regular Visitor
      2 years ago

      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's avatar
        jennratten
        Super User
        2 years ago

        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?