Ideas on multiple excel files --> Merge --> Power BI

[efowler]

Hi,  I have a situation that I am not sure I have the best approach..  Looking for comments and thoughts from the experts here.

Cliff Notes

- I have a daily call center report that is emailed automtically via excel. 

- The excel data shows call center info from the prior day

- The excel file has three tabs, the only data I need is on the second tab 

- My goal is to automated saving the excel file to sharepoint (via Power automate) and connecting Power BI to aggregate the data

- The excel file is delivered daily and currently only goes into a Sharepoint Online document folder 

This might be more of a Power Automate question before being a Power BI question but wanted to see if anyone had thoughts. 

Ideally I find a way to extract the data in the columns from tab 2 of the excel file and write that new data to a new row in a SPO Excel Table.  

[Anonymous · Accepted solution]

Hi @efowler ,

You can follow these steps to get it:

1. Create a Power Automate flow: Create a flow in Power Automate to transfer the local Excel file to SharePoint. You can use the "Recurrence" trigger to schedule the flow and the "Create file" action to upload the file to SharePoint.

   Solved: power automate from excel to sharepoint document l... - Power Platform Community (microsoft....

2. Connect Power BI Desktop to SharePoint: In Power BI Desktop, click on "Get Data" and select "SharePoint Folder". Enter the URL of your SharePoint site and authenticate with your credentials.

   Power Query SharePoint folder connector - Power Query | Microsoft Learn

   Quick Tips: Connecting to Excel Files Stored in SharePoint Online from Power BI Desktop - BI Insight

3. Load the specific sheet: After connecting to the SharePoint folder, you'll see a list of files in the document library. Select the Excel file you want to use, and in the "Navigator" window, choose the specific sheet you want to connect to. Click "Load" to add the sheet to your Power BI report.

Best Regards