cancel
Showing results for 
Search instead for 
Did you mean: 
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

6 REPLIES 6
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

lbendlin
Super User III
Super User III

I think you are missing the filter keyword.

 

Should be like this:

 

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

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 III
Super User III

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.

WouterV
Frequent Visitor

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
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors