Hi All,
We had a Power BI report which read in a number of excel files and applied a variety of Power Query transformations to make the data legible for visualisations. This process made the report refreshes incredibly slow since everything was being done within it. Our plan was to migrate this to a dataflow to make the report refresh quicker since the transformations now happened outside the report and those refreshses could be scheduled.
However since then, the project scope has grown massively and now they are planning on uploading new data every week (until now it was a single excel file which was replaced weekly) into azure blob storage so instead of seeing a single snapshot of the data we can now provide week on week trends etc.
My question is, when a new excel file is uploaded to the blob storage each week (same structure just a different name which relates to the week e.g datestamp), run the transformations on the new excel file and append these results into what has already been processed? In my head I see the process being as follows:
1. New file uploaded
2. Scheduled refresh opens new file (based on date stamp or most recent date added information)
3. A Power Query transformation runs to format the data (this same Power Query would run each week)
4. This newly formatted data is appended into a table with the previously processed data from the previous weeks excel files
5. Process repeats itself the following week
Is the above feasible (or indeed best practice)? If so does anyone have any ideas where to start or a more optimised approach?
I appreciate this is quite vague and we don't as yet have an example to show, but any help would be greatly appreciated.
