Excel Magic Trick 1331: Import Multiple Excel Files & Sheets into Excel: Power Query Get & Transform

Very clear, easy and informative explanation. Thank you for all your efforts.

cac vid cua ad thuc su dinh qua a, nhieu kien thuc bgio em moi biet do a ma giong noi cua ad rat de nghe va hay nua nen la rat thich xem vid cua ad a em gan hoc xong vid nay roi

Beautifully and clearly explained 👍

THIS IS AMAAAAZING !!!

Very clear. Many thanks.

the Excel.workbook formula has worked wonders for me

Thank you for all your excel videos... please help me on as I am creating a rental database of rental properties where we are paying rents to landlords. So below are some agreement details where I wanted to create a monthly Rent statement that to be paid to landlord


Example -
- Landlord Name - ABC
- Agreement Period- 15 Aug 19 to 14 Aug 25
- Initial rent : 1000
- Escalation after 3 year : 15%
- So after 3 year, rent will be - 1150

Output should be as per below:

Sr No From To Rent
1 15 Aug 19 31 Aug 19 =1000/31x17 = 548.38
2 1 Sep 19 30 Sep 19 1000
3 1 Oct 19 31 Oct 19 1000
...
...
36 1 Aug 22 31 Aug 22 = [1000/31x14] + [1150/31x17]= 1082.25
37 1Sep 22 30 Sep 22 1150
...
...
61 1 Aug 15 14 Aug 25 =1150/31x14 = 519.35

I can't able download Sourcedata folder dor this vedio please help me out

could you help me out for download source folder

If you import an individual workbook, you can go into the sheet and made modifications such as delete first 4 rows and use 5th as header. Is that an option if you import everything in the folder like this video shown?

Hi, how can I import multiple excel files into Power BI without combining the files as the data is all different? The reason is I have 20 excel files. So instead of Get Data > Excel one by one, I want to import one shot.

I have a folder containing 19 Excel worksheets with each worksheet containing 16 sheets inside it. While the name of the excel files are different, the name of individual sheets inside them is same i.e. they start from "Sheet 1" and go till "Sheet 16". What I have to do is append each sheet at the end of the next one and continue the same to get a new excel file containing only one sheet. (In other words, sheet 1 from file two (say, B) will be appended at the end of sheet 1 from file one (say, A) and sheet 1 from file three (say, C) will be appended at the end of the above two files containing sheet 1 from files A and B, respectively and so on untill every file is added).

this...I need to test it, but this may have solved a problem I've been experiencing for a long time. Thank you so much

Thanks a lot, is it possible to match multiple columns in combine queries and merge.

Is it possible to point Power Query to an online OneDrive folder rather than a local folder?

My goodness. I happened to find this and managed to follow it completing the practical work using Excel 365 on my W10 Laptop.
Useful stuff to know as I have dabbled with excel in the past.
Something worthwhile to due as I am currently at home furloughed due to COVID-19.
I had to pause and rewind back quite a few times but got there in the end.
Some of the functions are slightly different as I guess my excel is now than that featured here, but I managed it easy enough.

Hello,
i was not able to get a newly added sheet in the existing workbook to master sheet while refreshing power query,
why so?

You can make the Query Connection as the basis for the Pivot Table instead of using the table as a range. This way you dont have to refresh twice...

Hi sir , thx , when I try to append xml files from a folder , I face an error when clicking on the coloumn with two inwards arrows ( binary) .below the error :
We encountred an error while trying to connect .
Details : " Xml processing failed. Either the input is invalid or it isn't supported . ( internal error : Name cannot begin with '.' Character , hexadecimal value 0x00. Line 1 , position 2. )"

Could u please help . Thx