How To Create a Query in Excel Power Query [Tutorials]

Excel Power Query is a robust data transformation and preparation tool integrated directly into Microsoft Excel. It empowers users to connect to a wide array of data sources, clean, reshape, and combine data with remarkable ease. Power Query simplifies the extract, transform, and load (ETL) process, enabling efficient data analysis.


Using Power Query, you can perform the extract, transform, and load (ETL) processing of data.

Advantages of Using Power Query in Excel

• Native Integration: Power Query is now a core feature of Excel, eliminating the need for separate add-ins.
• Diverse Data Connectivity: Seamlessly import data from various sources, including CSV, text files, databases (SQL Server, etc.), web pages, and more.
• Simplified Data Transformation: Replace complex formulas like VLOOKUP, XLOOKUP, and SUMIFS with intuitive, point-and-click transformations.
• Data Relationship Building: Create relationships between different data sets for comprehensive analysis.
• Automation Through Applied Steps: Automate repetitive data cleaning and transformation tasks by recording and replaying steps.
• Efficient Data Merging: Quickly and accurately combine data from multiple sources.

How do I create a query in Excel that automates the cleaning up of my CSV file sent to me each month?

Automating CSV File Cleanup with Power Query

Eliminate the need for manual data cleanup by creating a Power Query query that automates the process for your monthly CSV files. Here's how:

1. Open a New Workbook: Start with a blank Excel workbook.
2. Import Data: Navigate to the "Data" tab, click "Get Data," select "From File," and then "From Text/CSV."
3. Select Your File: Browse to your CSV file and click "Import."
4. Transform Data: In the preview window, click "Transform Data" to open the Power Query Editor.
5. Apply Transformations: Use the Power Query Editor's intuitive interface to clean and transform your data. For example, filter columns, remove duplicates, change data types, and more. Each action is recorded as an "Applied Step."
• For Example: To filter product categories, use the dropdown arrow in the “ProductCategoryID” column, and select the desired filter values.
6. Load Data: Once the transformations are complete, click "Close & Load" to import the cleaned data into your Excel worksheet.
7. Refresh with New Data: To update the query with next month's CSV file, go to the "Data" tab, click “Queries & Connections”, then double click your query. Within the Power Query editor, select the “Source” step within the "Applied Steps" pane, and then update the file path to the new file.
8. Finally, click "Close & Load" to refresh the data.

Supercharge your team's productivity with Excel Training. We work with you to customize a course to teach your team how to use this powerful tool to simplify your workflow, boost efficiency, and unlock valuable insights from your data.