Teach Power Query to extract a code from examples

Power Query uses AI heuristics to infer transformations from examples. This exercise follows the product documentation.[6][1]

In Excel for Microsoft 365, start with a table whose Code column contains BOX2-017-UK, PEN-42-EU and PAD-006-US.[2][3]

1. Select a table cell, then Data → From Table/Range. In the editor, select Code and choose Add Column → Column From Examples → From Selection.
2. Enter 017 beside BOX2-017-UK and 42 beside PEN-42-EU; press Ctrl+Enter. Expect 006 for PAD-006-US.
3. Inspect the proposed transformation above the preview: it should extract between hyphens. Correct a wrong suggestion by double-clicking that result and providing another example.
4. Choose OK and inspect the new Applied Steps entry. Use the column’s type icon to select Text, preserving leading zeros; then Home → Close & Load.

Add CARD9-120-EU to the original table. Select a cell in the loaded results, choose Query → Refresh → Refresh, and expect 120.[4]

Digits in the prefixes catch a rule that simply collects every number.

Report an error or suggest a correction