I am currently working on a power bi dashboard that uses an excel file as a data source. I want to auto populate the excel file with new values from existing excel reports each day.
In the source file there are several sheets each with several columns (date, inventory, capacity of transportation, etc.)
My objective is to get specific column values from each excel report corresponding to today's date and add them to the corresponding columns in the source file (while adding a new row corresponding to today's date).
I have checked several articles on how to use Microsoft Azure and VBA but I didn't find exactly what I am looking for. Especially that the reports need some cleaning since they contain headers, titles, comments, etc.
Any ideas?
Quote:
Both Power BI and Power Query offer the opportunity to load data from multiple workbooks. As you are writing your dashboard in Power BI it doesn't make sense to have some of the work done in Power Query, presumably in an interim spreadsheet - unless you need that spreadsheet for something else
Here is an example of using Power BI from Power BI.Tips and one for Power Query from Microsoft