cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Pete_HSINFRA
Frequent Visitor

Creating Templated Word Document Reports through Power Automate in Power BI

Hi,

 

I've been asked if I could create Client reports on live Projects we are working on, using Power Automate in Power BI.

 

These are fairly high level reports containing 2 x tables - Risks & Issues.

 

I'm happy with creating a Word Document Template using the Developer Tab on each table. In Power Automate I can create RiskLine & IssueLine Variables and use parallel filter and array functions in Power Automate to separate Risks and Issues from each other, ready to populate in their own tables in the Word Document Template. I filter using a Combined Risk & Issue Category in Power BI, which splits into Risks & Issues.

 

This is where I come across a problem. When I use the "Populate a Word Template" step in Power Automate, any filtering I have down in previous steps is ignored.

 

The Risk Table uploads all the Risk Information correctly (See below); however as you can see the Issue Category & Title columns, still pull through in the Risk Table (Conversely, the Issue Table pulls through all the Issue Information correctly as well as the Risk Category & Title columns).

 

Pete_HSINFRA_0-1631288790618.png

 

Any thoughts would be appreciated.

 

Pete

3 REPLIES 3
lbendlin
Super User
Super User

The contents of the fields assigned to the Power Automate visual in Power BI are independent of any filters you may apply to other visuals on that page  (they are impacted by page level and report level filters though).  When you connect the visual to a flow it should auto generate the "Apply to each" loop - have you modified that part of the Power Automate flow?

Hi,

 

This is the flow I've currently got.

 

I initialise 2 x Variables - one for Risks & one for Issues - in parallel. 

 

Using "Apply to Each", I use a condition to filter on respective Risks & Issues and then append the result to an array.

 

Pete_HSINFRA_0-1631630434733.png

 

 

Pete_HSINFRA_1-1631630525987.png

 

I then add steps "Populate a Word Template" and create a file, which ignores the conditions imposed for appending the arrays, which is what is stumping me.

 

If I use "Apply to each" and attempt to "Populate a Word Template and then "Create a File", as below,

 

Pete_HSINFRA_2-1631631294998.png

This is the error message I get:

Pete_HSINFRA_3-1631631334823.png

 

I've tried every which way and cannot get the individual tables to filter out the respective Risks and Issue from each other.

Hi @Pete_HSINFRA ,

From the error message, it looks like the result expects an array but actually gives a string. Please recheck your expression to see if it matches the condition. There are some links here that have the same error message as yours, you can check the solution in there. Hopefully they will help you solve the problem as well.

Apply to Each failed Error

How to convert string to an array in Power Automate

Power Automate Apply to each error

Power Automate: Fixing the error on empty instance of a multiple values Managed Metadata field
If the above still doesn't solve the problem, perhaps you can create a post in the Power Automate forum for a more professional answer.

Best Regards

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

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.