14.4 Append multiple Excel files from a folder in Power BI (Power Query) | By Pavan Lalwani

#pavanlalwani #powerquery #excel #dataanalytics

In this Power BI tutorial, we will learn how to append multiple Excel files from a folder. If you have a folder where you regularly paste files with similar data, you can automate the process of appending them in Power BI. We will demonstrate this with an example where we have a folder containing monthly sales files. Each file represents a different month.

We will first explore the contents of the Excel files and see the structure of the data. Then, we will import the folder directly into Power BI and extract the necessary data for analysis. We will use the Power Query editor to remove unnecessary metadata and retain only the relevant columns. Finally, we will rename the columns and adjust the data types.

By the end of this tutorial, you will have a clear understanding of how to append multiple Excel files in Power BI and streamline your data analysis process.
---------------------------------------------------------
💼 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
---------------------------------------------------------
Timestamps:
00:00:03 Exploring Power BI: Appending Multiple Excel Files from a Folder 📂
00:00:29 Files in the Folder: April, Feb, Jan, May, March 📂
00:00:59 Examining Data in April 2020 Excel File 📊
00:01:27 Similar Column Names in June 2020 Excel File 🔄
00:01:46 Importing the Folder into Power BI 📥
00:02:41 Cleansing the Data: Selecting Relevant Columns ⚙️
00:03:26 Extracting Data from the Compressed Format ✂️
00:04:16 Creating a Custom Column with Extracted Data ⚙️
00:05:15 Selecting Only Tables of Interest 🔄
00:06:05 Renaming and Formatting Column Headings 🔀
00:06:35 Finalizing the Clean Data in Power View ⚒️

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

[pusdejazz]

Thanks Pavan - you are brilliant and very explanatory👍👍👍

[IrfanKhan-wvrh]

Sir.. Dil jeet liya.. what a explanation.. Thank you sir!

[chatkara]

Explained in very simple way, great Pavan!!

[sockkhimpah]

Thank you so much, Pavan!
I'm impressed with your video, crisp and clear, straight to the point. Truly amazing!!!

[TheWestcoastrangers]

Pavan, This was super helpful. I am appending raw csv files from a LinkedIn export. It is always in the same format, but it is not in tables. Do you have any advice about how I might append that data?

[Ehabalsharif]

Thank you so much. The video is so helpful and explained in such a nice and simple way. This will save me so much time.

[geraldsahd]

Thanks Pavan! Everyone has a video on CVS but Excel is a little tricky

[shahjayesh]

Hi. What if first I want to filter n group some data and want each file from folder to go through similar process before going into table?

[vedavathit.mahadeva]

Hi Pavan, while appending multiple files of similar format when I added custom column in new column I got text as error. The file format is 1-16 rows is basic details of customer and from 17th row I have table format in all the files. What could have gone wrong. Thanks a lot for your excellent videos. Could you please let me know on this.

[rpatel]

Do you have any video that help me "count on of consecutive days and no of sequence" in power query. Thanks

[avantikaatul]

Sir, @5:12 Custom.Name and Custom.Item are same (have the same name), what's the difference between them?

[anuragshrivastava]

is there any course on power bi including dax advance level

[smmahiuzzamanmahin]

I have another query. Each excel file contains multiple sheets with different headers. (the contents are not in a table and I also don't want to convert it as table in excel).

How can I set the first row as the header for each sheet while appending in Power Bi?

[aditimanojkumar]

When I do this..file append with heading.i ma appending Excel file...plz suggest

[ritujain]

Hi Pavan
If i have to add columns in the table how do I get them in power BI after refresh of the data

[paramjeetsingh]

thanx sir, ,,, when you type excel. then automatically shows workbook but in my case i have to type completely Excel.Workbook why ?? thanx

[raghunadhamara]

Hi pavan sir can we have a session on power pivot options in power bi please

[chanchalarya]

can we add excel sheet in power bi with calculation and formula?

[gauravkhurana]

Can you show how to auto refresh on the Power Bi service online?