Power Query is a data manipulation tool frequently used for business intelligence and data analysis. Both Microsoft Excel and Microsoft Power BI support Power Query. A single source of truth and well-organized, error-free data are requirements for high-quality analysis. While many analysts spend many hours merging data, running lookups, and altering data, Power Query enables all of these tasks to be automated with straightforward steps. One of the most revolutionary tools available to any analyst dealing with data in Excel is Power Query. It will not only save up hours for analysts, but it will also lead to fewer manual errors and a better capacity to gather data from a single source of truth.
Power Query, as its name suggests, is Excel 2010 and later a most potent data automation tool. Data may be imported into Excel using Power Query from various external sources, including Excel workbooks, CSV files, text files, and the web, to name a few. The information can then be cleansed and made ready for our needs. Power Query includes a number of helpful capabilities, including the ability to append data and establish connections across various data sources. The joining of the data sets is what this is known as. The technology also allows us to categorize and condense data. Without a doubt, it is a beneficial tool.
Benefits of Power Query
- A data transformation tool called Power Query is accessible in both Microsoft Excel and Power BI.
- Power Query is a crucial tool for any analyst using Excel since it allows users to source data from a single source of truth while saving them hours of time and minimizing human errors.
- Power Query can successfully automate time-consuming manual tasks by remembering the user’s data transformation actions.
- Modifying data layout, connecting to central data sources, merging related tables, and combining numerous files are common use cases.
Power Query
A feature using which you can take data sets similar files in the same folder and then set up an automation process through which you can combine all of them and get the consolidated data. Here we are going to merge these data from four months using the power query
To merge the data from all sheets follow the steps below:
Step 1: Open a blank data workbook.
Step 2: Navigate to the data ribbon.
Step 3: Locate the get and transform data options. Select Get data > From file > From Folder.
Step 4: Navigate to Folder and Click Open.
This will list all your files in the Excel Folder. There is an option to combine directly but because our data is not 100% clean there are some blank rows on the top and headers that we do not need we would need to use the combine and transform data option so that’s what I will select if your data is clean and there is nothing else in the file you just want to combine everything as is you can directly use combine and load option.
This will open up the combined files dialog and here it will show you one of the files, in this case, it’s showing me the first file and then shows the data here
Step 5: Then click OK, At this point, you have merged data from all the files into your power query editor and, if you look closely you can see a new column with the name of the workbooks from which data is extracted and you’ll get a preview of this table.
Step 6: Edit the data as you like and when you are done Click on Close & Load. Your merged data is prepared at this point; all you need to do is add it to your new workbook. Therefore, select “Close & Load” from the Home Tab.
You can see the combined data in the worksheet and whenever you add a new file in the .xml file in the above folder the worksheet will get updated
Note: The above files should have the same naming format.