Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
WouterV
Frequent Visitor

Power Automate "export to file for paginated reports" multi value

Dear community,

 

I've built a Power Automate flow that uses a SharePoint action to generate a PBI paginated report. Now, one of the parameters is multi-value and I'm having issues to pass this variable from Automate to PBI. If I pass the array object, I get the error "The parameter(s) "<param>" has invalid values.". After that, I tried passing as string, using colon as well as semi-colon, but kept giving the same problem. How does Power BI expect the parameters to be passed?

 

Best regards,

W

2 ACCEPTED SOLUTIONS
v-deddai1-msft
Community Support
Community Support

Hi @WouterV ,

 

I also suggest you to create a post in power automate forum. And in power bi you should set Allow multiple values property for the parameter:https://docs.microsoft.com/en-us/power-bi/report-builder-parameters#bkmk_Report_Parameters

 

And if you need to Pass a report parameter in a URL , The format for a multi-value parameter is to repeat the parameter name for each value. please refer to the example in this document: https://docs.microsoft.com/en-us/power-bi/report-builder-url-pass-parameters#additional-examples

 

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

 

Best Regards,

Dedmon Dai

View solution in original post

Thanks for your input. To elaborate on your comments:

  • I created the exact same post on the Power Automate forum at the same time I created the post here (https://powerusers.microsoft.com/t5/General-Power-Automate/Power-Automate-quot-export-to-file-for-pa...). Though, not a single response yet there, seems the Power BI community is more active 🙂
  • "Allow multiple parameters": this is indeed selected in the PowerBI Report Builder. I can run the report without a problem from PBI Report Builder and from the PBI service.
  • "Parameter repetition": I tested this and that seems to work, saldy there's a but... Since you don't know how many options a user will select beforehand, you need to create as many parameters as there are options. As value you need to check if this n-th option was selected, if not it should take one of the values that was selected. It becomes a bit a dirty expression, but at least it's a solution. Maybe I'll post the expression that I come up with in the Power Automate forum and see if there is a cleaner expression possible. The PBI side of the story seems to be resolved at least, thanks for that!

View solution in original post

7 REPLIES 7
Anonymous
Not applicable

Initialize an empty array variable

For each loop for parameter values

in loop:

1) compose:

{
"name": "paramname",
"value": "@{items('Apply_to_each')}"
}

2) append to array variable

Exit loop

In PBI action, switch param input to array

ParameterValues is your array variable

 

 

 

v-deddai1-msft
Community Support
Community Support

Hi @WouterV ,

 

I also suggest you to create a post in power automate forum. And in power bi you should set Allow multiple values property for the parameter:https://docs.microsoft.com/en-us/power-bi/report-builder-parameters#bkmk_Report_Parameters

 

And if you need to Pass a report parameter in a URL , The format for a multi-value parameter is to repeat the parameter name for each value. please refer to the example in this document: https://docs.microsoft.com/en-us/power-bi/report-builder-url-pass-parameters#additional-examples

 

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

 

Best Regards,

Dedmon Dai

Thanks for your input. To elaborate on your comments:

  • I created the exact same post on the Power Automate forum at the same time I created the post here (https://powerusers.microsoft.com/t5/General-Power-Automate/Power-Automate-quot-export-to-file-for-pa...). Though, not a single response yet there, seems the Power BI community is more active 🙂
  • "Allow multiple parameters": this is indeed selected in the PowerBI Report Builder. I can run the report without a problem from PBI Report Builder and from the PBI service.
  • "Parameter repetition": I tested this and that seems to work, saldy there's a but... Since you don't know how many options a user will select beforehand, you need to create as many parameters as there are options. As value you need to check if this n-th option was selected, if not it should take one of the values that was selected. It becomes a bit a dirty expression, but at least it's a solution. Maybe I'll post the expression that I come up with in the Power Automate forum and see if there is a cleaner expression possible. The PBI side of the story seems to be resolved at least, thanks for that!
lbendlin
Super User
Super User

I think you are missing the filter keyword.

 

Should be like this:

 

  {
    "filter""table/column in ('value1','value2')"
  }

Thanks for clarifying, though still doesn't seem to work. To edit the code directly, I had to export, edit the json, and import again. It fails on the import step, without giving a proper error message. Given this is the only thing I touched in the json, either it's still my syntax or this type of action won't accept "filter"?

The value I put:

{
"filter": "DataSet1/Param1 in (@{variables('Categorie')})"
}

 

Were you able to get a similar flow working this way?

lbendlin
Super User
Super User

Use the same syntax as here.

Filter a report using query string parameters in the URL - Power BI | Microsoft Docs

Power Automate will then translate that into the JSON format required by the API call.

Thanks for the suggestion, but still throwing the error. Either I'm not applying it correctly, or this syntax is not possible in the "export to file for paginated reports" action. Let me describe what I have done now:

  • Trigger: SharePoint - For a selected item. I used several parameters here, 1 of them allowing selecting multiple values, let's call it Param1.
  • Action 1: Initialize a variable, Categorie, with expression "join(triggerBody()['text_3'],''',''')" and then outside the expression also 1 more single quote on each side
  • Action 2: PBI - export to file for paginated reports. As the parameter value, I put in text "in(", then selected "Categorie" variable from dynamic content, then closed the braces in text ")".
  • Action 3: SharePoint - Create file. Not important in the problem context at hand, just for completeness.

When triggering the flow, I'm getting this in the json:

"value": "in ('Value1','Value2')"
Instead of interpreting this value, I assume it's passing it as string. Would be great if you could tell me how I can pass the parameter value correctly so it'll be interpreted correctly.
Thank you in advance.
Automate_VariableDeclaration.png
 
Automate_PBIParamValue.png

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors