Advanced Excel Power Query | Merge Queries with 3 Common Columns | Tutorialspoint
Vložit
- čas přidán 9. 10. 2019
- Advanced Excel Power Query | Merge Queries with 3 Common Columns | Tutorialspoint
Advanced Excel Power Query Online Training. In this power query tutorial, you will learn about Merge Queries with 3 Common Columns or V LOOKUP with 3 lookup columns. Get Certified in Advanced Excel (Power Query):bit.ly/3LdiidF
Use coupon "CZcams12" to get ‘’FLAT 12%’’ OFF at Checkout.
Quality Learning to UPSKILL yourself only at TutorialsPoint. Explore & Learn the top trending courses curated by the best trainers in the Industry @tutorialspoint: Your go-to Learning Solution.
Power Query is a powerful tool given by Microsoft. Power Query combines different software(SQL, Excel, VBA).
With our Trainer, You learn:
- Introduction to Advanced Excel Power Query
- Excel Installation
- Advanced Excel Power Query Text Functions
- Date Function- Calculate Age in 2 button clicks
- Merge Queries from two Excel Files
- Append 100 Excel Tables
- 99+ Excel functions can be replaced by button clicks
- M Function for Date
- 700 M functions to reduce tasks
- Merge Queries - No VLOOKUP.
- How to Clean Financial Data with Power Query.
Upskill your career by training on the best-TRENDING Courses in the Market.
Premium Packs with Lifetime Access: bit.ly/3m8dwp0
Trending Courses: bit.ly/3KBW32w
Check out Ebooks on the Latest Technology: bit.ly/3Y3MCvR
Microsoft Excel is a spreadsheet tool capable of performing calculations, analyzing data, and integrating information from different programs. This Power Query training has been designed for users who want to learn Advanced Excel Power Query in easy and simple steps. It will be highly useful for those learners who do not have prior exposure to Microsoft applications.
Watch more Related Videos
Advanced Excel Power Query- bit.ly/3V86mPa
MS Excel for Beginner- bit.ly/3N4XuI3
Excel VBA Macro- bit.ly/3our7YS
Tutorialspoint, a leading ed-tech platform, offers Simply Easy Learning at affordable prices. We offer the best quality certification courses designed by global experts in top fields like Development, IT, Networking, Data Science, Artificial Intelligence, Machine Learning, Cyber Security, Business, Marketing, Office productivity, and Lifestyle. Those interested in learning from the basics to advanced levels of a particular topic can opt for our Prime Packs. We cater to the needs of 40 million learners per month with our 7000+ courses and 5000+ eBooks.
Subscribe to our Channel to get more related updates and turn on the notification: / @tutorialspoint_
#tutorialspoint #Excel #advancedexcelpowerquery #powerquery #exceltutorial
Thanks man! You're the best!
Very good video. Only one thing to note - the total sales formula is not Sales * discount. Its sales * (1-discount)
I appreciate your Excel Skills. Thanks for teaching us Excel.
This is the best explanation I have seen on CZcams.I have been searching all day.Thank you so much.My hero.
Hi Pawan
You are Gold. You are doing a service to Accountant. I wish that I watched your videos one year back. God bless you abundantly.
I appreciate you and this video! It has really helped me resolve a Power Query need at my job and will help going forward. Great work and please continue to make content.
This is exactly what I was looking for! Thanks a lot for the clear explanation!
Video is very helpful. Want to add one thing, during end it is mentioned as total sales which is actually total discount and after subtracting discount from original sales column, we will get net sales. Thanks!
Really amazing and very explicit. I just understand that "Left outer join". Thanks for sharing
Very good information sir
This video is really good explanation of query.
Just to point, the last column heading should be named as Discount amount instead of Total Sales.
THIS GUY HAS MADE THE EXCEL EASY FOR ME
This video has helped me in so many ways and at so many times. Honestly gem of a person you're ❤️
Nicely done as I am new ish to power query and was able to follow you in your presentation. Thank you.
Thank you well explained
Thank You For sharing Knowledge. Its really Useful.
Thanks!
Made it work, thank you so much :D
You explain very well . You are doing a great job
Thanks a lot
GJ!VERY HELPFUL
Greatest class ever watched
Thank you!
Thanq sir
Thank you very much, Arijit. Very well explained.
THANK YOU SOO MUCH .....
Great
You saved my day!
Thanks a lot. Your explanation is simple and clear. ( I tried many to many and got lost)
Love and Respect from Pakistanl; in fact that shoiuld be Total Discount column
Super teaching skills
I love you Bro.. This makes my work alot easier..😍😍
AMAZING!!! that's realy BREAKTHROUGH!!!
but what can be done if I have to replace a nested XLOOKUP formula (xlookup function within xlookup function) ????
I'm looking forward your solution
Is there a playlist exclusively for powerquery? Good job!
Thanks for this video it has helped me a lot,,,, Where can I get the dataset you are using for my training?
Why don't they allow a simple conditional merge join. I can't find it anywhere without don't long work around after the fact.
I have 2 tables. Both tables have acct number and a letter date field.
However I want to join the table on acct number and where the letter date from the first table falls between -2 and +2 days of the letter date in the 2nd table. In SQL it's simple but not in PQ.
Any ideas?
does that only work for 2 tables with similar count of rows?
Thanks for making this series, it's so easy to understand. Could you please share the files for practice?
YOU CAN GET IT FROM DIFFERENT WEBSITES
Very nice. Thank you. But please be aware when you multiply discount% with sales you get total discount and not total sales :-)
The result is total discount, not total sales please. Good video.
Hi, thats a good solution! But, what if I'm talkin about a sheet that contains like 300 hundreds lines and 30 columns, and I need to get a specific line x column, and the input of this data needs to be on a specific line x column too, is this possible?
Is it possible to use this method with some of the Lookup table cells blank (as a wildcard)?
Very nice presentation...How do you present this on youtube wlth you being in center and touch screen operation
sales X discount will not be total sales thats just the discount amount given , sales - discount amount gives total sales
Sir if table headers are different so can we combine that data those columns
merge query other optins also plz sir, on adv data🙏
Hi Pawan Sir,
It's very interesting, many thanks.
I am a bit confused about your calculation of total sales. Is that correct way (Sales * Discount = total sales)? I think that gives us a result total discount over the sale. Kindly help.
Many thanks
You are right Sapan, Total sales would be (Sales*(1 - %Discount))
Hi, Can we add unmatched column in merge? I mean, all matching columns + columns from one table + one selected column from second table
@@harpreetsinghgrover I am looking for same. Do you know how to do it?
Try combining three columns and then remove duplicate values. For that you'll have to take only right or left part of three or more or any number of columns so that remove duplicate work fine.
Making same post again, please reply, while merging two queries, 3 output getting if the duplicates are in base table
Is there a way to do this but when the columns contain numbers and you need only the ones higher or lower?
Sir, Excellent. But, do U give tutorial in HINDI language, plz let me know. Thanxxx.
what if there are different number of rows in two tables?
full outer join is not giving me all the rows... what to do in this case?
Hello, first off, thanks for the video. But it didn't work in my case. I have two queries: Q1 (price list in different currencies) has 90K lines, Q2 (exchange rates in USD) has 25 lines. The common columns are currency codes. I want to add a new column to Q1 (Unit price in USD) where it converts the unit rates from other currencies to USD. After performing the operation following the video, it returns 1M lines. Please let me know what step i may have missed or what extra step needs to be done. Thanks in advance.
Sir can you help me out of this I am not getting my join kind block I'm excel.. how I will get it
Hi i can see only 13 videos in power query course, case study 1,2 and 4 are missing can some one help me with that
Sir end result is not in sequence with Main file...for example, if i've India at serial number 1, it's shows at 3 or 4 in merged file...Any reason why ?
where can we get the excercise file ?
Why data appear twise after using merge
Please provide some excel template files to practice
Hi Vishal, am trying to find out ageing between 2 dates or today... And then have create ageing slab, using query function but could not succeed. For example
I have DOJ and Last working date of employees, here I need to find out how many days an employees works..so here LWD - Doj will gives me total number of days, if employees is still working then DOJ minus from today (),
Similarrly baded on number of days I have to create ageing slab say 0 to 1 , 1 to 3, 3+ to 6 so on.
Could please share the querey link it same kind of video available or if you can share the query will be a great help from you end.
👍
Pawanji, could you provide link to download practice files to follow along
Kindly share the file for practice
Sales ×Discount % is not total sales
It is Total Discount
i have unique problem.. having multiple row with duplicate name ,some value in first column and some column is empty while in second duplicate name row the value have in column which is empty in first column
Name Month Month
Rakesh June
Rakesh May
Ramesh April
Ramesh August
Can you merge this data excluding duplicate (one name value in single row)
What is the difference between merge columns and merge queries
with merge columns you merge two columns into one, for example when you merge column Month and column Revenue you'll get "Month:Revenue". When you merge queries you connect two queries with a specific connection (left/right/inner/outer join), and it can be used to append multiple columns from the other query, to your existing main query