Creating an automated work flow for MS Excel and MS Power BI using MS Power Automate

[Konain10]

Hi, 

Being a Business Analyst at my company, I am required to provide Flash Reports to senior leadership on a daily basis summarizing business performance across our different locations. 

As part of this process, I receive daily updates in an Excel file from all our locations (i.e., 1 Excel file containing the update per location). I then manually extract the right data and input in another Excel file before emailing it to the senior leadership.   

I recently learned about MS Power Automate. I am interested in knowing if it's possible to automate the above process using MS Automate. More specifically, I want to know if I can automatically extract data from the different Excel files and input into this other Excel file and then have this one Excel file automatically emailed to senior leadership on a daily basis? 

Let me know if any part of my query is unclear. 

Thanks, 

Konain 

[LarryAlx]

Hi @Konain10 

Here are some suggestions if you have access to a MS SharePoint:

1. You may want to
   1. Have a power automate trigger which moves the file from your email to a SharePoint folder, assuming you are receiving the files through email.
   2. Then have Power Bi pick up the excels from there.
      1. Power Bi can do the data prep work, modeling, dashboard, etc. which should remove your manual work.
2. Or, better yet,
   1. give the different locations access to upload the files to the SharePoint folder
   2. Then have Power Bi pick up the excels from there and have Power Bi do the data prep work, modeling, dashboard, etc. which should remove your manual work.

You may also be able to substitute the SharePoint with another connector, just open up Power BI --> Get Data --> More to see all the options available, there may be something else you could use.

Hope this helps.

[David_MA]

My idea is similar to @LarryAlx , however depending on your environment and your licensing, I would create a Data Flow in Dataverse (An overview of dataflows across Microsoft Power Platform and Dynamics 365 products - Power Query | M...) with a scheduled refresh. You would then save the Excel files with the same names to the location you selected when you set up the Data Flow. You can then use the Power Query features of the Data Flow to transform your data.

Then you could set up a schedule flow to grab the data from the Dataverse table, save it to a CSV table, create a file, then attach the file to Outlook and e-mail it. With some extra effort you can create an Excel spreadsheet instead, but creating a csv file is easier.