How to pass Multivalue parameter using Power automate

[Anonymous]

Hi,

    I am working on a requirement where i need to create a flow by getting the parameters from the SQL table and pass it to Power BI Paginated report and export it to CSV or excel and email to users. Here when i pass single value it is working fine but when i pass multiple values to single parameter it is not working so please someone help me to achieve this scenario.

Thanks

Vivek N

@Rhiassuring @Pstork1 @DamoBird365 @chrisingh42188 @YukaHosomi 

#Powerautomte #PowerBI #Paginatedreports #export #SQLServer

[Pstork1]

What action are you using to pass the parameters?  

[Anonymous]

I am using the below steps to setup the flow. Here i am getting parameters from a SQL table and pass the output values to the Power BI paginated report parameters using add dynamic content 

[Pstork1]

Then it should just be a matter of repeating the parameter name with each value.  Since that isn't really possible unless you know how many parameters you have you should use an Apply to Each to create an array of values with the parameter name and each value.  Repeating the parameter name as necessary.  Then click on the icon above and to the left of the entry to switch it to List mode.  Then insert the array. At least that is the way I think it will work.

[Anonymous]

Thanks for the response. For example parameter 3 might be having lot of values so you want me to repeat it as many times? Then it will be very manual way of repeating it so many times? Can you brief me with an example please 

[Pstork1]

1) Initialize an array variable

2) Add each parameter to the array variable as a JSON record something like this

{
    "InternalParameterName": "ParameterValue"
}

3) For the parameter with multiple values do that in an Apply to Each Loop creating one record for each value

4) Insert the array variable into the action as I showed in my previous response.

[Dev_S]

Hi,

I am having the same scenario, where combination of single and multi valued parameters need to passed to Paginated report. I used the same logic used "append to array variable" and passed the values to actions as you  showed. And other parameters directly from SQL table's column.

But the issue is I'm getting extra square brackets to the array variable, so it is failing.

For example, 

Input Parameters for Paginated report :

Parameter 1 : Center agency

Parameter 2 : ABCD

Parameter 3 : ABCD XXX – 1234 , ABCD yyy – 5678

[

  {

    "name": "Parameter 1",

    "value": "Center agency"

  },

  {

    "name": "Parameter 2",

    "value": "ABCD"

  },

[

  {

    "name": "Parameter 3",

    "value": "ABCD XXX – 1234”

  },

  {

    "name": "Parameter 3",

    "value": "ABCD yyy - 5678"

  }

]

]

Can you please help me in resolving the issue? I tried to use string append also its not working.

Thanks is advance !!

Devi

@Rhiassuring @Pstork1 @DamoBird365 @chrisingh42188 @YukaHosomi 

#Powerautomte #PowerBI #Paginatedreports #export #SQLServer

[Pstork1]

Can you show a screenshot of what you are using for your append to array variable and how it is positioned in your flow?

[Dev_S]

Hi 

Below are my steps:

  1. After fetching the rows from SQL, passing it to "Apply each" loop.

  2. Splitting the multi-valued parameter (Partner) and appending using "Select" function.

  3. Passing the output from previous step and direct values from SQL table (parameter1,parameter2) to "Export File to paginated report" task.

 4. Then an Email task to send the downloaded file.

Thanks!

[Pstork1]

The select is your problem.  Each time you use the select the output is an array.  So you are appending an array to an array, not appending records to an array.  What you want is to set the array to the Union() of the existing array and the output of the select.

[Dev_S]

Hi,

 I am new to power automate. Could please explain how to do the union() of select output to existing array? 

Thanks.

[Pstork1 · Accepted solution]

Its a little bit tricky since you can't a variable can't reference itself.  But consider this scenario.  I have three variables all of type array.  VarArray1, VarArray2, VarArray3.  1 and 2 both have arrays of records in them.  3 does not.  So in a Set Variable action for VarArray3 i do the following expression in the expressions tab of the content dialog.

union(variables('varArray1'),variables('varArray2'))

That combines the two arrays into a single array.  Then I do another set variable to copy varArray3 overtop the values in varArray1 and set the value of varArray2 to empty.  If I do that in each loop I will end up with a single array in varArray1 that has all the values from all the arrays along the way without the extra square brackets.

[Dev_S · Accepted solution]

Hi,

This worked like a gem. After select i added an array variable and used union() to merge the other parameters with select output.

Thank you so much. 😊😊 

Thanks!!

[Anonymous]

Hi,

I am using a flow to download the paginated report for different parameter combination that is fetched from a SQL table. The downloaded report is emailed to different recipients. Now i have a new requirement to zip the file after download and email the same.

Is there any option or solution for zipping the excel report?

Thanks in advance for your help!!

@Dev_S @Pstork1 @ScottShearer @MarconettiMarco @Hardesh15 @eliotcole @annajhaveri @takolota @Shujaath_Khan 

[Pstork1 · Accepted solution]

I think we've discussed this on a different thread. There is no Zip action available in the base product.  You'll need to use a third party connector like Encodian.