Creating Templated Word Document Reports through Power Automate in Power BI

[Pete_HSINFRA]

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

Any thoughts would be appreciated.

Pete

[lbendlin]

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?

[Pete_HSINFRA]

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.

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,

This is the error message I get:

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

[v-yiruan-msft]

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