Automate Data Clean-up with Power Query in Excel | Step-by-Step Guide
🚀 Tired of cleaning messy Excel data manually? In this video, learn how to automate data clean-up tasks using Power Query in Excel. Whether you're removing duplicates, fixing column formats, or filtering unwanted rows, Power Query can save you hours of work with just a few clicks!
🔍 What you'll learn:
Importing and transforming messy data
Removing blank rows and duplicate entries
Standardizing column names and data types
Automating repetitive cleanup tasks
Refreshing data with one click
🧠 Perfect for: Excel users, data analysts, students, and anyone who deals with repetitive data cleaning tasks.
👍 Don't forget to Like, Share, and Subscribe for more Excel automation tutorials!
00:00Hello friends, in today's video we will learn how automatic formatting of data can be done using Power Query
00:08And how can multiple seats be combined into one space
00:14Once you've got that set up it will mean your job will be much easier
00:22Because every time new workbooks are added to this folder all your transformations will be applied automatically
00:29And then you can use it to create Pivot Tables or charts or visualizations on the same master spreadsheet
00:37So let's get started and see how it works
00:41Here I have a shared folder where a new report comes every month
00:58For example here is an Employee Pay Roll Report which is generated every month
01:04This means that every time new data arrives, we don't have to do the same steps again and again, for this we can use Power Query
01:12Once you set up the transformation you can use it again and again
01:19So now we close this file, now we will open a new empty workbook, now we will go to data, then click on get data, then select from file and then from folder
01:37Because we want to bring all the new documents from the same folder, now after selecting the folder, we will click on Open, then you can see that the connection with the file folder has been established, here we will see all the files of this folder.
02:07And we can transform the data by opening power query. We have to include the new file in this and transform the data. So for that, now we will click on combine and select combine and enter the data.
02:37From here we can choose our first file, so here we will first take a new file, select it, then here you will see Preview, now we will click on OK,
02:54Now Power Query editor will open where we can insert unwanted columns, change position of columns, create new columns, etc.
03:16All these changes will be done once and will be automatically applied to every new file later. For example, if we don't need the column containing search data, we can remove it by clicking on Remove Column.
03:32Here we can drag the Paydate column to the Salary column,
03:41We can also create a new column, suppose we have to calculate the salary for the last day, which will be salary divided by 30,
03:49To create a new column here, we will go to Add Column above and click on Custom Column
03:59Here we will add the formula. If we want to calculate the salary for the next day, then we will click on salary and insert it from here.
04:09After this, type divided by 30 and click on OK
04:18So here the salary for the remaining day has been released
04:23Now we will double click on this custom and change the name of the column
04:29on day salary and press enter
04:32Here we can drag this column and place it along with the salary column
04:39You can type the currency data from here
04:47Here you can see that all the steps are being followed
04:50Now all our formats and calculations are compressed
04:56After this, go to the Home tab and click on Close and Load
05:02So you can see here all the transformations have been applied together and a table has been created.
05:12See, the column of salary has appeared here and in our folder, we had two files of March and April, their data is visible here.
05:23Now every time we insert a new month's report in this folder and click on Refresh, Power Query will keep applying the same steps on the new data
05:34No manual work required
05:36Currently we have files of March and April in our folder. Now we will put the files of every month in this folder.
05:45After inserting the file, you have to go to the data tab and click on refresh. That is all you have to do and you can see that the data of many months has also been inserted here.
06:00You don't have to do anything, you don't have to do any manual work. Now we can easily create a Pewter table or chart on this combined data. To analize our data, what we will do is press Ctrl+A.
06:30If not, select exit and click OK. Here, on the side, you will see a field list. From here, you have to select the paid account. We need these two, otherwise, we will drag them from here and remove them. After that, the salary field.
07:00For us to be able to see the trends easily, what we have to do is go to the insert tab and select the chart from here and our chart will also be ready and we will be able to see the trend of our data quote easily
07:17This way you don't have to repeat the same steps every time new data arrives
07:24Rather, work done once gets applied again and again.
07:27If you find this information useful then please like the video
07:33Share it with your friends and subscribe to the channel. Thanks, see you in the next video.
