The most crucial step when starting data analysis is importing and organizing data. Power Query is a powerful tool available in Excel and Power BI that helps users retrieve and clean data from various sources with ease. In this article, we will introduce the concept and key features of Power Query and demonstrate how to use it in real-world scenarios through an example of importing a CSV file.
What is Power Query?
Power Query is a tool that allows users to import, transform, and prepare data for analysis. It is built into Excel and Power BI and supports connections to various data sources.
Key Features:
- Data Import: Retrieve data from Excel files, CSV, databases, web pages, and more.
- Data Cleaning: Filter, remove columns, and change data types for better organization.
- Merge & Append: Combine multiple tables into a single dataset.
- Automation: Save and reuse transformation steps for repetitive tasks.
Data Import – Supported Sources:
- Excel files, CSV files, and text files.
- Databases (SQL Server, MySQL, etc.).
- Web scraping to extract structured data.
Example: Importing a CSV File
Problem: Consolidating monthly sales data into a single table.
Steps:
- Navigate to Data tab → Get Data → From File → From CSV.
- Select the CSV file and open it in Power Query Editor.
- Review the data and click Transform Data to finalize the import.
Result:
| Month | Product | Sales |
|---|---|---|
| Jan | Laptop | 1,000,000 |
| Feb | Smartphone | 800,000 |
| Mar | Tablet | 500,000 |