Every day, while running the Supply-Chain at Cuisine Solutions for one of our plants, our 3rd party warehouse emailed me a copy of the daily raw material inventory report within an Excel file.
I’d manually save the file to a SharePoint document library, rename it, copy its contents, and append the data to a master data excel file in a different document library.
The data within the emailed Excel file did not come in a table, it had the same column count for each file but dynamic number of rows based on the changes in inventory. I added a date column to the file and manually populated today’s date for each row.
The master Excel file’s data was within a table, and had all the same columns as the dynamic file including the added date column. It connected to the Power BI service for reporting the inventory status daily to several stakeholders.
The goal is to automate this daily task using Automated Cloud Flow and is accomplished with two separate flows. Once the automation is setup the next parts demonstrate how to connect Power BI to the master data file and then manage the data refresh in Power BI automatically.
- First, save the Excel file to a SharePoint document library when it’s email to you, and rename the file to include today’s date.
- Copy the dynamic contents of the Excel file to a master data Excel file. While doing so, add a date column and populate it with today’s date.
- Connect Power BI to the Excel dataset.
- Manage the data refresh in Power BI.
Click on the links below to go to each blog post in the series:
Series 1 of 4: Copy Email Attachment to SharePoint and Rename-Click Here!
Series 2 of 4: Copy Excel Content (Dynamic) from Excel file to Another-Click Here!
Series 3 of 4: Connect Power BI to Excel-Click Here!
Series 4 of 4: Manage Power BI Data Refresh-Click Here!
