How to use the Excel Power Query Generator.
Power Query code should match the actual source schema and locale. Order transformations so type changes and filtering do not silently lose records.
Make the workflow fit your task.
List the source columns and their current types, then specify trimming, conversion and filtering in execution order. Use an explicit culture for locale-sensitive numbers or dates. Keep conversion errors visible and compare row counts after each step before loading the result.
- What you provide
- Source schema and transformation requirements.
- What you get
- M-code draft with ordered transformation steps.
See the input and the result.
Illustrative input and output · a teaching example, not a live WebAct run
Example input
Write a self-contained Power Query M example. Source columns ID and Amount are text. Rows: ID " A01 ", Amount "12.50"; ID "B02", Amount "oops". Trim ID and convert Amount using en-US; keep invalid amounts visible as errors rather than dropping rows.
Completed example
let
Source = #table(
type table [ID = text, Amount = text],
{{" A01 ", "12.50"}, {"B02", "oops"}}
),
Trimmed = Table.TransformColumns(
Source, {{"ID", Text.Trim, type text}}
),
Typed = Table.TransformColumnTypes(
Trimmed, {{"Amount", type number}}, "en-US"
)
in
Typed
Expected result in Power Query:
A01 → Amount 12.5
B02 → Amount Error, because "oops" cannot be converted to a number.
Both source rows remain available for inspection. This example has been checked against the documented functions; it is not a recorded Power Query execution.Load this input into the prompt, then copy it to WebAct to try the task. Your result may differ from the illustration.
Decisions and troubleshooting.
Should type-conversion errors be removed automatically in Power Query?
Keep them available for diagnosis unless an exclusion rule is agreed. Removing error rows can hide missing revenue or records.
Why does an amount convert correctly on one computer but fail on another?
The culture may interpret decimal and thousands separators differently. Supply the intended culture and representative values to the conversion step.
Reference for this workflow.
Try it with your own source.
Replace the example with your material in the task prompt. Keep the requirements you need, then copy the task into WebAct.
Customize and copy the task ↑