How to populate the fields of an Excel template with data from Power BI (or is there another way)?

[D_PBI]

Hi all,
Please see the below Excel template:

The above template is currently populated through an end-user manually looking-up values within our D365 solution and then copy/pasting those values into the template.

I've been asked to populate the above Excel template with data from our D365 solution through an automated manner. I use Power BI, hence posting here, but am open to other ideas (maybe Power Apps or Power Pivot in order to achieve this).

I can gather and maniuplate the data in Power Query and/or DAX, this isn't a problem. However, I do not have any idea on how to populate the Excel template.

Does anyone know how to achieve what I need to do?   Has anyone been tasked with the same and were you able to achieve the outcome and how (links and/or videos are welcome)?

Some initial questions/thoughts...
1) Is there a way to populate Excel from witin PBI (Desktop or Service), whether that is pushing from PBI or pulling from Excel?
2) Would doing all the modelling work in PBI's desktop Power Query mode and then connecting to that same PQ model from Excel itslef help with things?
3) What about if I forgot about PBI altogether and tried to complete all using Power Pivot for Excel - would this work?
4) WIs there another tool within the Power Platform that will help achieve my aim - maybe Power Apps or something?
5) Can anyone suggest a visual/technique to complete the exact same layout in PBI and then export the report's layout as Excel so it would like exactly like the Excel template and therefore there is no need to do any mapping of data, they would simply used the exported PBI data (providing it stays in the exactly the same layout).

At present, I am attempting to use matrix visuals (as matrix visuals allow for right-click copying of data) and position each visual to look like the Excel template. It's not good and I wonder if what I am attempting can be done, if someone knows better please tell me as I'm just searching the net on how to do this.

Thanks.

[Anonymous]

@D_PBI Hi, were you able to solve this ? Trying to do something similar 

[D_PBI]

@CNENFRNL  - thanks for your response - much appreciated. Unfortunately, it seems there are manual steps involved in using 'Power BI Exporter'. The PBI Exporter works from within Desktop but it will be the end-users, those that will use the report, won't have access to Desktop, plus they will not want to take all the manual steps of exporting, navigating etc...

Whether I am able to create the report in PBI and push to the Excel template, or create the report in Excel (if that is the only route), the end-user will be entering a Reference Number in a filter visual in which the data is restricted to that Reference Number.

At present, it sounds like I cannot layout the report to mimic the specified Excel template (with rows and columns being merged as per template). Even if I could mimic the template, I couldn't export the PBI report to be any format and retain it's specifc layout (I expect the laid visual will just become loads of mini tables once exported).

I appreacite that maybe you cannot push Power BI data to an Excel template, but I was hoping that you could pull Power BI data from each individual Excel field if Excel could utilise the PBI model somehow?

Any further ideas anyone?  Can Power Apps do what I need?

Thanks.

[CNENFRNL]

As long as rows of your template are fixed, use a "mosaic" way to achieve your goal; split the template into some small tables with underlying queries consuming data from the same source as PBI uses.

[CNENFRNL]

AFAIK, no way to merge cells in PBI, thus it's impossible to populate your template directly; but there's such a workaround, I believe.

1. Export a proper dataset from power query this way,
   https://www.biinsight.com/exporting-power-bi-desktop-data-using-power-bi-exporter/
2. You can preset worksheet formulae in your template, which comsume the exported dataset; or use VBA to munipulate the exported dataset.