Expression.Error: We cannot convert the value "[Function]" to type Function

[gitouttatown]

I have a report that I'm converting to a PBIT and then moving to another location to create a PBIX. This will involve changing some parameters to connect to other data sources.

I'm getting a problem with one query. I have a table where I've added columns using "Add Column" "Invoke Custom Function"

This works in the original report. But when I try to run the query in the PBIX imported from the PBIT file, I get this error:

Expression.Error: We cannot convert the value "[Function]" to type Function.
Details:
Value=[Function]
Type=[Type]

This seems to be a type mismatch but I can't figure out why or how to fix it. I created the column usign the dialog to select my fuction and it's arguments.

The statement is:

= Table.AddColumn(#"Changed Type", "Prior Visit", each PriorVisitIEN([EPI], [IEN]))

where PriorVisitIEN is the name of a custom function in the current file. I've tested it, and it works and the arguments are the right types.

Any clues will be greatly appreciated.

Thanks !

[Poojara_D12]

Hi @gitouttatown 

The error "We cannot convert the value '[Function]' to type Function" occurs because Power BI loses the reference to the PriorVisitIEN function after exporting the report as a PBIT template and then importing it as a PBIX file. This happens because Power BI sometimes fails to properly restore function dependencies when reloading the file. To fix this, open Power Query Editor and check if PriorVisitIEN still exists in the Queries pane under the Functions section. If it's present but not recognized, remove the column where it's used and re-add it using Invoke Custom Function. Ensure that PriorVisitIEN is correctly set as a function and not mistakenly treated as a table or disabled. Additionally, if the function depends on other queries or parameters, verify that they are properly restored in the PBIX file. If necessary, manually recreate the function by adding a new blank query, renaming it as PriorVisitIEN, and pasting its M code. Finally, check for missing dependencies or broken references, as Power BI sometimes requires manual re-linking after moving reports between environments. This should resolve the function reference issue and allow the query to work correctly.

[rohit1991]

hi @gitouttatown ,

This happens when Power Query loses the function reference after exporting to .PBIT.

Solutions:

1. Revalidate PriorVisitIEN in Power Query.
2. Reapply "Invoke Custom Function" manually.
3. Ensure the function is defined before usage in applied steps.
4. Use explicit reference: = Table.AddColumn(#"Changed Type", "Prior Visit", each @PriorVisitIEN([EPI], [IEN]))
5. Check parameter types to match function inputs.

"The goal is to turn data into information, and information into insight." – Carly Fiorina

Need Power BI help? Connect on LinkedIn.

[C4YNelis]

Hi @gitouttatown ,

I realise this is a long time ago, but I just ran into the exact same scenario (not for the first time), so I decided to give it some more time. Maybe it will also help someone else at some point.

The problem has nothing to do with your function or function call itself (that is, not with the semantics at least), but you run into the privacy firewall. I for one used a public resource to validate dates from different cultures via the function. Changing this resource to a organizational resource solved the problem. Not really the way I'd like to see things, but it works at least!

Thanks to this thread: Expression.Error: Cannot convert the value "[Function]" to type Function (microsoft.com)

Cheers,

Niels

[Greg_Deckler]

@gitouttatown Something to try but not ideal. Try copying your function code from Advanced Editor and paste into notepad or something. Delete function. Recreate function and paste in code. Save. @ me if this does not work or if it does, I'd like to know!!

[gitouttatown]

@Greg_DecklerI tried deleting the function and adding it back, but the same error occurs.

Thanks for the suggestion.  Like you said, "not ideal" because it if worked... it would indicate some weird error in PBI. But hey I'll take any workaround I can get at this point!

[EugeneNovikov]

I had similar problem. My function referenced another query in that dataset directly. The problem disappeared when I passed that query as a function parameter like that:

let
CustomFunction = (Date as date, CurrencyCode as text, OperationAmount as number, RateTable as table) as nullable number =>

instead of:

let
CustomFunction = (Date as date, CurrencyCode as text, OperationAmount as number) as number =>
let
// Get the matching row from [raw]NBU Currency rates query
FilteredRow = Table.SelectRows(#"raw NBU Currency rates", each [Date] = Date and [Currency ID] = CurrencyCode),

It has something with the row context in which the add column function executes. Not sure what it is. I used ChatGPT to code these and then modified manually to get it working.