Connect, Clean, and Refresh Excel Data with Power Query and Claude AI

If you download the same report every month, clean it up the same way, and paste it into the same spreadsheet, there is another approach: one you set up once. Power Query is Excel's built-in tool for connecting to data sources, transforming what arrives, and updating everything with a single click when the source changes, and most Excel users have never touched it. This class works to change that, with Claude writing the harder parts so you can focus on describing what you want rather than the syntax. The underlying language is called M; you don't need to learn it, since Claude writes it, explains it in plain English, and adjusts it when something isn't quite right.

Learners will have the opportunity to:

1. Explore the Power Query mental model: it sits between a data source and the spreadsheet, handling everything in between
2. Connect to a data source (a CSV export, a folder of monthly files, or an Excel workbook) and load it into Power Query
3. Build transformation steps visually: filter rows, remove columns, rename headers, fix data types, split and merge fields
4. Work with Claude to write M language for transformations the visual editor can't handle (custom columns, conditional logic, combining queries)
5. Set up a refresh so that when next month's export arrives, one click updates everything
6. Practice a university-specific scenario: combining monthly enrollment or budget exports from multiple departments into a single clean master file
7. Explore when Power Query is the right tool versus VBA or manual cleanup
8. Practice structuring source files so Power Query can find and process them reliably
 

Topics

- The Power Query mental model
- Connecting to CSV exports, file folders, and workbooks
- Building transformation steps visually
- Writing M language with Claude for advanced transformations
- One-click refresh pipelines
- Combining multi-department exports into a master file
- Power Query versus VBA versus manual cleanup
- Structuring source files for reliable processing