Forum Discussion
Import daily excel report and adding rows in same table
Hi, Thank you in advance for helping me on the below query:
Every day bank statements (excel) saves in a folder and that needs to be taken into power query and then accumulated the report on daily basis.
Ex: 1st Table imported and saved in query
| Table1 | |||||
| Bank reference | Narrative | Customer reference | Value date | Credit amount | Debit amount |
| CFRG3445 | NEFT from XYZ | 5345 | 1-May-22 | 3467 | |
| ASUI44 | Recevied from ABC | 346 | 2-May-22 | 6367 | |
| LKJDS33 | CHQ from YEE | 2355 | 2-May-22 | 346 | |
| SDFOR44 | NEFT from MEP | 24511 | 2-May-22 | 34536 |
Then, next day query needs to import with below data and adding back to table 1
| Table2 | |||||
| Bank reference | Narrative | Customer reference | Value date | Credit amount | Debit amount |
| RDTJYE33 | NEFT from CS | 7568 | 3-May-22 | 5747 | |
| SHJK55 | Recevied from MANJ | 5673 | 3-May-22 | 45783 |
Finally, I want the result and adding daily reports to the table 1
Expected result in Table1 after added rows from next day (table2)
| Bank reference | Narrative | Customer reference | Value date | Credit amount | Debit amount |
| CFRG3445 | NEFT from XYZ | 5345 | 1-May-22 | 3467 | |
| ASUI44 | Recevied from ABC | 346 | 2-May-22 | 6367 | |
| LKJDS33 | CHQ from YEE | 2355 | 2-May-22 | 346 | |
| SDFOR44 | NEFT from MEP | 24511 | 2-May-22 | 34536 | |
| RDTJYE33 | NEFT from CS | 7568 | 3-May-22 | 5747 | |
| SHJK55 | Recevied from MANJ | 5673 | 3-May-22 | 45783 |
Well if there are separate tables for separate files, you can use the below option to exclude the previous files form the refresh.
However in your case as it is only one table containing all excel files data, there is no way you can restrict the refresh for few.
It can be possible if you load the files once and keep the pbix file for future use, however you need to load the latest data as well and refreshing the data will delete the data of files which are not present at the folder loacation.
I hope I was able to clarify it for you!
3 Replies
Hello ManjunathaEP ,
If you are consuming the files from a specific folder, you can simply write the logic to read all the files and combine them into 1.
Then based on the new files on day to day basis, you can simply refresh which would load the new files as I can see the new files have the same table structure as the original one.
The code to read through the files will look something like:
= Folder.Files(<Files Path>)And the code to read the files would be:
= Table.SelectRows(<Previous Step>, each [Extension]=".csv")This will work if you have csv file, you can change it accordingly.
You can the combine all the files using the button as shown:
See if these steps work for you.
Thank you so much for your prompt reply and it helps me a lot. Is this works, if old files after completing a month deleted from folder? I would like to accumlate the all the data and store it in a query, whether those files exist or not in the folder. Request you to support
Well if there are separate tables for separate files, you can use the below option to exclude the previous files form the refresh.
However in your case as it is only one table containing all excel files data, there is no way you can restrict the refresh for few.
It can be possible if you load the files once and keep the pbix file for future use, however you need to load the latest data as well and refreshing the data will delete the data of files which are not present at the folder loacation.
I hope I was able to clarify it for you!
