Importing & Cleaning CSV Data
Use Power Query to import a raw CSV file, navigate the Power Query Editor, and apply a series of transformations to produce a clean, analysis-ready table.
Power Query is Excel's built-in data transformation engine — a dedicated workspace that lets you connect to, clean, and reshape data sources without formulas or manual editing. Every action you take inside Power Query is saved as a named step, which means your entire transformation process is recorded and replays automatically the moment your source data changes.
In this lab, you'll open the Power Query Editor for the first time. You'll import a raw CSV file, learn the layout of the editor interface, remove noise from the top of the file, restructure the column layout using Unpivot, and load a clean, properly typed table back into Excel — ready for analysis.
| # | Steps | Directions |
|---|---|---|
| 1 | Open a Blank Excel File and import data. | |
| 2 | Ensure the file is using a comma delimiter, and press Transform to load the Power Query editor. | |
| 3 | Look around the Power Query Editor including: Data Area, List of Queries (left), Query Steps (right), Ribbon (top). | |
| 4 | Delete 10 unwanted header rows from CSV data. | |
| 5 | Identify correct headers from the data. | |
| 6 | Get familiar with the query steps pane, identifying one step per transformation. | |
| 7 | Delete unwanted Ref1 and Metric columns. | |
| 8 | Highlight Store & Cat columns, and unpivot the rest. | |
| 9 | Filter blank rows from the Value column. | Use the column header dropdown, same as in Excel. |
| 10 | Rename Attribute and Value columns to Date and Sales, respectively. | Double-click on the column headers to rename. |
| 11 | Change Date and Sales column types to Date and Decimal Number, respectively. | |
| 12 | Rename query. Close and load query to worksheet. | |