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

preview_player
Показать описание
#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:

📚 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 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 💼
Рекомендации по теме
Комментарии
Автор

🚀 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. 💯💼

PavanLalwani
Автор

video's are short and simple. planning, Designing series and executing must not have been easy. As a outsider we feel easy. Thankyou for creating series of video. Appreciate your hard work..

checkmateatnet
Автор

Very Nice Pavan.. This is really helpful. so precise, so informative.. Please keep it up and Thank you.

harshalpatil
Автор

Thank You! Does this method keep connection to original Excel files as source data? When info changes in each of the individual Excel files, does the PowerBI query update the info in the merged view? Also, can there be 2-way connection and updating? Can data be updated manually in the PowerBI view, and changes can be automatically updated in the original Excel source files?

shabinashukoor
Автор

Can this be applied using folder instead of importing data using excel? Just curious.

rajdeep
Автор

Sir when we apply left join
Then why is it not showing all records of left table.
It only shows the matching records (inner join)

_saurabhjindal
Автор

Hi sir, Can one to many/many to many files be joined while data is being loaded instead of one by one?

யாழ்குறும்பு
Автор

Hi Pravan, Again big thank you for the course. One doubt, could you please explain what is Use fuzzy matching to perform the match if possible.

odudabdul
Автор

Hi, How to merge more than 3 excel files with one primary key. All the 3 or 'n' excel files are having same data. It should be dynamic.

iamshivarth
Автор

If the table join was done with 'Power View' instead of 'Power Query', what would be the difference?

யாழ்குறும்பு
join shbcf.ru