15.2 Merge Data from Multiple Excel Files or Workbooks in Power BI (Power Query) | By Pavan Lalwani

#pavanlalwani #powerquery #excel #dataanalytics

In this video, we will explore how to merge data from multiple Excel files or workbooks in Power BI. Often, during trainings, people ask whether it is possible to merge queries or merge sheets from different data sources. The answer is a resounding yes! In this tutorial, we will demonstrate the practical application of merging data in Power BI.

We begin by creating a folder containing two Excel files: a master table and a transition table. Although the tables are the same, they are separated into two different files for demonstration purposes. We open both files simultaneously and arrange them side by side for easier comparison.

One file is named "Two Transition Table" and the other is named "1 Master Table." Both tables consist of identical data and structure, albeit in separate Excel files or workbooks. The transition table is labeled "Transaction 1," while the master table is named "Master 1."

After closing the Excel files, we proceed to Power BI where we click on "Get Data" and select Excel. This refers to video number 5.2 in the series. We first load the master table by choosing the file that contains "Master 1." Then, we click on the load button or the transform option.

Next, we repeat the steps for the transition table by selecting the file with "Transaction 1." Clicking on "Open" loads the table. Both tables are now displayed in the field section of Power BI. To further manipulate the data, we expand the field section.

To merge the queries, we go to the home menu bar and click on "Merge Queries as New." The transaction one is selected from the top table, while the master one is chosen from the bottom table. The common column used for the merge is the "Employee Code." We want to bring the "Salary" column from the transition table into the master table. This is achieved by selecting all values from the first table and only matching columns from the second table, similar to VLOOKUP functionality.

By default, the type of join applied is a LEFT JOIN, mimicking VLOOKUP. To apply VLOOKUP, position the desired table above the other in the Merge Queries window. Once ready, click on the OK button to initiate the merge.

The merged table will now display all the columns. To narrow down the selection, we can click on the expand button and choose only the "Salary" column. Confirm the selection by clicking OK. This approach provides more flexibility compared to traditional VLOOKUP, as it allows for merging multiple columns.

In summary, this video tutorial has demonstrated how to merge queries in Power BI by combining data from two separate Excel files. Now you are equipped with the knowledge to effectively merge data from multiple sources for enhanced analysis and visualization in Power BI.
---------------------------------------------------------
💼 Invest in your future with our Power BI 2023 NEW Course at an incredible 50% discount:
🔗 https://learn.pavanlalwani.com/course...

📚 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!
🎉🔗 https://learn.pavanlalwani.com/s/store
You can also explore our Power BI DAX 2023 course and get a 360º understanding of DAX
🎉 🔗https://learn.pavanlalwani.com/course...

Invest in yourself. Become a Power BI expert. The future is yours! 💼💪"
📥 Download Practice Material from Server 💾:

#datacleaning #businessintelligence #powerbi #excelfunctions
---------------------------------------------------------
Timestamps:
00:00:03 Merging Queries from Different Excel Files 📊
00:00:22 Explaining the Two Excel Files 📂
00:00:50 Name of the Transition Table 📝
00:01:12 Name of the Master Table 📝
00:01:40 Loading the Master Table 📥
00:02:08 Loading the Transition Table 📥
00:02:38 Merging Queries as New South 🔄
00:03:07 Expanding the Merged Table 📊
00:03:26 Selecting the Salary Column 💰
00:03:45 Final Result: Merged Tables 💼