"End to end" refresh

[smiley1]

Hi folks

I am not sure if this is the right part of the forum for this or if this is even possible. I am not asking for someone to tell me how to do this, I could just really do with some guidance as to whether something exists that would allow to the below to happen. I can then do the work.

We have a number of dashboards in Power BI, these are refreshed weekly. This is currently a manual process taking around 3 hours each dashboard which is eating into time that could be spent doing something more productive. The process to update the dashboards is very defined and clinical ie it is something that in my mind could be automated however because the tasks cover multiple applications I am not really clear whether there is one single "hit a button and leave it" way to automatically do the refresh. I have listed the process below. This is a standarised process.

1) Data files (xlsx) received via email or uploaded via Kahootz

2) Data files are moved into a folder either on a local pc or on Onedrive

3) Data files are opened within Excel, header names from a template file are superimposed where the existing headers are and maybe other minor data manipulation which can easily be covered with some VBA.

4) Data files are then imported into SQL server ( via right click, tasks)

5) Script is ran to transform data in SQL server.

6) Data output in csv.

7) csv file is imported into Power BI and report refreshed.

Can the above be automated by one single application? I appreciate there will be setting up etc to do even if it can be automated. Any pointers or sugggestions would be really appreciated.

[smiley1]

So... a very belated update from me nearly 2 years after I posted my opening question in the hope that this update will help other users.

This is how we automated the process:

1) Data comes in as csv files through a data ingest stage that the client drag and drops into our online portal (via Azure)

2) In Azure data factory I have set up pipelines and dataflows to cleanse, process and standardise the data, together with amending master tables with new data sets. Outputs to csv files and master SQL database. This is no easy task owing to the complexities and variety of the differnt data feeds but once the principle is up and running it can be used and adapted to the other data sets.

3) The PowerBI report is pointed to the 3 x master sql databases in Azure using row level security to ensure the relevant dataset is visible to the relevant users.

4) Yes I can confirm that other than the initial set up process it is completely automated, now saving our company a huge amount of time!

Any questions please let me know.

[Anonymous]

The question that needs to be asked is why you are doing all of those steps in the first place?  Apart from receivng the file and having it placed for Power BI to find, is there a reason that the manipulation being done in Excel VBA can't be done in the import section of Power BI using Power Query?

My expectation is that you could set up an email to receive the file, use Microsoft Flow to save the file into a network folder, then have Power BI use its Power Query to import the data as is, then perform similar operations to what is described.

[smiley1]

That is a good question. The data quality of the files we receive determines the work that needs to be done within Excel / VBA before the file is ready for processing in SQL. It would be impossible to list at the things that may need to happen to an Excel file before it is ready for an import into SQL not because I don't want to but because there are so many different permuations of data cleansing and data scenario creation that may exist or be needed in the data sets we receive.  I think that programmability to make it do what you want may be the strength of something like Excel against something like Power Query.