15.4 Merge Data Having Multiple Criteria or Multiple Columns in Power BI (Power Query)

#pavanlalwani #powerquery #excel #dataanalytics

In this Power Query tutorial, we will explore an interesting topic: merging data with multiple matching columns or criteria. Often, we encounter situations where we have two tables with multiple matching columns, and we need to merge them to get the desired output. In this video, we will walk through a practical example using two files: a master table and a transitional table.

Both tables are stored in different files, but they have matching columns such as first name and last name. However, the positions of these columns are different in each table. Additionally, there are instances where the same values appear multiple times in the last name column. If we were to perform a simple lookup using only the last name, we would not get accurate results. Therefore, we need to consider both the first name and last name columns for our lookup.

By using Power Query in Power BI, we can easily merge the tables and obtain the desired output. In this example, the final output we want is the salary column from the master table into the transitional table. We will guide you step-by-step on how to achieve this using Power Query.

To follow along, make sure you have the necessary files stored in the designated folders. In Power BI, click on "Get Data" and select the respective Excel files. Rename the tables accordingly and use the "Merge Queries" feature to match the desired columns. Be sure to select the matching columns in the correct sequence, both from the top and bottom.

Finally, expand the merged column, select the required columns, and click on "OK" to obtain the output.

Merging data with multiple matching columns or criteria can be a challenging task in Excel; however, Power Query in Power BI simplifies this process tremendously. Watch this tutorial to enhance your Power Query skills and learn how to merge data effectively in Power BI.
---------------------------------------------------------
💼 Invest in your future with our Power BI 2023 NEW Course at an incredible 50% discount:

📚 200+ MCQs to test your knowledge.
📝 20+ Assignments for hands-on practice.
🔍 11+ Case Studies for real-world insights.
💼 5+ Industry Projects to build your portfolio.
🎓 Course Completion Certificate for your resume.
💡 Tips & Tricks to boost your skills.
🔎 5+ Industry Datasets for practical learning.

Join thousands of professionals who have transformed their careers. Enroll now at 50% off!
You can also explore our Power BI DAX 2023 course and get a 360º understanding of DAX

Invest in yourself. Become a Power BI expert. The future is yours! 💼💪"

#datacleaning #businessintelligence #powerbi #excelfunctions
---------------------------------------------------------
Timestamps:
00:00:03 Merging Data with Multiple Matching Columns 🔄
00:00:12 Practical Example: Master Table and Transitional Table 📊
00:00:33 Observations: Changing Positions and Duplications 🔄
00:00:56 Utilizing First Name Column for Accurate Lookup 🎯
00:01:19 Desired Output: Salary Column from Master Table 📋
00:01:49 Selecting and Renaming Tables in Power BI 💼
00:02:19 Merging Queries with Multiple Columns 🔄
00:02:45 Matching Columns from Top to Bottom 🔄
00:03:14 Expanding Master Column and Selecting Salary 📊

[PavanLalwani]

🚀 Calling all data enthusiasts! 💼🌟

👉 Discover the power of Power BI with our Specialization course at a jaw-dropping 50% off!


💥 Level up your analytical game and supercharge your career with the Power BI DAX course. 💯💼

[vivekkar]

Pavan, you are my favorite teacher for Power query and BI.

[johnunnunny]

Super class, understand able sir your step by step explaining is really good.

[mohitarora]

Dude, that was amazing and to the point.. Subscribed !!

[krishnakishorepeddisetti]

Interesting Sir...i didnt knew this technique...thank you for teaching this to us

and sorry for bugging you sir...kindly teach us on how to do cumulative sum and running sum in power query...it will be very helpful.

Best regards
Kishore

[priyadarshinichettiar]

Thank you so much for this Sir. I'd been adding calculated columns for concatenation and lookupvalue to achieve the same. I will use this from now, thanks again.

[VinayKumar-ijeu]

Hi Sir.. Are you covering complete course here on Power BI? Basic to advance?

[chiranjeevlamba]

sir if we have 10 matching column then we need to select all or just few for reference

[firdousjahan]

U explain difficult concepts so easily appreciate🙂

[suravikar]

Hi Pavan, thank you for all the great videos. Quick question: why I don't see the merge query in power BI?

[H-dotr]

Hwo can I do it in Excel in the same workbook?

[vivekkar]

sir i have question, last week i was trying to load pdf file having 200 pages. Loading itself took hell lot of time and when tried to transform it systems hangs. any idea why?

[sukhdevsingh-xmnz]

May I get a practise file ....Bec in description...when I open downloading files ...there are many files so I get confused which file is to download for particular video in which you r teaching !!

[aniketpachore]

After merging Query, some columns show null i follow the same database as you

[niharikasmily]

Sir, if we merge columns, delete columns, deleting rows, getting date/text/number functions using button clicks then what is the use of DAX? Please explain. I am new to power bi

[shrutisharma]

Hello what should we do if we have two tables, table A and table B .Table A has 4 column and table B has 3 column but I want only two columns as a output while merging.

[KD-phvi]

Can we Import data from power query of power bi to excel?

[paramjeetsingh]

sir i need to know i have 50 lakh data in some other software....and i bring into power pivot and how to make
charts on 50 lakh data that we imported into power pivot ? pls sir reply thanxx