Power Query in Excel

"Getting data" in Power Query means opening a connection to a source and pulling a copy of it into the Power Query Editor, a separate window where you build your recipe. The source itself is never modified. Four sources cover the overwhelming majority of an analyst's life:

• For a single CSV, use Data ▸ Get Data ▸ From File ▸ From Text/CSV, or the shortcut button Data ▸ From Text/CSV. Excel shows a preview with a detected delimiter and file origin (encoding). Click Transform Data to open the editor instead of Load, so you can clean before anything reaches a sheet.
• For an Excel workbook, use Data ▸ Get Data ▸ From File ▸ From Workbook. A Navigator pane lists every sheet and named Table in the file. Tick what you want and click Transform Data.
• For a whole folder of same-shaped files, use Data ▸ Get Data ▸ From File ▸ From Folder. This is the one that changes lives. Point it at a folder and every file in it can be combined into one table, next month's file included. Drop a new file in the folder, refresh, and it appears. Worked example 1 does just that.
• For a web page, use Data ▸ Get Data ▸ From Other Sources ▸ From Web, or Data ▸ From Web. Paste a URL and the Navigator lists the HTML tables Power Query found on the page. Pick one and click Transform Data.

How is the editor laid out?

Whichever source you choose, you end up in the Power Query Editor. Learn its geography, because it's stable across every version:

• The ribbon tabs come first. Home holds append, merge, Group By, Close & Load, plus column management. Transform holds the operations that change the selected columns in place: split, type, unpivot, trim. Add Column holds operations that create a new column and leave the source intact. View toggles the Formula Bar and opens the Advanced Editor.
• In the centre is the data preview, a sample of the result of the steps so far. People misread it more than anything else in the tool. The preview is a live rendering of the recipe applied to a sample. It isn't your data, and what you're actually building is the list on the right.
• On the right is the Query Settings pane, and the part of it that matters is Applied Steps, the ordered list of every transformation, top to bottom. Each entry is one recorded step (Source, Promoted Headers, Changed Type, Filtered Rows, …). Click any step to see the data as it was at that moment. The steps above it have run and the ones below haven't. You can rename a step with right-click ▸ Rename, and it's worth giving steps meaningful names. You can delete one with the ✕ to its left. You can reorder by dragging, but do it carefully, because order is logic. And you can insert a new step after any selected step.

How do you see the M code behind a step?

Turn on View ▸ Formula Bar straight away and leave it on. Every Applied Step is one expression in the M language, and the formula bar shows it. You'll rarely write M by hand at this stage. Reading it is another matter. Once you see that Changed Type is Table.TransformColumnTypes(...), the whole tool stops being mysterious. View ▸ Advanced Editor (also Home ▸ Advanced Editor) shows the entire query as one let … in block. The steps are just named lines, each feeding the next. So a query is nothing more than a pipeline of named steps.

The one keyboard fact that matters most is a worksheet shortcut, and you won't use it inside the editor. Ctrl+Alt+F5 runs Refresh All. The editor itself is deliberately ribbon-and-mouse driven. There's no rich shortcut layer to memorise, so what you'll drill here are precise ribbon paths, and hotkeys hardly come into it.