Exporting Multiple Reports from one dataset using a filter (cost centre) & also sending an email of the exports to the relevent personnel

[Shaunbuckleyy12]

Hello, I have a client that has a request that sounds simple but is proving very difficult to implement in Power Automate. 

Essentially the client wants to export a payroll report for EVERY cost center every 2 weeks, save each of the exports down in a location separately & finally, send an email to the Cost Centre Managers with the exports attached. Only certain Managers can view certain reports so this also needs to be dynamic in the flow. 

I cannot find a solution anywhere on youtube, does anyone know if this possible? The bit I am struggling with, is filtering the export to only show data for each specific Cost Centre. I am not that experienced with Power Automate so could be missing something. Thanks Guys. 🙂

[lbendlin]

What is a "report"?  Excel, Power BI, something else?

[Shaunbuckleyy12]

Just excel is fine for the exported reports. its a really simple table with just some payroll data for staff.

The ideal solution would be to connect to the database where the payroll data is stored into Power BI or Power Query in Excel if that is easier and then export the multiple excel reports from Power BI/Excel every 2 weeks using Power Automate. 

I think I need to use the 'loop' and 'do until features' but not 100% sure how this works.

[lbendlin]

You would create an array with your recipients and their filters. Then you iterate over the array and run the below action with the filters applied accordingly. Note: There is no Excel export option.

If you want Excel instead you would need to run a DAX query against the dataset and then stuff the results into an Excel file that you create on the fly.

[Shaunbuckleyy12]

Yeh I was thinking I would need to use the run a query against the dataset but the area that I am really unclear on is the 1) how do I run the data to show everything for only a specific cost centre and 2) save down a cvs/excel of each of the different reports with the different cost centers. 

I am also not too sure how to make sure I send the emails to the correct managers depending on the cost centre they manage too. 

would you be free for a quick call tomorrow perhaps? Or when you are free to discuss? 

[lbendlin]

Please remember this is a community forum where users help users.  If you need professional consultancy there are plenty of Microsoft partners available to assist you.

[Shaunbuckleyy12]

No problem lbendlin, will try and get in contact with one of them, thanks for your help 🙂

[Shaunbuckleyy12]

@lbendlin, been searching for help the past few days but the fees I am getting quoted are out of my client's budget. I wanted to ask you if you wouldn't mind having a quick look at the problem worded in a different way to see if it sounds like it may be an easy flow to create or if it is even possible (if you don't mind of course). 

I think it is easier to understand what is required by me attaching the files. If you look at the 'Payroll Data - DCC Demo' file and the first tab specifically, this is how the Power BI Report will look, it will be a simple table containing all of the columns in that spreadsheet for the Payroll Data.

I then want to export out similar excel/csv's to the 2 files I have also saved down which just filters all the data in the report by Cost Center and makes a separate file for each CC. For this example, I only included 2 CC's but in reality there is nearly 300 so I need Power Automate to create 300 separate files automatically (every 2 weeks). 

Does it sound possible to you? 🙂