@@GoodlyChandeep heyy, I unable to get your video on consolidate the data from different files when data is not in identical format ie. Columns are different. Can you please help on this.. appreciate your quick turnaround
Absolutely brilliant..! Thank you so much..! I never knew this and just applied to combine data from 167 sheets and it worked like butter smooth. Thanks a Ton..!
Chandeep, this was fantastic. You clearly explained processes that others simply glossed over and I was able to complete an important project. Thank you!
Hi Chandeep -- This is a life saver. Just one query, I wish to replicate the same on Excel web. Is it possible? My team and I want to work on the online excel and input respective data so that the same is auto inputted in the consolidated lis
Amazing video! May God richly bless you for this wonderful tutorial 😊 This is much faster than other methods I’ve found that have you create individual sources per sheet, or try to use buggy VBA code.
In the meantime, you can check out this book -ruclips.net/video/A61KJt4L87Q/видео.htmlsi=vK6wV5m85mqErqof Also, my book is coming soon, so stay in touch for any updates :)
Hi... Can u assist as in ... How to obtain the data on refreshing the consolidated file for a new sheet inserted in the existing workbook with the same headers?
Hello Chandeep, Thanks a lot. Your trainings are perfect. I have a question: I need to transform and combine multiple files in a folder. In the "Transform Sample File" step, I need to add a column to the "sample file" which includes the most used text in a specific column. I can easily group, sort, keep first row, remove other columns and drill down the needed value. But when I go back to the previous "original table" step, the variable isn't recognized. By any chance, can you propose a solution? Thanks again for all your sharings. Regards,
Thank you for the guide. i do have some additional question on adding more sheets. you shown us to replicate sheet at (06:01) , would it still work when we Create New Sheet (+)?
another thing, i tried working on the same Workbook (01:18) > Data > Get Data > From File > From Excel > "same Workbook" > Import , i get the following error msg "Unable to Connect: "The process cannot access the the file 'c:\Users\userA\Workbook.xlsx' because its is being used by another process" Would you know what this error means.?
Hello Chandeep. I learned a lot from this tutorial; however, if I am combining sheets with the same ID say, and I want the other fields that pertain to that ID from other sheets, how can I get all the data within the same row for the ID without removing duplicates?
How can I retain the formated field the same way after unpivot, for example when I change a field to currency, after pivot I lose the $ sign and the ,'s.
Hi Chanddep! Thank you for your tutorial. I have a question, I have multiple reconciliation sheet which I would like to summaries the balancing total. Do you have a solution?
Hi Chandeep - this work perfectly until the very end... It won't let me specify the current worksheet and instead summaries my 7 sheets in another 7 sheets :). I've re-watched the video at least 5 times and paused and copied exactly and can't see what I'm doing wrong???
I follow the steps and when I am to Import the Data I do not have the option to add it in my existing worksheet. When I add to a New Worksheet, then I get duplicate the sheets I wanted to aggregate in a form of a Table. When there is the expand option should I opt in for Aggregate instead of Expand?
I have a different sheets same like inventory with duplicate names of the items but I want it to be consolidated without being duplicate but the qty should not change Ex. Screw 5 inch 10pcs , screw 5 inch 50pcs
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).
When I try to consolidate the contents of other sheets to a blank sheet "Consolidated Data" which is in the same file it gives me the error as soon as I click the Transform button as "The process cannot access the file 'D:\OneDrive\OneDrive \Desktop\File Name.xlsx' because it is being used by another process." However if I chose the source as another file it allows me to proceed.
Hi Chanddep. As we are using same file to prepare Consolidated sheet, now if i want share the file with others they get error to find that data source couldn't find. Is there any way that everytime the query pick the file in the same file in which i am working, even if i rename itor save it somewhere else?
I have found the way from other video. First convert all the sheets in TABLE Then create a query for each query GET DATA ->> FROM OTHER SOURCES ->> BLANK QUERY ->> in the formula bar type "Excel.CurrentWorkbook()" Next time you just update the data in the sheets and refresh the queries
Hello, this worked perfectly but when I make any changes to the sheets, like new entry or even deleting a sheet, the combined table won’t refresh. Any idea how to fix this?
How can you connect to the same file while it is currently open? It is giving process currently in use error if you attempt to connect to the same file.
How to do if it is required to transpose data (with both attributes and date for a month) for each sheet and combine each sheet (in monthly basis) in power query? FYI...The headers are on row side.
Hi Chandeep, I have consolidated data using the said method. I just want the cell formatting also to be the same as in my original sheets. It pulls up the data only but not the formatting. Like for example in some sheets I have some data in Red color font, some cell backgrounds have changed as per my requirement. How to pull up the cell format in the new consolidated sheet
Hi Goodly Is there any reason in 2024 version to get an error when trying to consolidate in the same workbook like this? Details: We encouterred an error when trying to connect: "The process cannot access the file 'C:\Users\xxxxxxxxxxxxxxx\OneDrive - xxxxxxxxx\Documents\xxxx\Power Query\Consolidation in same workbook.xlsx' because it is being used by another process."
The data I am working on is taken from a PDF file. There are 2 rows automatically coming in each of the data sheet, which I need to combine. How to solve this issue? Plus, just because of this issue, row heads are getting repeated every time the data is being appended from another sheet. Please help.
Chanddep, I just consolidated a massive file that was related to a 100 Million dollar project! Kudos brother!!
Hope you get a percentage of that 😅
Cheers
@@GoodlyChandeep thanks unlikely haha. Thanks brother
@@GoodlyChandeep heyy, I unable to get your video on consolidate the data from different files when data is not in identical format ie. Columns are different. Can you please help on this.. appreciate your quick turnaround
THANK YOU! MY FIRST TIME CONSOLIDATING MY FILE INTO ONE WORKBOOK. THANK YOU A LOT!
Absolutely brilliant..! Thank you so much..! I never knew this and just applied to combine data from 167 sheets and it worked like butter smooth. Thanks a Ton..!
Fantastic!
Chandeep, this was fantastic. You clearly explained processes that others simply glossed over and I was able to complete an important project. Thank you!
Your method is much easier to understand & it helps me to consolidate all my data into one workbook.Thanks for posting this video.
Wonderfull explanation. You speak very fast. I need to pause and rewind. Lots of information. Thanks!
Glad it was helpful!
I love the way you explained .. Great job.
Thanks a lot for this video. Is possible to just merge the first 100 data from each sheet?
Thanks. Watched many other videos. Your solution is simpler & quicker. That works. Thanks!
Small world! I am also based in Dubai and have seen your events on Meetup. Would love to join someday.
Cheers
Hi Goodly. I amazed how did you notice it. You are very welcome. Write me anytime cs@mytablon.com
Very Useful and Perfect Explain that how to Consolidate the Whole sheet in single Source of Sheet.
After you create the consolidated sheet will new data added to the individual sheet automatically update in the consolidated sheet? Great Video!
Thanks, what if the number of rows in consolidated sheet becomes greater than 1 mio?
This is really helpful... Other channels were showing by creating the table... What if I have 500 sheets I don't want to create those many tables...
This is very clear and easy to follow, but I am not able to get the data to refresh after updating a source sheet. Thoughts?
Very nicely and precisely made the video and the content, I was not able to get the data on same file and getting error that file is being used
You are Genius !!! Awesome Chandeep Bhai
Hi Chandeep -- This is a life saver. Just one query, I wish to replicate the same on Excel web. Is it possible? My team and I want to work on the online excel and input respective data so that the same is auto inputted in the consolidated lis
😊 🎉 you always serve the best tutorial for complex function of power query. Thank youuu for your enthusiasm to share your knowledge!
Thanks Lea!
Bravo Chandeep🙏👏👍 love u...
Hi Chandeep, your demo is excellent and i am not finding get data option, please guide me...
Thanks for making this video,, helped saved a lot of manual effort for me.
Amazing video! May God richly bless you for this wonderful tutorial 😊 This is much faster than other methods I’ve found that have you create individual sources per sheet, or try to use buggy VBA code.
Hi sir, how to do the same in google sheets, could you please let me know
Glad to check this video, Found what i was looking for. Thanks Brother !
Appreciated to your lesson! You save my life 🙂
Hi Chandeep, Lets say I get a new copy of the same sheet every week to combine. Is there a way to automate it?
Awesome, is there any specific book to suggest, to learn power query in detail
In the meantime, you can check out this book -ruclips.net/video/A61KJt4L87Q/видео.htmlsi=vK6wV5m85mqErqof
Also, my book is coming soon, so stay in touch for any updates :)
Brilliant - thank you for sharing - saved me a ton of time!
I have been searching for a way to do this, Thank YOU!
Glad I could help!
Exactly what I wanted. Cheers!
Thank you my bro Chandeep from Bangladesh
Hi this is great but for some reason when i use tables to do this it is not refreshing?
Hi Boos - I have multiple workbook and each workbook have multiple worksheet, so how we compile all worksheet in single Book.
Hi chandeep, when i will refresh all data will loaded double the time
I need your assistance with merging a bit of a complicated file
Thank you so much man...Just love it
Hi... Can u assist as in ... How to obtain the data on refreshing the consolidated file for a new sheet inserted in the existing workbook with the same headers?
Hello Chandeep,
Thanks a lot. Your trainings are perfect. I have a question: I need to transform and combine multiple files in a folder. In the "Transform Sample File" step, I need to add a column to the "sample file" which includes the most used text in a specific column. I can easily group, sort, keep first row, remove other columns and drill down the needed value. But when I go back to the previous "original table" step, the variable isn't recognized. By any chance, can you propose a solution? Thanks again for all your sharings.
Regards,
This is so helpful! Thank you so much!
what if I want to combine different columns from different sheets into a single sheet dynamically?
Thank you for the guide. i do have some additional question on adding more sheets. you shown us to replicate sheet at (06:01) , would it still work when we Create New Sheet (+)?
another thing, i tried working on the same Workbook (01:18) > Data > Get Data > From File > From Excel > "same Workbook" > Import , i get the following error msg "Unable to Connect: "The process cannot access the the file 'c:\Users\userA\Workbook.xlsx' because its is being used by another process" Would you know what this error means.?
Hello Chandeep. I learned a lot from this tutorial; however, if I am combining sheets with the same ID say, and I want the other fields that pertain to that ID from other sheets, how can I get all the data within the same row for the ID without removing duplicates?
Incredible tutorial - thanks so much, definitely SUBSCRIBED!
How can I retain the formated field the same way after unpivot, for example when I change a field to currency, after pivot I lose the $ sign and the ,'s.
Great explanation brother . Thanks a lot !👍
Hi Chanddep! Thank you for your tutorial. I have a question, I have multiple reconciliation sheet which I would like to summaries the balancing total. Do you have a solution?
Hi Chandeep - this work perfectly until the very end... It won't let me specify the current worksheet and instead summaries my 7 sheets in another 7 sheets :). I've re-watched the video at least 5 times and paused and copied exactly and can't see what I'm doing wrong???
Thank you so much.. perfectly explained!
VEry Helpfull, Thanks.
Awesome. thanks man!!!
I follow the steps and when I am to Import the Data I do not have the option to add it in my existing worksheet. When I add to a New Worksheet, then I get duplicate the sheets I wanted to aggregate in a form of a Table. When there is the expand option should I opt in for Aggregate instead of Expand?
Thank you, this is very helpful!
bro thanks , it helps me a lot. easy to understand
my excel are old version please suggest me how can i combine multiple sheet in one sheet, there is no option of get data in my excel sheet
chandep bro... thanks to help me to consolidated my large no. of files
Thanks much for this knowledge share, appreciated!!
I have a different sheets same like inventory with duplicate names of the items but I want it to be consolidated without being duplicate but the qty should not change
Ex. Screw 5 inch 10pcs , screw 5 inch 50pcs
Thanks bro save my time alot
What if the data is / headers are different, how to consolidate the data?
Great lesson. Thank you
Thanks so much. This was very helpful
Legendary video, thank you so much!
Glad you like it! 💚
awesome video! thanks...saved me so much time!
Great to hear!
i have a query on this method which is useful. i am unable to open the same file while opening the location where it is saved
Thank you for the excellent tutorial
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).
Amazing yaaar🎉🎉
Thank you very much for wonderful explain
Brilliant! Thank you so much❤
Dear Chandeep would you please give an example power query using medical students data, I am badly need to understand it
Nery nice. However, this does not work when excel file is on the onedrive!
When I try to consolidate the contents of other sheets to a blank sheet "Consolidated Data" which is in the same file it gives me the error as soon as I click the Transform button as "The process cannot access the file 'D:\OneDrive\OneDrive \Desktop\File Name.xlsx' because it is being used by another process."
However if I chose the source as another file it allows me to proceed.
Thanks a lot Chandeep
Hi Chanddep. As we are using same file to prepare Consolidated sheet, now if i want share the file with others they get error to find that data source couldn't find. Is there any way that everytime the query pick the file in the same file in which i am working, even if i rename itor save it somewhere else?
I have found the way from other video.
First convert all the sheets in TABLE
Then create a query for each query
GET DATA ->> FROM OTHER SOURCES ->> BLANK QUERY ->> in the formula bar type "Excel.CurrentWorkbook()"
Next time you just update the data in the sheets and refresh the queries
I need to combine sheets that are NOT related at all and have inconsistent headers. Please help! I've not been able to find a video.
Hello, this worked perfectly but when I make any changes to the sheets, like new entry or even deleting a sheet, the combined table won’t refresh. Any idea how to fix this?
excuse me sir, at 3:54, how can i remove desired top row number for all table, all my sheet are same form so...Hoping ur answer to come soon
Hi, Thanks for the tip! but I dont have the get data query, am using excel 2013. Is there any other method?
You'll have to install Power Query in Excel 2013. It's free
Very helpful. thankyou,
Thank you! Time saver! :)
The custom option doesnt work for me i follow the steps and then the columns say error not table
Hi, Thanks. Pls explain how to consolidate the data from Multiple Excel files.
ruclips.net/video/mZbD8aduIJU/видео.html
i WANT TO COMBINE MULTIPLE SHEET INTO ONE MASTERSHEET. BUT DATA INCLUDE MANY CALCULATIONS AND MIX TYPE DATA SHEEET.. I AM CONFUSE
Great learning
Thank you it works😊
How can you connect to the same file while it is currently open? It is giving process currently in use error if you attempt to connect to the same file.
What if a column name in other sheets are different, Can we just have the tables come one below the other without having one common header?
Hi Sir, I don't want to combine the data instead would like to generate multiple tables from single query
THANK YOU SO MUCH😭
Explanation is Crystal Clearly Good CCG - Chandeep Chabra of Goodly :)
How to do if it is required to transpose data (with both attributes and date for a month) for each sheet and combine each sheet (in monthly basis) in power query? FYI...The headers are on row side.
Hi Chandeep, I have consolidated data using the said method. I just want the cell formatting also to be the same as in my original sheets. It pulls up the data only but not the formatting. Like for example in some sheets I have some data in Red color font, some cell backgrounds have changed as per my requirement. How to pull up the cell format in the new consolidated sheet
Sorry but Power Query cannot retain the source formatting.
@@GoodlyChandeep Is there any way other way to do both i.e consolidate data as well retain source formatting?
Thanks Brother
You're soo great , man .your way is so smooth and easy
It would still combine!
@@GoodlyChandeep okay thank you ❤
Hi Goodly
Is there any reason in 2024 version to get an error when trying to consolidate in the same workbook like this? Details: We encouterred an error when trying to connect: "The process cannot access the file 'C:\Users\xxxxxxxxxxxxxxx\OneDrive - xxxxxxxxx\Documents\xxxx\Power Query\Consolidation in same workbook.xlsx' because it is being used by another process."
Close the file and try again
@@GoodlyChandeep Sir, Still the same error
The data I am working on is taken from a PDF file. There are 2 rows automatically coming in each of the data sheet, which I need to combine. How to solve this issue? Plus, just because of this issue, row heads are getting repeated every time the data is being appended from another sheet. Please help.
DAMN, This was AWESOME thank you bro
Glad you like it!
Dude!! You rock.
Thanks. Glad it helped!
Awesome!!!
Hi, Can you please make a video to combine multiple workbook into one workbook.
Note: Each workbook contain multiple worksheet
ruclips.net/p/PLr7RyN24TvNbB4KvzYWF39zPak_gqFWjl&si=qEAwh58_JF9lI3l_