1. Power Query: What It Is and Why Use It

Power Query is Excel's built-in ETL tool (Extract, Transform, Load). It loads data from files, folders, databases and web services, cleans and combines it, and sends the result to a worksheet or to the data model. You'll find it under Data › Get Data.

The main idea: you record the steps once (remove columns, change a type, filter, merge), and from then on you just click Refresh. New data runs through the same steps automatically. Every step is written in a language called M.

• Append — stack tables on top of each other, for example 12 monthly files;
• Merge — join tables on a key, like VLOOKUP but for a whole table at once;
• Unpivot — turn a "wide" table (months in columns) into a "long" one that PivotTables like;
• Group By — totals by group.

Availability: Excel for Windows has the full feature set; Excel for Mac has most of it; Excel for the web supports a limited set of sources. Power Query never changes your source data — it works on a copy.

The next lessons in this track are hands-on: you'll build real queries in a Power Query simulator.