Our company uses SAP Business Warehouse to consolidate data from many sources and I access that data under Excel files. Now we try to find a solution to save daily new data to a main large file (also a workbook), then create dashboard (with Power BI) from that for strategy issue.
The problem is, the file gets larger after each day (will be soon 20 mil. rows) and therefore Excel file cannot be used anymore. We want to put new rows to current main file and the value for each attribute of each new row depends on the current dataset (formulas). Now we update that main file (with new rows) manually in Power BI.
But that task is pretty time-consuming so we want to find an automated solution. That means a colleague as expected will put a small new data set (under a workbook file) in a tool and get an updated main file with new rows.
My daily task was cleaning and analyzing data so I do feel now that I lack basic data management knowledge. Could I do that with Power BI or R/Python? We work with Access, Power BI Desktop, Python, R and KNIME. Any recommedation from you will be so nice to me. Thank you in advance!