How to automate the refresh of daily excel/csv dat...

Prabhakaran2794 · ‎09-13-2023

Hi Community,

Scenario :

I receive excel/csv files on daily basis with date included in the file name.
The columns numbers and names doesn't change and needs to be appended to existing data.
There are approximately 40000 rows on daily basis.
The data hence would be use for create few reports.
End goal is to visualize this data in Power BI.
Need an automated solution for data something like incremental data update.
The solution must not slow down when the data gets larger.

Can we use MS Fabric to acheive this? Or any other solution including the tool to use would be of a great help. Thanks in Advance!!!

JoeBarry · ‎09-14-2023

Hi @Prabhakaran2794

Do you receive the CSV per email? Below is how I would do this process. Lets say you do receive the CSV per email.

Create a Folder on SharePoint online within a shared site (Not OneDrive) and Name it as you wish
Create a Power Automate flow to get the CSV file from your Email and add it to the SharePoint folder you created https://powerautomate.microsoft.com/en-us/templates/details/f7a46809e53c42108034e56acf83bb79/save-my...
To start, lets add 1 CSV to the new Folder you created in SharePoint
In Power BI use the connector SharePoint Folder and add the Site URL, should look like this https://mycompany.sharepoint.com/sites/Data
Click on Transform Data
Every file that is on this site will appear
You will need to filter the URL column to the folder Path of the folder you created.
When it has filtered, there should be a column Binary, click on the two arrow pointing down, to load the data.
This will create a Transform query and a Table with the CSV data. (If you somehow delete the CSV later, the whole query will break, so make sure it doesn't change name or be moved)
Make the transformations to your data.
If you have more than one CSV currently, add these to the SharePoint folder. Power BI will ingest these and make the transformations automatically
When a new file is added to the SharePoint later from your email, this will be added later when the report refreshes.
When you finish your report, publish it to a Workspace
Set up a Report Refresh https://learn.microsoft.com/en-us/power-bi/connect-data/refresh-data
As it's a SharePoint source, you don't need a Gateway

Hope this helps

Joe

If this post helps, then please Accept it as the solution

Prabhakaran2794 · ‎09-14-2023

Hey Joe.

That was a great solution on how to deal the scenario using Sharepoint. I will consider the solution. But any solution using the MS Fabric would also be appreciated.

Thank you for your precious time. 😊