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).
not sure if you guys gives a shit but if you guys are bored like me during the covid times then you can watch pretty much all of the latest series on InstaFlixxer. I've been watching with my gf lately :)
That what i was looking for. Thanks for your vid! Regards from Poland :) P.S. Next time try maybe to zoom out your vid so we can see everything you do.
Im struggling to deal with error handling when appending tables. My queries are pulling late orders from a data bank in a separate workbook. When there are no late orders under one process....it tries to append an empty table....freaks out and crashes the workbook.
if you didn't have the date in there, how would you know which query it came from....is there a way to add an indicator column to tell you which sheet each data point came from?
Yes when combining data, you can remain with the Sheet name. To extract the content of the excel Use Add custom column, New column Name use "DataContent", Formula use Excel.Workbook([Content]) and press ok. and keep expanding. The sheet name will be under Item Name . Choose the "Item column" and the "Data" column and expand "Data" Column. This will solve your challenge
What if you would’ve merged February deposits with March loans? Would it just have blanks under loan column for February deposit rows? And blanks in the deposit column in March Loan rows?
Wayne Seymour that typically happens when your append a column that isn’t formatted the same as another column. For example: “Column 1” will not append to the bottom of “column 1” due to the case differences and create another unwanted column.
Append puts another table at the bottom. You'll need to make sure both tables have the same column names and formatting before doing so. You can also append more than 2 tables at a time. Merge is adding it to the side - sort of like doing a vlookup function in Excel. For this you need to have key columns in both tables to allow you to match.
Good request. I think most viewers would like to follow the exercices step by step. I can easely construct a table for me with random numbers and the same columns from the video, but nor even everybody will do the same.
This question was made 1 year ago, but I think is worth answering, because it happened to me: it happens if the names of the columns and their Data Type are not exactly the same.
Thank you!! Your tip at 7:35 is the most important when merging. That is what helped me after hours of trying to figure out how to do this!
This is what I expected basic of pq to understand ❤❤❤ thanks
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).
Congratulations on your effort to share your knowlegdes with us! Hi from Brazil!
not sure if you guys gives a shit but if you guys are bored like me during the covid times then you can watch pretty much all of the latest series on InstaFlixxer. I've been watching with my gf lately :)
@Landyn Erick yea, have been using instaflixxer for since december myself :)
That what i was looking for. Thanks for your vid! Regards from Poland :)
P.S. Next time try maybe to zoom out your vid so we can see everything you do.
1:00- Append
5:48- Merge
Best video so far on this. Solved my problem. Thank you!!
great video, it's helping me... how do you add more queries to an existing append? Every time i try to do so, it creates a new append
Thanks. A very good introduction
Im struggling to deal with error handling when appending tables. My queries are pulling late orders from a data bank in a separate workbook. When there are no late orders under one process....it tries to append an empty table....freaks out and crashes the workbook.
Super and clear explanation thank you
You are great my man
Thanks a million ❤❤❤🎉🎉🎉
This tutorial is great.
Great video !!
if you didn't have the date in there, how would you know which query it came from....is there a way to add an indicator column to tell you which sheet each data point came from?
Yes when combining data, you can remain with the Sheet name. To extract the content of the excel Use Add custom column, New column Name use "DataContent", Formula use Excel.Workbook([Content]) and press ok. and keep expanding. The sheet name will be under Item Name . Choose the "Item column" and the "Data" column and expand "Data" Column. This will solve your challenge
Super helpful thanks alottt
How to removed duplicate in merged the data?
What if you would’ve merged February deposits with March loans?
Would it just have blanks under loan column for February deposit rows? And blanks in the deposit column in March Loan rows?
yes
@@mukundkanodia8817 appreciate it
@@canefan17 have you completed the excel course..till now..since it's been 3 years
@@mukundkanodia8817 haha sure have. Have moved on to SQL. I found power query to be unreliable and slow.
Why is excel power query adding a unwanted column to the appended table when I refresh the data ?
Wayne Seymour that typically happens when your append a column that isn’t formatted the same as another column. For example: “Column 1” will not append to the bottom of “column 1” due to the case differences and create another unwanted column.
Can we just append all three files into sheet 1 without creating new sheets 1,2,3, append ?
sonu patel absolutely! Your way would be much more efficient and typically how I do it.
@@geekyveep3420 hi could you give me a hint on how to handle that. Please
@@geekyveep3420 then why did you not make a video of the more efficient way that you typically use?
Ty men nice work 😘😘
What is the difference between append vs merge ?
Append puts another table at the bottom. You'll need to make sure both tables have the same column names and formatting before doing so. You can also append more than 2 tables at a time.
Merge is adding it to the side - sort of like doing a vlookup function in Excel. For this you need to have key columns in both tables to allow you to match.
Geeky Veep thank a lot
Nice
could you share the excel sheet.
Good request. I think most viewers would like to follow the exercices step by step. I can easely construct a table for me with random numbers and the same columns from the video, but nor even everybody will do the same.
Thank you
how about merge?
The merge feature is about 6 minutes into the video. I hope you find it helpful.
Why is excel power query adding a unwanted column to the appended table when I refresh the data ?
This question was made 1 year ago, but I think is worth answering, because it happened to me: it happens if the names of the columns and their Data Type are not exactly the same.