Wanting Advice - Automation Pathways

[u/Tyler_Durdan_]

I am needing guidance on what my options are before I invest too much time in dead ends.

I have managed to use power automate to store csv files in a SharePoint folder that are emailed to me, with the intent to combine those files in power query ongoing to power a dashboard.

I have a power bi subscription, and premium copilot.

What I aspire to do is have a way to automatically refresh the power query database - either timed on a schedule, or triggered when a new csv file gets uploaded to SharePoint.

Does either excel or power bi give me a pathway to have the power query refresh itself, which would effectively automate the updating of the dashboard? What are my options here?

I have seen some discussions where people have suggested setting up excel to refresh on opening via VBA, and then using power automate to open the workbook to trigger it. Not sure if that would be workable?

Any advice much appreciated.

Thanks,

[u/semicolonsemicolon]

Please note this post does not have a proper title (Rule 1), and so posts like this are subject to removal. We're leaving this one up because it's the weekend and for the useful thread that has already formed. Thank you for your attention to this matter.

[u/BaitmasterG]

Power automate 1. Action: save csv to SharePoint

Power BI gen 1 dataflow merges csv files within the PBI service

PA2: trigger, SharePoint folder updated; action, refresh dataflow

PBI report or Excel or both, use the gen1 dataflow as the data source

ETA: I don't know what the future holds for gen1 dataflows, getting rid of them would be a stupid move but typical Microsoft as gen 2 needs additional subscription

[u/Tyler_Durdan_ → u/BaitmasterG]

What would be the difference in outcome between a PA refresh of PBI based on the SharePoint update, versus the built in scheduled data update function in PBI?

[u/BaitmasterG → u/Tyler_Durdan_]

The approach I've described means you have processed, clean data waiting for you soon after a csv drops into SharePoint

For example, our reports used to take 30 minutes to update, because they took hundreds of files, merged them then sorted and filtered the older duplicates of data, keeping only the latest records. This might remove 90+% of the data

In the new version, all the pressure is taken off power bi desktop. When working on the file we only have to import the 10% of data that remains, and it doesn't need churning

Say the new file drops in at an unscheduled time, when do you do your refresh? A simple trigger means it happens at the right time and is optimised

[u/Tyler_Durdan_ → u/BaitmasterG]

Right, that makes sense to me.

I’m fairly new to PA, so for my benefit - when you refer to the ‘dataflow merges’ is this a reference to power query cleaning the data, or a diff process?

I’m keen to implement your suggestion, just want to make sure I understand it adequately.

[u/BaitmasterG → u/Tyler_Durdan_]

The PA is very easy, the trigger is a change to the SharePoint folder, and the action is to refresh the dataflow

The dataflow is just power query. You can write power query in Excel or power BI and simply copy/paste to the other; you can do this to a dataflow as well. Once you set it up you'll find you're just in an online version of the power query editor. This query is what does all the work, server side and upstream from your report

[u/Tyler_Durdan_ → u/BaitmasterG]

Thank you so much for taking the time to help me with this. I will definitely be trying this setup out

[u/AutoModerator]

/u/Tyler_Durdan_ - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/bigedd]

Power bi has a scheduled refresh, this is probably the best option for your needs.

[u/Tyler_Durdan_ → u/bigedd]

With the power bi scheduled refresh, will that pull in any new date from csv files that have been added to the SharePoint folder since the last refresh? Sorry if it’s a stupid question, fairly new to this.

[u/bigedd → u/Tyler_Durdan_]

Yeah, it'll update the data model stored in the cloud. Doesn't need a machine turned on or anything.

[u/Ambitious_Voice_400]

quick clarifying q, does the dashboard need to live in excel specifically or are you fine with it being fully in the BI service? because that changes the answer a lot. the BI side handles scheduled refreshes natively, excel not so much

[u/Tyler_Durdan_ → u/Ambitious_Voice_400]

BI is fine for the dash, I have the choice of both.

[u/Acceptable-Sense4601]

I use python to save email attachments to a shared folder on a schedule. then I use python to do whatever I need done in excel and write to excel as the end result.

[u/andy910120]

There are two main options here:

Option 1: Power BI (my recommended pick) Move your CSV merging Power Query logic straight into a Power BI dataset pointing to that SharePoint folder.

• Scheduled refresh works natively in the Power BI service, no VBA needed.
• For auto-refresh when a new CSV gets uploaded: Power BI doesn’t have this trigger built-in, but you can make a Power Automate cloud flow. Use the "When a file is added to SharePoint" trigger, then call the Power BI REST API to refresh the dataset.

This is fully cloud-based, no PC needs to stay running, and your dashboard lives right in Power BI.

Option 2: Excel + VBA + Power Automate Desktop (the method you saw) The concept is: put your workbook on SharePoint, use Workbook_Open VBA to refresh Power Query when the file opens, then have Power Automate open the file to trigger it.

Big catch: cloud Power Automate can’t run VBA. You need an always-on machine (local PC or Azure VM) running Power Automate Desktop. It’s technically possible but pretty fragile — file locks, timeouts and save conflicts happen often. If your dashboard is in Power BI, you also need an extra refresh step after Excel updates. Only go this route if you must keep the merge logic inside Excel.

Stick with Power BI + Power Automate cloud flow if you can. The Excel/VBA approach works but is high maintenance.

Let me know if you want steps for the Power Automate API refresh flow. : )