14.4 Append multiple Excel files from a folder in Power BI (Power Query)

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

Рет қаралды 58,934

3 жыл бұрын

#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:
🔗 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!
🎉🔗 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/course...
Invest in yourself. Become a Power BI expert. The future is yours! 💼💪"
📥 Download Practice Material from Server 💾:
#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 ⚒️

Пікірлер: 60

@PavanLalwani Жыл бұрын

🚀 Calling all data enthusiasts! 💼🌟 👉 Discover the power of Power BI with our Specialization course at a jaw-dropping 50% off! 🔗 Grab your discounted spot here: learn.pavanlalwani.com/courses/Power-BI-2023-63a05686e4b0a7b1ab4dfc8d 💥 Level up your analytical game and supercharge your career with the Power BI DAX course. 💯💼 🔗 Explore the possibilities here: learn.pavanlalwani.com/courses/Power-BI-Dax-634fa813e4b0c08ed56c9919

@0pusdejazz Жыл бұрын

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

@chatkara7344 3 жыл бұрын

Explained in very simple way, great Pavan!!

@IrfanKhan-wv8rh Жыл бұрын

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

@sockkhimpah6926 Жыл бұрын

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

@PavanLalwani Жыл бұрын

Thank you so much !!

@krishnakantapanda6835 9 ай бұрын

Useful information

@shaikhasif9489 2 жыл бұрын

Great @Pavan

@bsai1070 2 жыл бұрын

awesome clear and precise

@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.

@PavanLalwani Жыл бұрын

You're very welcome!

@shahjayesh 3 жыл бұрын

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?

@geraldsahd3413 2 жыл бұрын

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

@PavanLalwani 2 жыл бұрын

My pleasure

@AlterAgs 2 ай бұрын

Thank you.

@vedavathit.mahadeva237 2 жыл бұрын

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.

@jailsonj.martins5355 3 жыл бұрын

Nice, Very good,

@vasu4ug 3 жыл бұрын

Happy Birthday Sir..

@smmahiuzzamanmahin757 3 жыл бұрын

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?

@amanbhargava2702 2 жыл бұрын

Thank you😊😊😊😊😊😊😊😊

@avantikaatul 3 жыл бұрын

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

@manishlalsah8075 Жыл бұрын

Thankyou sir ..

@anuragshrivastava7855 Жыл бұрын

is there any course on power bi including dax advance level

@rpatel4698 2 жыл бұрын

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

@RaviKumar-gz9my Жыл бұрын

Very Nice Pavan. Appreciate

@PavanLalwani Жыл бұрын

Thank you !!

@ritujain7559 Жыл бұрын

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

@George-kg7cd Жыл бұрын

Hello Pavan, how could I refresh or load a chosen table? Because when I refresh or load the new data, it refreshes all the tables. Thank you!

@paramjeet975singh3 3 жыл бұрын

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

@chanchalarya983 Жыл бұрын

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

@aditimanojkumar5043 10 ай бұрын

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

@gauravkhurana4807 Жыл бұрын

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

@kumargaurav5644 2 жыл бұрын

Why didn't we click on Combine and Transform data as we did in the case of CSV files?

@MeRajiCrazy 2 жыл бұрын

Yes same doubt I got

@raghunadhamara6243 2 жыл бұрын

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

@AyushDubey205 Жыл бұрын

Hi Pawan, Clean names didn’t appear post selecting the data rather i had to promote headers which led to error. Please advise

@kunicagupta9659 6 ай бұрын

@PavanLalwani suppose we have multiple tables in particular first sheet of excel then is it possible to import each table from that single 1st sheet in powerbi

@PavanLalwani 6 ай бұрын

Yes

@madhubachu7392 3 жыл бұрын

Sir, Can you upload Power Pivot videos

@amritajain1111 Жыл бұрын

Hi, can anyone tell, I have created custom column, but inside column, "error" is there not "Table".

@Parimala_p 4 ай бұрын

Sir when appended the folder i am getting data source error how to rectify please help

@checkmateatnet 2 жыл бұрын

at 2:43 when you click on Binary at "content" column file size is displayed at bottom, when i try its directly navigating to that file /section. Do we need to change any setting for it?

@checkmateatnet 2 жыл бұрын

With keyboard arrow key its working