What is Power Query and how does it differ from regular Excel formulas?

Power Query — labelled Get & Transform — is a data connection and transformation engine built into Excel. You connect to a source, apply a sequence of transformation steps through the interface, and load the result. Every step is recorded, so refreshing re-runs the whole pipeline against new data.

How it differs from formulas:

• It is repeatable by design. Formulas transform data already in the sheet; Power Query defines a process that runs again whenever the source changes. Replacing a monthly manual clean-up with a refresh button is the whole point.
• It handles far more data. Transformation happens outside the worksheet grid, so you can process millions of rows and load only the summary.
• It connects to real sources — databases, folders of files, web APIs, SharePoint — not just what you pasted in.
• The steps are visible and editable. The Applied Steps pane is self-documenting in a way that a nested formula is not.

What it is especially good at: combining every file in a folder into one table, unpivoting cross-tabulated data into the flat format PivotTables need, merging tables the way a SQL join would, and splitting or reshaping columns.

Note: The rule of thumb worth stating: use Power Query to get data into shape, and formulas or PivotTables to analyse it once it is there. Mixing the two — cleaning with formulas that must be dragged down every month — is what Power Query exists to eliminate.