Kenji Can you do a video, showing how to extract figures coming from outlook excel attachments to your excell spreadsheet. For example everyday I recieve an excel attatchment on outlook, and from that attatchment I copy and paste the figures onto my speadsheet. Can this be automated. Plz can you show if this is possible
Hello my excel files (3Nos. Jan, feb and mar) from folder are not merging. While loading only one feb file is shown instead of data. What is the reason
I love it when you say "Alright!, Back to the Video😄" after talking about your Course. Been binge watching you for the last two days and learned a lot of things in a short time :) really thankyouu sm ^^
The "From Folder" query is simply the best. I use it all the time where I need to combine data from multiple files, or I want to change the data from similar files for reporting.
Newbie here. I spent hours seperating out filtered data into new pages, copying and pasting it all into one. Then, my visuals suffered because of all the separations. I'm subbed now.
These days almost all files in a professional setting are stored on shared (Sharepoint)drives. This makes it significantly harder to do this. It took me a while to figure out. It would be amazing if these type of videos touch on that or show that. But thanks a lot for the video many will benefit from this!
This is exactly what I was going to touch on. I do not have the ongoing monthly updates all neatly in one file location as an Excel workbook. I have monthly updates, that come in as separate month files in a Sharepoint location. I need to stack each of these every month.
Thanks a lot @kenjiExplains. I have being able to get my work done within minutes. I really grateful. Please can you also make a tutorial for us on how to consolidate multiple excel workbook with multiple sheets into one. Thanks a lot.
2:29 Yes, Power Query is the best!... Now, another way I thought of was Copy -> Paste Special -> Paste Link. Does this have the same problem as the other methods? Thanks.
Hi kenji, Thank you for this clear explanation! My ex husband was a BI consultant and I have watched many times, but it seemed always very complecated. Now I actually need the knowledge. You are working in this example with sheets and files, my source is on the web. I have tried to merge them, but it didn't work out the way I wanted. Would this also work for 2 files from the web? Thank you again and hope to hear from you. Kind regards, Sam
Thanks kenji you’re really a life saver. I’m a beginner IB (investment banker not irritable bowel LOLZ) ant I am learning a lot. Even though chat GPT can literally do all this hahahaha (job will be obsolete LOLZ). but my firm doesn’t let me use it LOLZ, so thanks Kenji 🎉
Hi Kenji, to merge the excel sheets into one, should all the sheets have the same number columns? And should the columns be arranged in the same order?
Order is not required and if there are more column, power query will add unique columns and show relevant data in each col. For instance, if one sheet contains sales person and other dont, the merged data will show sales person col with null of the sheet where there is no column and show data for sheet where data is available. hope it answers your question
Thanks a million Mr Kenji, this was indeed helpful. But few concerns, I noticed that the date format has changed when transposed to Power Bi, also the $ sign is missing for price and sales, and the number format for units sold. Is there a way to maintain the format?^
Hi! Kenji. I have 2010 version excel w/PQ. I have one sheet with additional column. When I bring them all into power query & remove the additional headers like you show. The other sheets have the values in the wrong column. Is there a way to correct this in PQ or do I have to add the extra column in all the sheets before bringing it into PQ? Thank you for your time & your videos!
Hi Kenji, for work, I have used the "Combining Multiple Excel Files" method you presented. However, the styling of entries gets lost (half of an entry is bold, the other half is in regular font). How can I maintain the integrity of the data?
Power query is the best solution but you could have used VSTACK refering to excel tables instead of sheet/range. And if you don't want to concert the ranges into tables, you can use a dynamic named range using the offset function. Those are great workarounds :)
Thank you for your videos, the problem I am having is that I want to pull a daily csv into a folder, but it has the same name every time, would you suggest renaming when I pull it into the folder? Or is there a better way?
Hi Kenji, thanks for ur useful video. I have a doubt . If the total number of rows exceeded 1048576, whether power query will split the data to the next sheet? Can u tell how to do that please.
You are so beautifully succinct with your videos. I'm having a problem trying to combine 12 workbooks. I get all the way to the final steps of Combine&Load and click on my data, but get "[Expression Error] the key didn't match any rows in the table." I've done it over a half dozen times and tried to read up on the key error; to no avail. I'm about to tear out my hair. Any suggestions? Thanks in advance for taking a moment to read this.
Kenji! found it! my worksheets each had a different 'Sheet 1' name! I made it work after naming Sheet 1 the same in every file. I will definitely be searching your other videos for assistance
Have one important excel file and it takes more time to open please if you have any solution to this file. I use office 2021. For all you are my teacher ever and ever. Thank you.
I want to be able to use the “from folder” function, but I need the files to be cleaned up before they they are pulled into the total file. If I need to clean my data, like you show when pulling from an excel file in the beginning of the video, how can I make sure my data is organized before it pulls in from the “from folder” function. Can these 2 things be done in conjunction with one another?
Very helpful, Thank you. Could you please upload a video on how to convert pdf file and multiple pdf files to excel? If you could use bank statement pdfs it would be really helpful...🙏🙏
Im having issues in power query where if some columns i need to combine from multiple sheets are formula-based, not values. Power query sometimes import those columns and return entries as null if i dont open the excels first to recalculate and save. Is there a workaround so i wont have to open reclaulate and resave excel?
This was very helpful. Sadly I am very new to this but working in office for many years. I’m trying to continue improving how I present data. I am a bit concerned with how I can use this. I need weekly changes but it will use over 125k lines per week. These are extremely large files. I’m not sure this will work or if there is something better to consider. I just want to be able to add the file to a folder and it would update a pivot that I can create a dashboard. Anyone else use extremely large files?
Can we do this in single excel file, where we have different sheets for each month & at last in final consolidation sheet it keeps adding on automatically?
what if I do not want to combine all sheets? For example, if I have 12 worksheets (1 for each month) but I only want to combine 3 ( make it a quarter), how can i do that? thanks
Would it be possible to extract the Power Query in a different format so one can create a Pivot Table and summarize all those monthly reports and avoid using Power BI?
hi i have 20 files with one sheet in every file . every sheet has deffirent name . how could i bring all the sheets from 20 excel files into only one file with the 20 sheets in it. it means i can open only one file to work with those sheets . thanks in advance fore your help.
What if the columns in each month differ in numbers? However, the column names do match and we want only specific columns with the same name to be kept in our data. How to do it?
In PowerQuery you can click on the header of any columns you want to exclude from the resulting table and click on “‘Remove Columns”. PowerQuery has a lot more to offer in regard to cleaning and consolidating data. Try it! Look at the ribbon and the context menus for columns, lines and other objects, click or right click respectively and play around while watching the table showing for the effect of what you’re doing. This hands-on approach will reveal many of the basic functionality.
Hi, my name is Kamran and I am based in Dubai. I am watching your videos and impressed with your excel skills. I was wondering if you can help me with one formula to automatically calculates cost of shares sold on First-in-First-out basis. I regularly trade in stock market so volume of transaction is quite large. Hence, it is not possible to manually check cost of each share. Hence, I am looking to automate my file, however, I am not able to arrive at cost of shares sold. I took help from AI tools, watched youtube videos etc but remained unsuccessful. Problem comes if I make a split purchase and a split sale i.e. first buy quantity of any share on different dates and then sell the quantity in small chunks on later dates. I would be grateful if you have or you can suggest any solution for this. KR
@@AbrahamPanicker_AB Sincere thanks for your reply. I am looking for calculation either in Excel or Query that can calculate cost of shares sold on earliest purchase basis. For example i purchase 500 shares on 1st Apr and 500 shares on 2nd April. Now: if i sell all 1000 shares on 3rd April, formula should add the cost of both earlier purchase. If i sell 500 on 3rd and 500 on 4th, formula should first pick cost from 1st Apr and then 2nd Apr. But If i sell only 700 shares on 3rd Apr, formula should first pick cost of 500 shares from first purchase, then proportion cost of 200 shares from 2nd purchase. The remaining 300 unsold appear as closing quantity with proportionate cost of 200 shares from the 2nd purchase.
Dear Mr.Kenji, I appreciate you due to creating such an informative content. You are really a great teacher. Unfortunately, I can't download the excel files of your tutorials recently. Despite facing the message " Thanks for downloading (might take a few seconds)" right after writting down my email address, I do not recieve any file
🔥Take our in-demand Power BI for Business Analytics course: www.careerprinciples.com/courses/power-bi-for-business-analytics
Kenji Can you do a video, showing how to extract figures coming from outlook excel attachments to your excell spreadsheet. For example everyday I recieve an excel attatchment on outlook, and from that attatchment I copy and paste the figures onto my speadsheet. Can this be automated. Plz can you show if this is possible
Hello my excel files (3Nos. Jan, feb and mar) from folder are not merging. While loading only one feb file is shown instead of data. What is the reason
You make complex things easy for us with best presentation skills. You're great !
I love it when you say "Alright!, Back to the Video😄" after talking about your Course.
Been binge watching you for the last two days and learned a lot of things in a short time :) really thankyouu sm ^^
This video was super easy to follow. EXTREMELY helpful! Thank you!!!
Just stumbled upon your video while searching for how to merge multiple Excel sheets. It was beneficial and engaging. Keep up the great work!
Omg! I love how you explain! Step by step. Thank you❤
You by far have the best tutorial videos. Thank you for providing so many helpful videos.
Thank you !
Finally a clear explanation! Thank you very very much!!!
The "From Folder" query is simply the best. I use it all the time where I need to combine data from multiple files, or I want to change the data from similar files for reporting.
can you show us or send us the link to the video,thanks in advance
Thank you. You saved our lives. I love you
Thanks!
Your teaching style and method is awesome 👍
Newbie here. I spent hours seperating out filtered data into new pages, copying and pasting it all into one. Then, my visuals suffered because of all the separations. I'm subbed now.
These days almost all files in a professional setting are stored on shared (Sharepoint)drives. This makes it significantly harder to do this. It took me a while to figure out. It would be amazing if these type of videos touch on that or show that. But thanks a lot for the video many will benefit from this!
This is exactly what I was going to touch on. I do not have the ongoing monthly updates all neatly in one file location as an Excel workbook. I have monthly updates, that come in as separate month files in a Sharepoint location. I need to stack each of these every month.
@JohnYoga Just curious whether you use OneDrive? Maybe you can link to the files through that (?)
@@JJ_TheGreat thanks for responding. They are on different Sharepoint locations.
Such a simple explanation, great video Kenji!
Thanks a lot @kenjiExplains. I have being able to get my work done within minutes. I really grateful. Please can you also make a tutorial for us on how to consolidate multiple excel workbook with multiple sheets into one. Thanks a lot.
OMG new subs here sir. thank you for this tutorial. it save me alot of time! more power sir!
love from ph!
2:29 Yes, Power Query is the best!...
Now, another way I thought of was Copy -> Paste Special -> Paste Link. Does this have the same problem as the other methods?
Thanks.
Great job Kenji. Keep it up.
thanks very much senior, that was magic, no more copy and paste
Hi kenji, Thank you for this clear explanation! My ex husband was a BI consultant and I have watched many times, but it seemed always very complecated. Now I actually need the knowledge. You are working in this example with sheets and files, my source is on the web. I have tried to merge them, but it didn't work out the way I wanted.
Would this also work for 2 files from the web?
Thank you again and hope to hear from you.
Kind regards, Sam
Thanks kenji you’re really a life saver. I’m a beginner IB (investment banker not irritable bowel LOLZ) ant I am learning a lot. Even though chat GPT can literally do all this hahahaha (job will be obsolete LOLZ). but my firm doesn’t let me use it LOLZ, so thanks Kenji 🎉
Hi Kenji, to merge the excel sheets into one, should all the sheets have the same number columns? And should the columns be arranged in the same order?
Order is not required and if there are more column, power query will add unique columns and show relevant data in each col. For instance, if one sheet contains sales person and other dont, the merged data will show sales person col with null of the sheet where there is no column and show data for sheet where data is available. hope it answers your question
@@worldofdatawhich kind of makes it a complete mess...doesn't it?
absolute legend this guy!
Good Afternoon Sir,
Excellent, Excellent, Excellent.
Thanks a lot Kenji , this exactly what i need for my work related to multi seasonal cost matrix thanks thanks muwahhhh😅
Thanks a million Mr Kenji, this was indeed helpful. But few concerns, I noticed that the date format has changed when transposed to Power Bi, also the $ sign is missing for price and sales, and the number format for units sold. Is there a way to maintain the format?^
Thank you so much !
Hi! Kenji. I have 2010 version excel w/PQ. I have one sheet with additional column. When I bring them all into power query & remove the additional headers like you show. The other sheets have the values in the wrong column. Is there a way to correct this in PQ or do I have to add the extra column in all the sheets before bringing it into PQ? Thank you for your time & your videos!
Hi Kenji, for work, I have used the "Combining Multiple Excel Files" method you presented. However, the styling of entries gets lost (half of an entry is bold, the other half is in regular font). How can I maintain the integrity of the data?
Thanks a lot for this nice tutorial.
Power query is the best solution but you could have used VSTACK refering to excel tables instead of sheet/range. And if you don't want to concert the ranges into tables, you can use a dynamic named range using the offset function. Those are great workarounds :)
Thank you for your videos, the problem I am having is that I want to pull a daily csv into a folder, but it has the same name every time, would you suggest renaming when I pull it into the folder? Or is there a better way?
Great job Kenji.Thank you
Hi Kenji, thanks for ur useful video. I have a doubt . If the total number of rows exceeded 1048576, whether power query will split the data to the next sheet? Can u tell how to do that please.
You should not have such a large number of rows in Excel...Best use a proper high volume database...
@kenjiexplains can you able to make Monthly Financial Model Actual vs Budget, as well year to date, full year analysis
Very informative tht really help me solve my recent requirements
You are so beautifully succinct with your videos. I'm having a problem trying to combine 12 workbooks. I get all the way to the final steps of Combine&Load and click on my data, but get "[Expression Error] the key didn't match any rows in the table." I've done it over a half dozen times and tried to read up on the key error; to no avail. I'm about to tear out my hair. Any suggestions? Thanks in advance for taking a moment to read this.
Kenji! found it! my worksheets each had a different 'Sheet 1' name! I made it work after naming Sheet 1 the same in every file. I will definitely be searching your other videos for assistance
It's great. Only problem I have is 12 months data streching upto 3lakh rows.
May I know What is the maximum rows power query handles?
Really helped me. Thank you.
Have one important excel file and it takes more time to open please if you have any solution to this file. I use office 2021.
For all you are my teacher ever and ever.
Thank you.
Can i still merge sheets if they had different sizes? for example Jan had entries in row A to D, while Feb, had A to G
Big Thanks 🙏😍🙏
Awesome vid as always! Thanks Kenji!
I want to be able to use the “from folder” function, but I need the files to be cleaned up before they they are pulled into the total file. If I need to clean my data, like you show when pulling from an excel file in the beginning of the video, how can I make sure my data is organized before it pulls in from the “from folder” function. Can these 2 things be done in conjunction with one another?
Very helpful, Thank you. Could you please upload a video on how to convert pdf file and multiple pdf files to excel? If you could use bank statement pdfs it would be really helpful...🙏🙏
Hi kenji ,can I use append query to do the same ?
Great effort... Thumbs up
Good tip. I liked that
This is really helpful!! Thanks
Hi Kenji, can I combine only a certain tab within multiple excel workbooks using this method?
Comment before watching.its a learning dose 😌
Love it!
Im having issues in power query where if some columns i need to combine from multiple sheets are formula-based, not values. Power query sometimes import those columns and return entries as null if i dont open the excels first to recalculate and save. Is there a workaround so i wont have to open reclaulate and resave excel?
This was very helpful. Sadly I am very new to this but working in office for many years. I’m trying to continue improving how I present data. I am a bit concerned with how I can use this. I need weekly changes but it will use over 125k lines per week. These are extremely large files. I’m not sure this will work or if there is something better to consider. I just want to be able to add the file to a folder and it would update a pivot that I can create a dashboard. Anyone else use extremely large files?
very helpful, thanks!
Thanks for this awesome video.
Is there a way to exclude hidden tabs, when consolidating ‘all’ tabs into one summary tab?
will this work in a queried data?
Can we do this in single excel file, where we have different sheets for each month & at last in final consolidation sheet it keeps adding on automatically?
Great video
3:16 Can’t you select multiple items and click on all of them?
Hello Kenji, can u recommend acceptable professional exam on Power Bi
Hey honestly not familiar with any professional exams sorry!
Does this only work only for same number of columns in all sheets ?
what if I do not want to combine all sheets? For example, if I have 12 worksheets (1 for each month) but I only want to combine 3 ( make it a quarter), how can i do that? thanks
can you make a tutorial just on power query as well?
I already have that here: th-cam.com/video/Al0viFDqNn4/w-d-xo.html
why don't I have the "folder" option? I just have the other options
hi ... how if i want to merged some data consist of > 1mio row each ?
Can be done this for google sheets as well
Would it be possible to extract the Power Query in a different format so one can create a Pivot Table and summarize all those monthly reports and avoid using Power BI?
I am not exactly sure what you mean, but you could Close & Load -> "Close & Load To..." -> PivotTable, instead of a regular table.
hi
i have 20 files with one sheet in every file . every sheet has deffirent name . how could i bring all the sheets from 20 excel files into only one file with the 20 sheets in it. it means i can open only one file to work with those sheets . thanks in advance fore your help.
Can you create a video on "How to create a Stock Research Report on PowerPoint" and convert into pdf or word
What version of Excel is this? The one I use has fewer options.
good job man
Very well explained. Thanks, Kenji.
I wish you the best kanji
Thank you dear
Thank you so much
Can we create multiple excel files from one excel sheet / data?
If I buy an access bundle course, can I activate this power bi course in video?
hey there, please send us your course queries at info@careerprinciples.com :)
Is it all the courses I can access?
What I mean by asking is All access annual subscription, it is all the course?
What if the columns in each month differ in numbers? However, the column names do match and we want only specific columns with the same name to be kept in our data. How to do it?
we can use power query Colum name function to filter only relevant columns
In PowerQuery you can click on the header of any columns you want to exclude from the resulting table and click on “‘Remove Columns”.
PowerQuery has a lot more to offer in regard to cleaning and consolidating data. Try it! Look at the ribbon and the context menus for columns, lines and other objects, click or right click respectively and play around while watching the table showing for the effect of what you’re doing. This hands-on approach will reveal many of the basic functionality.
it was great thanks
hw can combine different sheets with different data into one sheet
Excellent
Good job
Hi, my name is Kamran and I am based in Dubai. I am watching your videos and impressed with your excel skills. I was wondering if you can help me with one formula to automatically calculates cost of shares sold on First-in-First-out basis. I regularly trade in stock market so volume of transaction is quite large. Hence, it is not possible to manually check cost of each share. Hence, I am looking to automate my file, however, I am not able to arrive at cost of shares sold. I took help from AI tools, watched youtube videos etc but remained unsuccessful. Problem comes if I make a split purchase and a split sale i.e. first buy quantity of any share on different dates and then sell the quantity in small chunks on later dates. I would be grateful if you have or you can suggest any solution for this.
KR
I can help but need to know exact requirements
@@AbrahamPanicker_AB Sincere thanks for your reply. I am looking for calculation either in Excel or Query that can calculate cost of shares sold on earliest purchase basis.
For example
i purchase 500 shares on 1st Apr and 500 shares on 2nd April. Now:
if i sell all 1000 shares on 3rd April, formula should add the cost of both earlier purchase.
If i sell 500 on 3rd and 500 on 4th, formula should first pick cost from 1st Apr and then 2nd Apr.
But If i sell only 700 shares on 3rd Apr, formula should first pick cost of 500 shares from first purchase, then proportion cost of 200 shares from 2nd purchase.
The remaining 300 unsold appear as closing quantity with proportionate cost of 200 shares from the 2nd purchase.
If the number of data is not the same in all files then how to do
Nice video
4:44 You forgot to rename your query… It is still called “Jan”.
The combine and load did not work for me
How can we do this in google sheets.
Very Nice
Thx!!!
Thank you
When I select the folder I still see 3 Parameters and you have only 1, no matter what I do I can't get it to just have one folder wit All 3 documents
Dear Mr.Kenji, I appreciate you due to creating such an informative content. You are really a great teacher. Unfortunately, I can't download the excel files of your tutorials recently. Despite facing the message " Thanks for downloading (might take a few seconds)" right after writting down my email address, I do not recieve any file
Please check your downloads folder in your computer/browser
I do not have commands Get data and From file.
Why is it when I close and load, only 537 rows appear instead of the full data set?
Can anybody help please? :(
Why my query only loaded two sheets. I have 8 sheets total 😢
Kenji is the real MVP
I have 25 tables and theyre not merging to one Ive used all the steps above still nothing,.