Forum Discussion
"End to end" refresh
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.