Expression.Error: We cannot convert the value to type Function
By Tahir Nas · Examples run in our simulator · Updated
Expression.Error: We cannot convert the value 1 to type Function.
Something that isn't a function was called with (), or passed where a function is expected. Common when a parameter has the same name as a function, or each was left out of a transform.
Common causes
- Calling a step or parameter as if it were a function: Rate(2).
- Passing a value instead of each … to Table.TransformColumns.
- A custom function query that was changed to return a value.
How to fix it
- Only call names that hold functions; check the step that defines the name.
- Wrap transforms in each: {"Qty", each _ * 2}.
- Rename parameters so they don't shadow functions.
Find the step that fails
- Open the query in the Power Query editor (Excel: Data → Queries & Connections → Edit; Power BI Desktop: Transform data).
- Click the steps in Applied Steps from the top. The first step that shows the error is where it starts; later steps only repeat it.
- Read that step's formula in the formula bar (View → Formula Bar if it's hidden) and compare it with the columns and values the previous step produced.
Minimal example
Raises the error:
let Rate = 1.2 in Rate(100)Fixed:
let Rate = (amount as number) => amount * 1.2 in Rate(100)Both snippets are run by the Power Query Exercises simulator on every build: the first must raise Expression.Error, the second must run.
Practise avoiding it
- advanced~12 minPro
Write a shipping-cost function and apply it (kite shop)
Define a function that prices shipping from quantity and region, then add a Shipping column with it.
- intermediate~9 minPro
Write a tax function with an optional rate (shop)
Define Gross(net, optional rate) that uses 20% when no rate is given, and apply it to every order.
- advanced~12 minPro
Add a running total in date order (consultancy)
Sort claims by date, then add a Running Total of Amount from the first claim to each row.
- advanced~10 minPro
Trim every column except Amount with one generated step (members)
Text columns are padded with spaces, and new ones keep appearing. Build the Table.TransformColumns list from the column names.