Import daily excel report and adding rows in same table

[ManjunathaEP]

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

[PC2790]

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.

[ManjunathaEP]

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  

[PC2790 · Accepted solution]

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!