Create a query from "Deliveries" and change the type of "Qty" and "Price" to "Decimal Number".
Lesson theory
After you change a type, any text that can't become a number or a date turns into an Error. In Excel an error in a query shows up as an empty cell or a message on load, so handle errors on purpose:
- Replace Errors (right-click the column) — substitute a value such as 0:
Table.ReplaceErrorValues; - Remove Errors — drop the rows that have an error in the column:
Table.RemoveRowsWithErrors; - Replace Values — fix the text before changing the type (for example, "twelve" → "12");
- in M you can catch errors with a
try … otherwise …expression:each try Number.From([Price]) otherwise 0.
Decide deliberately: an unknown price can be replaced with zero (and flagged), but a row with no quantity should be removed because it skews the totals.
In the trainer, the Power Query editor sits under the sheet. Pick a source, add steps with the "+ Step" button, and check the result after each step (click a step in the "Applied Steps" list). The M formula of the step is shown above the preview. Remove a wrong step with "Delete last step".
Practice is part of Pro
The live spreadsheet, task checking and .xlsx files in all 170 lessons. Your progress is saved in this browser; sync across devices is coming soon.