Forum Discussion
Appending data from fresh report to report.xlsx
Hello Community Members,
I have come across one scenario, maybe I could get some help or ideas from EXCEL community.
I have a source website from which I can download an activity report. Let's assume CRM. There can/ cannot be some additional rows in it but whenever it is downloaded it will append a date and time in its file name. Like report_05-02-2024-20-15.xlsx There is some description in row 1,2,3 and actual data headers in 4 and data rows from 5 onwards.
Now I want to convert that into table from row 4 till the end of file, use a lot of pivot tables in another sheet and create reports based on those pivot tables.
If it is one file it can easily be achieved, BUT, PROBLEM: How / what sort of mechanism shall I use to append the additional data from the fresh downloaded file. to the one where report is created.
THanks.
5 Replies
Copy/paste of your brief private message: hi need your expertise
Could you develop here please?
- ???? develop ??
As I understand (not 100% sure): somewhere in an Excel file you have a data set from which you (will) build various reports/PivotTables and on a regular basis you want to append it new data (i.e. from downloaded file report_05-02-2024-20-15.xlsx)
If that's a good summary of the challenge you're dealing with, there's something doable with Power Query. There are at least a couple of challenges though:
- If you keep all the downloaded report_dd-mm-yyyy-hh-mm.xlsx files in the same directory - in other words if you don't delete files after appending their content to your existing data set - you'll need to understand how the file names are "constructed" (I only assumed dd-mm-yyyy-hh-mm above) to ensure you don't bring twice the same data
- The tricky part is to understand how to setup a self-referencing query (i.e. https://www.youtube.com/watch?v=wHgv_gWw7iQ)
EDIT Better link for what you expect to achieve: https://www.thebiccountant.com/2016/02/09/how-to-create-a-load-history-or-load-log-in-power-query-or-power-bi/ (by ImkeF)
NB: Not sure why you tagged this thread with "Excel for web". Just be aware this is currently doable with Excel on Windows (maybe with Excel 365 on Mac but not sure...)
- Will need to check and test... will come back... thanks for your reply.
Cheers.
