14.2 How to Append Excel Sheets, Tables in Power Query Power BI | Power Query Tutorial | ETL

#pavanlalwani #powerquery #excel #dataanalytics

In this video, we will learn how to append multiple sheets or tables from one single Excel file in Power BI. This process can be easily automated with just a few clicks, making it a complete automation process. We will explore the step-by-step process of appending multiple Excel tables or sheets into Power Query Power BI.

First, we have a file called "Monthly sales single file" which contains an annual sales data. Typically, separate sheets are created for each month's sales and kept in one Excel file. Our goal is to combine these sheets into one single file in Power BI. We have seven files with similar data, including the column headings, values, and the number of records. The date column and client names may vary, but the column names remain the same. The column positions and the number of columns can also vary.

To begin the process, we will open Power BI and import the Excel file using the "Get Data" option. This will display all the sheets present in the file. Since we want to append all the sheets, we can select them all. Each sheet contains 10 records, so the expected output should be 70 records. We can then click on "Transform Data" to load the sheets into Power Query.

In Power Query, we can see all the sheets on the left-hand side pane. Our next step is to create a new query where all the sheets will be appended. We can do this by selecting the "Append queries" option in the Home tab. Since we have six sheets, we will choose the option for three or more tables. We will then select the sheets one by one and click on OK. The final output will be displayed, and we can rename the query as "append one".

The appended query will have the three columns from the original tables and a total of 70 records. We can further rename the query as "Annual Sales 2020". The best part about Power Query is that if any changes are made to the original files, the appended query will be automatically updated.

Overall, this video provides a comprehensive guide on how to append multiple sheets or tables from one single Excel file in Power BI. Follow along with the step-by-step instructions to automate this process and effectively analyze your sales data.

---------------------------------------------------------
💼 Invest in your future with our Power BI 2023 NEW Course at an incredible 50% discount:
🔗 learn.pavanlalwani.com/courses/Power-BI-2023-63…

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

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

#datacleaning #businessintelligence #powerbi #powerquery
---------------------------------------------------------
Timestamps:
00:00:03 Intro to Appending Multiple Excel Tables or Sheets 📊
00:00:23 Monthly Sales Single File and the Need for Appending 📂
00:00:44 Understanding the Structure of the Excel File 📝
00:01:11 Opening the Excel File in Power BI Get Data 💻
00:01:41 Transforming Data and Loading Sheets into Power Query Power BI 🔄
00:02:10 Option to Append Queries 🔄
00:02:39 Appending Multiple Sheets as a New Query ✨
00:03:08 Renaming and Finalizing the Appended Query 💼
00:03:32 Automatic Updates for Any Changes Made in the Excel File 🔄
00:03:55 Conclusion and Refreshing the Data in Power BI ✅