Intro to Power Query for Speed

Every technique in this course so far operates on data that's already in Excel. But data rarely arrives clean and ready — it comes from CSV exports, database dumps, web sources, or other systems with inconsistent formatting, unwanted columns, merged headers, and structural problems that need fixing before any analysis can begin.

Without Power Query, cleaning imported data means repeating the same manual steps every time: deleting columns, fixing date formats, trimming spaces, splitting names, renaming headers.

Where it lives:

• Excel 2016 and later (Windows): Data → Get & Transform Data;
• Excel 2019 and later (Mac): Data → Get Data (limited compared to Windows but covers the essentials);
• Excel 365: full Power Query available on both platforms.

Connecting to a Data Source

Power Query can connect to dozens of source types. The most common for everyday Excel users:

• CSV or text file (From File → From Text/CSV);
• Excel file (From Workbook);
• Folder with multiple files (From File → From Folder);
• Table in current workbook (From Table/Range);
• Web page (From Other Sources → From Web).

Practice Exercise

Open new Excel workbook and complete the following:

1. Data → Get Data → From File → From Text/CSV → select S3_power_query.csv;
2. In the preview, click Transform Data to open the Power Query Editor.

As you can see, Power Query interprets the columns with , as text instead of a numeric value. To restore the correct data:

1. Click on the Unit Cost column, right-click, and select Change Type → Text.
2. In the pop-up dialog, confirm Replace current.
3. Repeat the same steps for the Revenue column. Alternatively, go to the Transform tab → Data Type → select Text.
4. When the data is ready to load into Excel, click Close & Load.

You will get a table imported from the CSV file (this approach also works for other data sources) with minimal processing issues and correct values preserved.