Append Different Excel files Automatically into a Single Table

[Ram_DCT]

Hi All, 

I have two excel files [one sheet in each] with same number of columns in each file in a folder. I want to combine these files into one master file by appending these files one after another dynamically - as every month there will a new file with same number of columns coming into the same folder.

My query should read the new file for the new month and append data into the master file I have created. For example, below is the data from two excel spreadsheets and need to be combined into one table AND whenever a new month's file is there in the folder, the file should be appneded in to the master file automatically whenever my scheduled refresh happens.

I have tried this using Folder->Transform Load->Binary->Select First File-> but getting an error that, "Key did not match". Please help team. I have attached the error screenshot.

May'20 Table
Name	Marks	Class	Rank	Sex
XYZ	90	X	2	M
PQR	89	X	4	F
LMN	87	X	5	F
ABC	95	X	1	M
Jul'20 Table
Name	Marks	Class	Rank	Sex
XYZ	78	XI	3	M
PQR	98	XI	1	F
LMN	67	XI	4	F
ABC	81	XI	2	M

[v-eachen-msft]

Hi @Ram_DCT ,

You could refer to this resolved case which is similar to yours.

https://community.powerbi.com/t5/Desktop/An-error-occurred-in-the-Transform-File-query-Expression-Er...

[lbendlin]

where is that "master file" ?  Power BI cannot write back into Excel workbooks directly.

[Ram_DCT]

Master file is nothing but a consolidated file where I am going to append all the files into one.