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 ha...
General Comment
Need Help
Tutorial Requests
jennratten's avatar
jennratten
Icon for Super User rankSuper 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
    Icon for Super User rankSuper 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?

    • tiprarakras's avatar
      tiprarakras
      Regular Visitor
      2 years ago

      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).