Removing Blank Rows Using Power Query
This video is the first in a series of a free Excel Power Query tutorials.
Welcome to Power Query
This Power Query guide helps beginners use Power Query to remove blank rows from Excel, making data clean for analysis while highlighting how to avoid common errors.
Why Remove Blank Rows?
Removing blank rows is essential for three key reasons:
- It ensures data accuracy, preventing errors in analysis and reporting.
- It improves data processing efficiency, as datasets without unnecessary blanks are easier and faster to work with.
- It enhances data visualisation clarity, making charts and graphs more interpretable by eliminating irrelevant gaps.
These steps align with the goals of data cleaning and management, crucial for effective data analysis in Excel using Power Query.
Removing Blank Rows - Step-by-Step Guide
Follow our detailed instructions to use Power Query for combining data from multiple sources, cleaning it, and preparing it for analysis.
First ensure that you download the Remove Blank Rows in Power Query exercise files that you will need to follow along with this tutorial.
Now, extract the files to a folder. (Use the desktop if you need to),.
Next, open a brand new Excel spreadsheet.
After you've done that you want to click on the Data tab, on the Ribbon, then from the Get Data Drop down list select Launch Power Query Editor.
In older versions of Excel you can find Power Query by clicking on Data - New Query - Combine Queries - Launch Query Editor
Once the Power Query Editor has launched then click on New Source - File - Folder to locate the folder that contains all your Excel files you wish to clean.
When the Browse dialog box opens locate the folder that contains the downloaded exercise files.
Now, double click the Files Sent to Me folder so that you're entered it. Then, click Open.
After selecting "Open," a window presents the contents of the "Files Sent to Me" folder, displaying not just the filenames but also their dates of access, modification, creation, and the folder path, offering a comprehensive overview of file details for easier navigation and file management.
Click Combine & Transform Data.
In Combine Files box you will need to select the file that will be the basis for the structure of all the data within the folder. Power Query will automatically select the first file in the folder, however, you may want to select a different file. One thing to note is that the structure of the data needs to be the same across all the files for this process to work. If, for example, the column headings are different separate columns will be created.
The option to skip files with errors during data integration is available via a checkbox, suggested for use when rapid data consolidation is essential despite the potential for omitted information. It is advisable to later investigate and address these errors when time permits, ensuring data integrity and completeness.
Click OK.
The Power Query window opens and you can see the data in the main window.
Before we remove the blank rows we need to select the columns that we want to use.
Click the Date column, then hold down the shift key and click Area so that all the columns that you want to keep are selected.
Right click on any of the selected columns and select Remove Other Columns.
As a result the only columns remaining and the columns that you want to work with in Excel.
Now, click on Remove Rows - Remove Blank Rows.
By employing this method to eliminate blank rows, we ensure the removal is precise, targeting only those rows that are entirely null across all columns, thereby preserving the integrity and relevance of the dataset.
Once you have done that, you will need to load the data back into Excel.
Click Close & Load - Close & Load to
Next, ensure that Table is selected. Choose Existing Worksheet then click OK.
Click OK.
The data has be imported into Excel. If you want to return to Power Query you can use the Queries and Connections window on the right. Simply double-click on the Files Sent to Me connection.
Conclusion and Next Steps
This guide provides a detailed walkthrough for removing blank rows in Excel using Power Query, starting from downloading and extracting necessary files, to launching Power Query Editor from Excel. It covers combining data from multiple sources, selecting relevant columns to keep, and the crucial step of removing blank rows to ensure data accuracy and integrity. The process emphasizes the importance of a structured approach to data cleaning, aiming for efficiency and precision, and concludes with importing the cleaned data back into Excel.
Interested in booking an Excel course for you and your team to build your confidence in using Excel? Then check out some of our Excel courses:
We also offer a range of database and analysis courses using Power BI.
Other Excel Videos Tutorials
