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.
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.
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.