FINALLY, I found your video that solves my issue. Thank you, thank you and thank you. I self-learned just enough to use Access to combine several data tables but then realized I can't use it online nor in the MacBook. So, I need to switch back to Excel and Excel has improved to 'Power Query' stuff in which I am not familiar with at all. But you have saved my day.
Tremendously helpful, thanks for sharing! I had a table of 213K+ unique values that I needed to merge with a csv of over 4M records (too large for a xlsx table) and this did the trick.
Hi Jon.. thanks for this quick tutorial on Merging Queries to mimic VLOOKUP. Always learn something new at your channel and at your web site. Also, really appreciate that you give access to the sample data to allow for following along with the video. Thumbs up!!
This is an amazing video. I did try to get the data from Customers table using xlookup, and I got a SPILL! error. Why wouldn't it add the fields horizontally and how would I fix that? I tried wrapping it inside a transpose function, but it didn't work. Would you have any comments on how to handle the Spill error? Also what do you do about the duplicate records in the out put table? Please advise Thank you so very much for your helpful vids!
Jon, can you merge data from many sources at once, like the append function where you post the files data you want to append in a folder and then power query append. Thanks for your videos.
Hi Jon, tried to apply this technique to merge Cost data to Sales data by item code to derive profitability. A large majority of the merged data works - big thanks! But I notice that for only a few selected item codes, duplicate sales data are created in the merged data table. What are the possible reasons for that, and what can I do to avoid creation of duplicates? Thank you!
Thank you Jon.I have a number of Sheets limited to 32 columns each so I may use a Form to enter data in each sheet and then I am combining them in one sheet of 255 columns or less for a mail merge, so this is just what I needed.
Minute 6:10, instead of adding a new sheet, can you replace an existing sheet? The reason I ask is, I have formulas that go to one of the connecting table, I would like to update the information and not have to create a new sheet with new information.
Hi. Thanks for the video, this was very close to what I'm trying to do, but not quite. Let's say I have a customers table with cusID and cusName columns. I would like to have another table where I enter the order information, called orders I suppose. when I enter a customer ID into the orders table, I would like the orders.customerName to update automatically. I don't want to create a merged new table, but rather pull that information into the orders table itself. Is this possible using either the Data Model or Power Query, or should I just stick to using XLOOKUP?
I am using a lot of this method to do my reconciliations for suppliers account. Thank you so much! It is a job saver! Now, my question is, I have made data connection to pdf files (statements of account) and created my power query. I noticed that if someone make any changes to the pdf file, e.g: re-name it or even delete it from our shared location, I lose the connection and messes all what I’ve done. To save my work, I did copy the table to a normal excel file, however, is there any way to repair the connection? Replace the connection by adding the link to another copy of the same pdf file that I used before? Do you know what I mean?
Thank you! the first attempt, it got all messed up and I was ready to give up "oh it does not apply to my case", then I tried again and worked beautiful. My case was whenever there is a country in one column, the race column needs place race name automatically. Example: If Ukraine country, then Race column-cell is 1-White, if Mexico, then 3-Hispanic; if China, then 4-Other Asian; if Phillipines, then 9-Filipino, and so forth. for this process I was using the Xlookup. So today first time used the PowerQuery. I handle 100 + data and no way I will type race for each country.
Hi thanks for all these great videos really helping me to sort all my personal and professional info... My question is, Could you do the merge query with an approximate text match? its the same example as your video only the Orders DB matching column has an approximate "fuzzy" match... Kind of like using wildcards with the merge query Thanks
Is it possible to have more than one lookup column? Let’s say both tables contain Customer ID and Department ID columns. In the second table, contact info is different, depending on the combination of those two columns. Is it possible to merge by using two or more lookup columns?
To merge (not append) do all tables have to be in the same sheet? My question is, can we have multiple Excel workbooks in a folder, be merged into a separate file, where this separate file keeps getting updated whenever the folder is updated ?
Hey Jon, Sorry this is unrelated to the video but I have been racking my brain trying to figure out what excel is trying to do with some of my inputs. When I put in my dilution labels, e.g. 1:50, 1:100, 1:200, etc. Excel is changing them to 0.07638r, 0.111r, and 0.18055r. I recognize that just changing the format of the cell to text will fix the issue of the "auto-fix," but I am confused as to what excel is trying to represent. It's not the division as you might expect for calculation of a ratio, as that would show 0.02, 0.01, and 0.005. Any guesses what is happening here? I'm probably just forgetting my grade school math...
Very nice , how can I Marge I modified table(Marge1) with another single table , knowing that the first table has a hide principal value , I've been trying to do this but it gets repeat some values
Hi Jon. I immediately subscribed your super great video. It's just awesome. By the way, do we have a way to sort a single column only? My file has no relationship with other columns each other, so I just wanted to sort one column that doesn't impact other columns. Thanks.
Very helpful video but I'm having trouble getting my connection only table query to be visible from the primary table that I am merging into. Do these tables have to be sheets of the same excel file (like they are in your vid)? I feel like that isn't the issue because you specifically mention they don't even have to be the same file type. But clearly I'm missing something obvious.
Thank you Jon, I have successfully merged many tables but I can not figure out how to either conditionally merge tables or use the equivalent of the DAX LOOKUPVALUE function. From my SQL Database table I have 2 queries and I need to be able to retrieve data from Query 1 Column C based on both Column A & B values and place this data in Query 2 based on Column B & D in Query 2. Is there any way you know how to do this?
I have a situation where i use IFERROR(VLOOKUP between two excel files. If the vlookup result is not found using the primary key then i use the secondary key to lookup. Can you help me how to do this in PowerQuery?
Hi Jon, Thank you for the video!! I have large data set in my excel file it (saved it in csv format) is around 930000 cells. When I m working in the file it got hanged most of the time. Kindly advise how to work smoothly in the file...
I want to ask a question it's not regarding this video but hope you answer it I have seen a couple of videos to create a search bar in excel to look through the table I have done all the steps correctly as described by many but it gives an error of formula I have done it 100 times exactly as it was described by some people. Need help.
Sir I used separate Power query to append Gross sales of various Branches to find total Gross sales and Sales return of various Branches to find total sales return in two different sheet, in one sheet total Gross sales and in other sheet total sales return. To find Total Net sales, I manually reduce sales return from gross sales. Sir is there any way to do this all calculation in power query itself.
Hi Jon, I am using version excel 16 and under Data option in the ribbon, I cannot see the from table/range option.I see options of of get external data or new query.Can you please guide me.
HELP Please! I ve used Merge before and it was working fine. But now, for my table with 80,000 rows, PQ is creating millions of rows based on the other table I am merging with, which is bizarre! Any solution around this? Its not even loading :(
Where has this channel been all my life?? Thanks Jon!
FINALLY, I found your video that solves my issue.
Thank you, thank you and thank you.
I self-learned just enough to use Access to combine several data tables but then realized I can't use it online nor in the MacBook.
So, I need to switch back to Excel and Excel has improved to 'Power Query' stuff in which I am not familiar with at all.
But you have saved my day.
It used to be half day or more for me to do the merging. Now, I can do it in less than 5 mins. Thank you Jon !
I've been doing VLOOKUP for days and this just answered my question of "why doesn't the machine do the work for me?" Thank you, Jon!
Everytime I watch this guy he saves me time! Even when I'm not expecting it
Thank you Jon! You've explained the merging in a very comprehensive way. I will definitely visit your channel often!
First thing what I do when watching your videos is clicking Like and then watching. Because I know they gonna impress me!
you are absolutely the best to learn from. the clarity and the way you train is just GREAT
Thank you!! I was going crazy trying to figure out an index match function and this was soooo much easier than that!
Tremendously helpful, thanks for sharing! I had a table of 213K+ unique values that I needed to merge with a csv of over 4M records (too large for a xlsx table) and this did the trick.
That's good to know, Evan. 😀
Hi Jon...Thank you for this tutorial. The videos are easy to understand
Hi Jon.. thanks for this quick tutorial on Merging Queries to mimic VLOOKUP. Always learn something new at your channel and at your web site. Also, really appreciate that you give access to the sample data to allow for following along with the video. Thumbs up!!
Thanks for making such clear and easy to understand videos!
Great job! I got it done following this step by step ... good job Jon! 👍
This is exactly what i was looking for - thank you.
Great to hear that! 😀
This is an amazing video. I did try to get the data from Customers table using xlookup, and I got a SPILL! error. Why wouldn't it add the fields horizontally and how would I fix that? I tried wrapping it inside a transpose function, but it didn't work. Would you have any comments on how to handle the Spill error?
Also what do you do about the duplicate records in the out put table?
Please advise
Thank you so very much for your helpful vids!
Excellent tutorial Jon!
Nice and simple explanation. Thanks a lot.
Very well taught. God bless you
So very well explained! Thanks.
Jon, can you merge data from many sources at once, like the append function where you post the files data you want to append in a folder and then power query append. Thanks for your videos.
Thank you, this video hit all the key points I needed.
Hi Jon, tried to apply this technique to merge Cost data to Sales data by item code to derive profitability. A large majority of the merged data works - big thanks! But I notice that for only a few selected item codes, duplicate sales data are created in the merged data table. What are the possible reasons for that, and what can I do to avoid creation of duplicates? Thank you!
Great Video, help me a lot. I was struglling to automate my vlookup from long time. now its matter of 10 min only. thanks
The Awesomeness Of Power Query..Great Tutorial Thank You Jon :)
Thank you Jon.I have a number of Sheets limited to 32 columns each so I may use a Form to enter data in each sheet and then I am combining them in one sheet of 255 columns or less for a mail merge, so this is just what I needed.
Its very useful. Thanks Jon
Enjoyed it! So much to learn!
Thank you! Massive help... something so simple...
Thank you, This video really helped me!
Thank you. Wonderfully explained.🙏🙏🙏
Thank you, John, its very helpful 👍
Mind blowing. Really very helpful.
Thank you! I always learn so much from you.
Thank you, Tshepo! I appreciate your support. 🙌
This is powerful and useful. Well explanation!
Thank you this is exactly what I needed
Very well explained. Thankyou.
You're welcome, Porridge! :)
thx for the tutorial, this helps me to "vlookup" from *txt data with more than 3 million rows, since "normal" excel can only handle 1 millions row
Very clearly explained. Thanks so much
Thank you very much Jon for this video
Glad you liked it, Khadi! 😀
Minute 6:10, instead of adding a new sheet, can you replace an existing sheet?
The reason I ask is, I have formulas that go to one of the connecting table, I would like to update the information and not have to create a new sheet with new information.
Thank you. I have issues when I got extra rows after merge queries. do you know how I can fix this. thank you
Hi. Thanks for the video, this was very close to what I'm trying to do, but not quite. Let's say I have a customers table with cusID and cusName columns. I would like to have another table where I enter the order information, called orders I suppose. when I enter a customer ID into the orders table, I would like the orders.customerName to update automatically. I don't want to create a merged new table, but rather pull that information into the orders table itself. Is this possible using either the Data Model or Power Query, or should I just stick to using XLOOKUP?
Thank you, Jon. This helped me a lot.
Awesome! Thanks for letting us know! 🙌
Cheers Jon, absolute legend.
Thank you John ! Great tutorial
kindly send me the link for your power query training. thank you
I am using a lot of this method to do my reconciliations for suppliers account. Thank you so much! It is a job saver! Now, my question is, I have made data connection to pdf files (statements of account) and created my power query. I noticed that if someone make any changes to the pdf file, e.g: re-name it or even delete it from our shared location, I lose the connection and messes all what I’ve done. To save my work, I did copy the table to a normal excel file, however, is there any way to repair the connection? Replace the connection by adding the link to another copy of the same pdf file that I used before? Do you know what I mean?
Thank you! the first attempt, it got all messed up and I was ready to give up "oh it does not apply to my case", then I tried again and worked beautiful. My case was whenever there is a country in one column, the race column needs place race name automatically. Example: If Ukraine country, then Race column-cell is 1-White, if Mexico, then 3-Hispanic; if China, then 4-Other Asian; if Phillipines, then 9-Filipino, and so forth. for this process I was using the Xlookup. So today first time used the PowerQuery. I handle 100 + data and no way I will type race for each country.
Thanks for these valuable info 👍
Well explained. Brilliant!
Can i merge more then 2 tables, or can i merge all files from folder? Thanks
Hi thanks for all these great videos really helping me to sort all my personal and professional info...
My question is, Could you do the merge query with an approximate text match? its the same example as your video only the Orders DB matching column has an approximate "fuzzy" match...
Kind of like using wildcards with the merge query
Thanks
For example if there multiple items in one coloum and also same items in other excel file ,so can we get same result or it will show duplicate item
Is it possible to have more than one lookup column? Let’s say both tables contain Customer ID and Department ID columns. In the second table, contact info is different, depending on the combination of those two columns. Is it possible to merge by using two or more lookup columns?
To merge (not append) do all tables have to be in the same sheet? My question is, can we have multiple Excel workbooks in a folder, be merged into a separate file, where this separate file keeps getting updated whenever the folder is updated ?
Hey Jon, Sorry this is unrelated to the video but I have been racking my brain trying to figure out what excel is trying to do with some of my inputs. When I put in my dilution labels, e.g. 1:50, 1:100, 1:200, etc. Excel is changing them to 0.07638r, 0.111r, and 0.18055r. I recognize that just changing the format of the cell to text will fix the issue of the "auto-fix," but I am confused as to what excel is trying to represent. It's not the division as you might expect for calculation of a ratio, as that would show 0.02, 0.01, and 0.005. Any guesses what is happening here? I'm probably just forgetting my grade school math...
Jon, thanks, very useful
Very nice , how can I Marge I modified table(Marge1) with another single table , knowing that the first table has a hide principal value , I've been trying to do this but it gets repeat some values
Thank you, this will help me learn how to extract data in my work environment. Love the Excel sample provided.
I'm happy to hear it. Thanks Claudia! 😊
Amazing Jon !! you have a magic
Brilliant, so easy to follow! Thanks Jon :)
Hi Jon.
I immediately subscribed your super great video. It's just awesome.
By the way, do we have a way to sort a single column only? My file has no relationship with other columns each other, so I just wanted to sort one column that doesn't impact other columns. Thanks.
Thank you Jon, you saved me!
you are amazing!! love you so much
Very late to the party here but this was really helpful... Thank you 😀
You're welcome, Pete! 😀
Does the result of this query updates when the source tables are updated? or is it a one off?
Can we perform these operations if the customers table was in a different worksheet?
Very helpful, thank you
Very helpful video but I'm having trouble getting my connection only table query to be visible from the primary table that I am merging into. Do these tables have to be sheets of the same excel file (like they are in your vid)? I feel like that isn't the issue because you specifically mention they don't even have to be the same file type. But clearly I'm missing something obvious.
Thank you Jon, I have successfully merged many tables but I can not figure out how to either conditionally merge tables or use the equivalent of the DAX LOOKUPVALUE function. From my SQL Database table I have 2 queries and I need to be able to retrieve data from Query 1 Column C based on both Column A & B values and place this data in Query 2 based on Column B & D in Query 2. Is there any way you know how to do this?
Thanks , where is the fix for the missing data during the merge. ?
Hi Excel Campus, I wonder if I can do a combination of calculated field values and lookup just like this. Is it possible? thanks for the answer.
Thanks for the great tutorial
You're welcome! 😀
I have a situation where i use IFERROR(VLOOKUP between two excel files. If the vlookup result is not found using the primary key then i use the secondary key to lookup. Can you help me how to do this in PowerQuery?
Hi Jon, Thank you for the video!! I have large data set in my excel file it (saved it in csv format) is around 930000 cells. When I m working in the file it got hanged most of the time. Kindly advise how to work smoothly in the file...
Great Tutorial. Thumbs up :)
Hello - I'm trying to merge but when the table column is created, the expand button is not working.
Explicit! Well done!
Excellent lecture
You are rock star 👍🏻👍🏻👍🏻👍🏻
I want to ask a question it's not regarding this video but hope you answer it I have seen a couple of videos to create a search bar in excel to look through the table I have done all the steps correctly as described by many but it gives an error of formula I have done it 100 times exactly as it was described by some people.
Need help.
Not sure but additional rows gets appended when I merge columns
Is powere query going to be faster than vlookup 30k rows/10-15columns?
Just need a help in this, you only merged two sheets at a time, what if I want to merge 4 sheets at a time?
👍👍👌👌 very helpful for me
Thanks
Can we load data from SQL table based on loaded table in power query without loading full table of SQL database please help
Awesome. Thank you!
Sir I used separate Power query to append Gross sales of various Branches to find total Gross sales and Sales return of various Branches to find total sales return in two different sheet, in one sheet total Gross sales and in other sheet total sales return. To find Total Net sales, I manually reduce sales return from gross sales. Sir is there any way to do this all calculation in power query itself.
Awesome Jon... thank you a lot
can we do this in Google sheet?
Hi Jon,
I am using version excel 16 and under Data option in the ribbon, I cannot see the from table/range option.I see options of of get external data or new query.Can you please guide me.
Awesome.Thanks Jon
Please help me how to avoid the duplication of data of sales while merging as i need only payment date column, but its getting duplicate
You a real one
Really Helpful
but why does it change the order of "rows" of my current query?
HELP Please!
I ve used Merge before and it was working fine. But now, for my table with 80,000 rows, PQ is creating millions of rows based on the other table I am merging with, which is bizarre! Any solution around this? Its not even loading :(
Bloody brilliant!!!
How come I couldn't find the "from table/range" option in data in the practice sheet I downloaded? is it bcuz of the excel version I have?