Check out our newly launched M Language course ↗ - goodly.co.in/learn-m-powerquery/
@kostiantynmakeiev95207 ай бұрын
after spendin I do not know how many hours of reading and watching some videos, in despair I opened that one - and this was brilliant, very easy to understand and to apply for my issue! Thank you so much!
@amirsaiftaz6 жыл бұрын
I was searching for merging multiple sheets into one sheet, thanks for your both videos helping me a lot to understand power query function. Amir Saif
@jitendrakulkarni72314 жыл бұрын
Very simple English, nicely presented
@jitendrakulkarni72314 жыл бұрын
Dual purpose lectures Power query and speaking English
@Ragamusic4 жыл бұрын
will it work for workbooks having data in multiple worksheets amd aslo will it combine the duplicate entries?
@GoodlyChandeep4 жыл бұрын
Please see this - kzbin.info/www/bejne/o4vFdWuXmdp8gLc
@mohdmuzammil30593 жыл бұрын
Explained very well. What if there is spelling mistakes sales rep name in two different years your response will be highly appreciated
@Eric-qm4vm2 жыл бұрын
Thanks so much!!! Your videos are fantastic. I’d like to ask you a few questions, hope you can help. 1. Is there a way to pull data from a workbook with uneven data structure? Imagine a workbook with several bordered groups. Each group represents a job site and the names of staff members assigned to work there. Some job sites have multiple bordered groups due to multiple shift requirements 1st,2nd and 3rd shifts. During the day, all staffing changes are made on this main workbook. For example, if a staff member calls out of work sick it is indicated next to his name where he is assigned to work. The job site bordered groups are in various ranges. Therefore columns are rows are not consistent or even matched. Is there a way to pull data like Sick call outs, vacation, etc on a worksheet like this? Question #2. Is it possible to automate dates on multiple sheets with uneven data structure? I am required to update the same dates on all 3 sheets in excel. All 3 sheets are structured differently and the date locations are in various cell ranges.. can this be automated by entering the date only on the 1st page? Thank you so much.
@SHIVAPRASAD-ov9jy2 жыл бұрын
Thank you for the Video, it is really helpful. Have a question, what is the option if I want to filter based on Sales person in combined file
@dgk2m92 жыл бұрын
Thank you very much, this video is exactly what I needed to properly understand what I've been doing wrong. Thanks!
@GoodlyChandeep2 жыл бұрын
Great to hear!
@markjansenlk5 жыл бұрын
Thanks for the very informative video, I have following questions; - Can I adjust the column width of the final sheet to stay fixed during refresh operation? - Can i protect the final sheet and still do the refresh?
@GoodlyChandeep5 жыл бұрын
Hey Mark 1. Once you load the data in Excel you may go to the DATA Tab >> Properties and uncheck the "Adjust Col Width" option 2. You can go the Review Tab and Protect the Workbook with a password, this won't allow the end user to modify or see the query (but the user can refresh it). The refresh won't happen in case the user is using Power Pivot as well, you may further read about this here - www.excelguru.ca/blog/2017/05/02/protect-power-queries/ Hope that helps, Cheers
@shubhamtyagi31442 ай бұрын
Awesome it saves my 1hour everyday.
@nallelialvarado939710 ай бұрын
Thank you for your video! I have a question. Is there a way to add a row with a name of the file? For example, say I'm getting the data that you're using but it does not have the row with the dates, just the data. The date is only in the title of the file. How can I add a name automatically that matches the name of the file?
@sumanjalir21448 ай бұрын
Hi Sir, is there any formula or format to prepare One View Dashboard Xl without sharing any reference data to others like just enter the one word or one number(which are mentioned in reference files) at any cell then it Comes all the required data at a time .. Please reply for this query it's Very useful to my profession
@suresh1234able4 жыл бұрын
Explanation is fantastic. Content is superb. Thanks a lot.
@sushantsharma2946Ай бұрын
Is there any way to eliminate specific rows from all sheet before close and load
@judyrodbryanvicente8638 Жыл бұрын
Hi Mate I'm a fan of your very clear video tutorials, I would like to rely a question and hopefully I get an answer to your magnificent mind :) If I combine 2 excel file (with same data structure), and same data but no date column, I only rely to ID (unique) to remove duplicates. My problem is, when I change something inside one column(update), it does not overwrite and still retain the old data since I'm referring only to a ID. How can I solve this issue in which if theirs an update it will adapt the new data as output, not the old data? Thank you in advance Chandeep, more power!!!All Support
@navneettiwana28446 ай бұрын
Pretty much useful and well explained👍
@BBB2323BBB4 жыл бұрын
great video. Can you help or point me to the right video that will help me combine the rows in power query like you combined the columns?
@SFLLibya4 жыл бұрын
Thank you very very very much , it was really helpfull , URGENT Q- can i separate the combined files by inserting each combined file's name before it's data? example : file year - 2005 then data , then cell contains "file name : year 2006" then data ,,,etc ???
@GoodlyChandeep4 жыл бұрын
While doing Remove Other Columns, Keep 2 columns. Content and the Name Then expand the files
@shoppersdream3 жыл бұрын
Thank you! What happened to the empty row you added on the top? I didn't see that you removed the empty row. Let us say that there is data in rows 2 and 3 and you don't want to show them. What would you do? You don't want these 2 and 3 rows to append after every table.
@philren77214 жыл бұрын
Very useful and effective for my daily work, thanks.
@GoodlyChandeep4 жыл бұрын
Glad it was helpful!
@Yatender_Yatender2 жыл бұрын
Sir need one help , I have connected power query with a folder and folder contains multiples files with different dates and I want to filter only specific dates data pls help-out Even I tried to make date filter as a function but don't know how to connect it with editor option Request to please help out..
@rajanjha23922 жыл бұрын
Hi, how to do transform files from shared files dynamically? Do you have any video on that please give the link
@vindyad20045 жыл бұрын
Very informative, thanks, I have a question with data entry forms created in excel. how do we combine these type of excel forms that are filled with data, to create a master data sheet, is there a way to do using power query?
@Pompeystumagoo3 жыл бұрын
Thanks very much for the video. I am having an issue removing he first few rows from my sample file and the bottom two rows from the same file. Removing the first 10 rows is not a problem, but when I go on to remove the bottom two rows, it throws up an Expression Error: The key didn't match any rows in the table. I've been going round in circles for the past 2 hours but can't figure out where I have gone wrong. Please can you help advise what might be causing this? Thanks.
@chaudhrypankaj4 жыл бұрын
Wonderful. How can we do reverse of it. Means split the combined file into different files.
@GoodlyChandeep4 жыл бұрын
Check if this helps kzbin.info/www/bejne/pYe9iGSdeLSVfrs
@chaudhrypankaj4 жыл бұрын
thanks but it could not help in splitting the data in one file into multiple files. requirement is to create multiple files from a large file. for example. I have data in 60000 rows and i want to create 30 files of 2000 row each. How can we do that automatically.
@GoodlyChandeep4 жыл бұрын
@@chaudhrypankaj I don't think that is possible unless you write some vba code
@archanasom60523 жыл бұрын
Can we use the procedure for sheets with merged cells and blank rows
@arseniocorres36195 жыл бұрын
Very very helpful. I have a question. I have several workbooks with the same structure and I combine the files manually. I took only the new data. Will power query able to update the file without duplicating the previous data and add only the new ones? Thank you very much to reply to this question? ARC
@GoodlyChandeep5 жыл бұрын
There are two ways While writing the query youll have to add steps so that only the new data gets picked up Or you can combine all data and create the calculation on new data using DAX Hope this helps 😊
@rushishah333 жыл бұрын
If we have made file using power query available in oxide 365, can we access the file on other systems which are operating on Microsoft office 2007?
@rizwanwali42235 жыл бұрын
Thanks for the clear explanation! 👍I have a question: if i have photos in each row, is it possible to combine them into the master sheet?
@GoodlyChandeep5 жыл бұрын
Photos?? I am afraid not
@Mist99634 жыл бұрын
Hi, Thank you for sharing a very informative video on merging workbooks. I have a workbook with multiple sheets which i have converted to table formats. The data in every sheet is different with uneven column headings and I don't know how to combine these. I have tried converting all the worksheets to table formats and appended them however I feel the data loaded is incorrect. Could you please help with this query?
@GoodlyChandeep4 жыл бұрын
Please watch this video kzbin.info/www/bejne/o4vFdWuXmdp8gLc
@sankar9641 Жыл бұрын
Hi Chandeep, I got error while doing merging the sheet as the two sheets few columns are identical columns and the rest of the columns i need in the merged data, but the excel is throwing error, Can you please help
@tanujamane16484 жыл бұрын
Thanks for this video. But how to convert each file table with standard header format. Cz I have data with multiple files which will added dynamically and each files having slightly different headers. So how to merge this type of data.
@GoodlyChandeep4 жыл бұрын
Hello Tanuja, See if this video helps kzbin.info/www/bejne/o4vFdWuXmdp8gLc
@HeadingForTomorrow4 жыл бұрын
Very clear, precise and to-the-point video, thank you! This is a very quality tutorial. Question: Is this possible with multiple tabs within 1 file + 1 tab in another file, so basically merging data from 2 tabs in 1.xlsx and 1 tab from 2.xlsx, and with completely column layout (one has 8 columns, other has 15, but they are relevant data, just presented differently like Customer vs Client, Last Active vs Last Purchase, Total Due vs Balance, etc..)
@GoodlyChandeep4 жыл бұрын
Watch this please - kzbin.info/www/bejne/o4vFdWuXmdp8gLc
@HeadingForTomorrow4 жыл бұрын
@@GoodlyChandeep Works great, except I have a tiny problem with extra rows on top of my file, I asked the question in that video, it's kind of a pickle but at least it's consistent. I hope you can help me.
@vinice92224 жыл бұрын
i want to merge multiple excel containing multiple sheets with data like sheet1, sheet2 etc to once existing excel with same sheets accordingly. is it possible
@manojjrkhiara2402 жыл бұрын
you are too good, thank you so much
@GoodlyChandeep2 жыл бұрын
Glad you like it!
@hamo76114 жыл бұрын
fantastic Video. thank you.
@AmandeepSingh-dc5gw5 жыл бұрын
That's an amazing video, but can this method handle big thousand rows data as well?
@GoodlyChandeep5 жыл бұрын
This can handle even a million rows or more ;)
@AmandeepSingh-dc5gw5 жыл бұрын
Hey Goodly, I am following you religiously and really your all videos are awesome.. Please post some more videos on power query and power view..big salute
@GoodlyChandeep5 жыл бұрын
@@AmandeepSingh-dc5gw Thanks Amandeep, I am glad you liked my work. Goodly is the name of my youtube Channel. My name is Chandeep :) You can find more of Power Pivot / Power BI / Power Query work on www.goodly.co.in
@YouTrolol Жыл бұрын
2:45 i'm coming to this video from your other video about creating dynamic paths. i've done that with dynamic files for other data sets. currently trying to do the same with merging other files. I can list the required files, but when i remove other columns and try to epand (at 2:45) i get an "expression.error: we oculdn't find an excel table named 'DynamicPath'. Any idea why this is? or am i watching the wrong video?
@maralrodriguez16806 жыл бұрын
Really really helpful! A BIG THANK YOU!
@GoodlyChandeep6 жыл бұрын
Thanks Maral.. Glad you found it helpful
@janpereboom85045 жыл бұрын
Thanks, thanks a lot for you clear video explanation! Due to certain character(s) used my Power-Query is not recognising a column from the multiple Excel Files. The text used for the column title is “Total [m3]”, with character 3 in superscript. Do you have an idea how I can cure this topic?
@GoodlyChandeep5 жыл бұрын
Try changing the column header? Does it work after that ?
@GoodlyChandeep5 жыл бұрын
We'll need to create a custom function that changes the name of the specific header before consolidating data from all the files together. Can you, as a sample send me 2-3 files, with the exact problem mentioned in the email ? goodly.wordpress@gmail.com
@samchan453 жыл бұрын
really awesome!!
@weiluk95264 жыл бұрын
Hi thanks for sharing, just have a question: if my files have multiple tabs, will it still work?
@GoodlyChandeep4 жыл бұрын
For multiple tabs please watch this one - kzbin.info/www/bejne/o4vFdWuXmdp8gLc
@venuszhang-bi3pu Жыл бұрын
How to import certain worksheet of every files in the folder as a independent worksheet (not merged into one worksheet)?
@Excel-power-users6 жыл бұрын
Very nicely explained
@GoodlyChandeep6 жыл бұрын
Glad you liked it!
@sumitsapra2836 жыл бұрын
Hi Chandeep Dataformat.error:the input couldn't be recognised as a valid excel document. Details: Binary. The above is the error while using the data query. But if I trying in different system it's working fine Please help.
@chandeepchhabra98546 жыл бұрын
Please make sure that your file type is NOT binary or xls (the old excel format)
@sumitsapra2836 жыл бұрын
I don't now what is binary but the excel format is XLSX
@sumitsapra2836 жыл бұрын
I forwarded the email please have a look Your vedios always help us to improve our job skills and for automation process Thanks for all the support.
@anefuoche10533 жыл бұрын
thank you
@SammyChia4 жыл бұрын
This save my day. Thank you so much!
@landroveraddict24573 жыл бұрын
Hi I wonder if you can help? I work in a school and need to export student data from our MIS to a web system each year. The web system does not use a unique identifier that is exported with their CSV. I would like to combine a CSV from the MIS with a CSV from the online system. I need to match the two files by First name, Surname and DOB. The MIS file is the most accurate and will contain new students and class group changes for all students. Absent from the MIS file will be pupils who have left and these need to be excluded from the final output. Now I am doing this manually and it takes forever and is prone to error. I would be incredibly grateful if you or one of the viewers can help. I have got as far as combine the two sheets, but this just puts one set of data below the other.
@nksodhi915 жыл бұрын
Hello Chandeep, Thanks for suchh awesome and simple explanation to such a complex doing. But I am facing a problem. When i click on combine files double arrow button over Content column it gives me error: DataFormat.Error: External table is not in the expected format. Details: gczox3ca.XLS Please tell me what can be done to fix this error. Please!
@GoodlyChandeep5 жыл бұрын
Hello Navdeep, Please change the file format from .xls to .xlsx using the save as option in excel and perform the steps. Power Query is not very good at fetching information from .xls (old excel format) files. Hope it helps
@sunnysun65675 ай бұрын
amazing !!!!!
@wensisong44924 жыл бұрын
Hi! Thanks for your interesting video! I have a question, I got an error “the key didn’t match any rows in the table”. Is it because my data is not structured consistently? Thanks!
@GoodlyChandeep4 жыл бұрын
Could be. Try watching this video - kzbin.info/www/bejne/o4vFdWuXmdp8gLc
@BrianThomas4 жыл бұрын
Question. How does this work if the workbooks are on a SharePoint? Would you still be able to pull the data in the same manner and merge data into a signal document?
@GoodlyChandeep4 жыл бұрын
Yes.. Sharepoint is one of the data sources in Power Query. You select that and the rest of the process remains the same!
@BrianThomas4 жыл бұрын
@@GoodlyChandeep awesome. Thank you very much. I will give it a try.
@richardschechter97405 жыл бұрын
what if the files have outlines and sort rows with duplicate information but different times. i.e merging opens downloaded by constant contact
@sandeepdas80294 жыл бұрын
I have a query. What if I want to pick up data of a particular month from 3 different worksheets containing 3 different subsheets each (for example, total 9 sheets for 9 customers where each company handles 3 different customers) & combine into 1 excel sheet? The worksheets contain yearly data and I need to pick up and project the data in a single sheet for all 9 customers.
@GoodlyChandeep4 жыл бұрын
Watch this video - kzbin.info/www/bejne/n4KxdnVvlNpni5o&feature=emb_logo
@nancikalidin86292 жыл бұрын
If excel has multiple sheet with uneven data..do we follow same method?
@GoodlyChandeep2 жыл бұрын
See this kzbin.info/www/bejne/o4vFdWuXmdp8gLc
@prayaashbykishon70534 жыл бұрын
Hey.. Thanks for video. I got this error when I click the combine. " DataFormat.Error: External table is not in the expected format. Details: ywzed51k.XLS " Please suggest.
@GoodlyChandeep4 жыл бұрын
You need to have all excel files in xlsx format. I guess your files are in the old format (xls)
@prayaashbykishon70534 жыл бұрын
@@GoodlyChandeep thank you
@prayaashbykishon70534 жыл бұрын
@@GoodlyChandeep No Goodly.. All files are in xlsx format but it says same error..plz help me out
@tanujamane16484 жыл бұрын
I want to do like u did with customer and client. But the client header is always changing in every file.
@GoodlyChandeep4 жыл бұрын
Try this - kzbin.info/www/bejne/oajJXnhplpiUhac
@maureen2845 Жыл бұрын
I've got an error "DataFormat.Error: The input couldn't be recognized as a valid Excel document."
@lucymulyadi439211 ай бұрын
What if we have 2 sheet in excel😂 page 2 is pivot table, can you help me? Thanks🙏🏻
@rahulraj-fs6fk Жыл бұрын
After the data gets loaded it shows errors. What should we do
@chandrabhanmishra18984 жыл бұрын
How to filter Data from one sheet to move Seprate sheet Item wise
@GoodlyChandeep4 жыл бұрын
Please watch this video - kzbin.info/www/bejne/pYe9iGSdeLSVfrs
@RajeshKumar-tj8uc4 жыл бұрын
While consolidating multiple file, I am getting an error stating " [Expression.Error] The key didn't match any rows in the table". I have to consolidate 12 files and each file has close to 50000 rows and 78 columns
@GoodlyChandeep4 жыл бұрын
Try this - kzbin.info/www/bejne/o4vFdWuXmdp8gLc
@GARREOLAH3 жыл бұрын
Awesome videos! That was really clear and helpful!. I would like to ask something. I have some files where the date is kind of split. From A1 to B3 I have the database info, like report date, report name, report responsible, and my actual table/info starts on A5 (row 4 is empty). Is there a way I can have a clean report with the following characteristics? 1. Take the report name (B2) to a new column. 2. Combine the info (the one that starts on A5 - Kn) from all reports, including the new column with its own report name I would really appreciate your help, I have been dealing with this for a while with no success.
@GoodlyChandeep3 жыл бұрын
See this - kzbin.info/www/bejne/mWmrgoJ5l690sNU
@GARREOLAH3 жыл бұрын
@@GoodlyChandeep Thanks so much! That was exactly what I was looking for. I do really appreciate your help! Keep it up!
@sumitsapra2836 жыл бұрын
I am trying but error showing while doing
@GoodlyChandeep6 жыл бұрын
What error are you facing ?
@dharmarajhatwar21263 жыл бұрын
How to convert multiple excel sheet data in a single sheet.
@GoodlyChandeep3 жыл бұрын
kzbin.info/www/bejne/n4KxdnVvlNpni5o
@dharmarajhatwar21263 жыл бұрын
@@GoodlyChandeep how to convert only required data from multiple excel sheet to single sheet