Expression.Evaluate(_, #shared) in PowerBI Service

[Nolock]

Hello,

I'm searching for an explanation of the following problem:

I'm developing a lot of PowerQuery queries and recently, I had to use Expression.Evaluate:

Expression.Evaluate("my expression using the internal function Text.Upper ", #shared)

It works in Power BI Desktop. When I publish my dataset to PBI Service (Premium) and let it refresh, the following error comes:

Does anybody have an idea why it doesn't work?

Actually, I have a workaround for my current case, but it is not a general solution:

Expression.Evaluate("my expression using the internal function Text.Upper ", [Text.Upper = Text.Upper])

EDIT: Or if you need a list of all Text.* function names in PowerQuery, you can create it manually in advanced and use it as a record for the new scope:

let
    Shared = #shared,
    TextFunctionNames = List.Select(Record.FieldNames(Shared), each Text.StartsWith(_, "Text.")),
    Result = List.Transform(TextFunctionNames, each _ & " = " & _ & ",")
in
    Result

Thank you very much for an explanation or a hint in advanced.

Best regards,

@Nolock 

[v-frfei-msft · Accepted solution]

Hi @Nolock ,

There is an idea about that, Seemed that the function is not fully supported yet.

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/19683592-provide-full-support-for...

[v-frfei-msft · Accepted solution]

Hi @Nolock ,

There is an idea about that, Seemed that the function is not fully supported yet.

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/19683592-provide-full-support-for...

[Nolock]

Thank you, @v-frfei-msft. I've voted for the idea.

[ImkeF · Accepted solution]

Meanwhile you can use this workaround, if you're using many functions in your expression: https://www.thebiccountant.com/2018/05/17/automatically-create-function-record-for-expression-evalua...

..I know it's a pain 😉

[Nolock]

Hi @ImkeF,

thank you.

In the end, I've solved this problem with creating a list of all Text functions. 

let
    Shared = #shared,
    TextFunctionNames = List.Select(Record.FieldNames(Shared), each Text.StartsWith(_, "Text.")),
    Result = List.Transform(TextFunctionNames, each _ & " = " & _ & ",")
in
    Result

The list is stored in a utility group, the load is disabled and I just copy all the values of the list into a record. This record, let's call it "All text functions scope", is then used like: Expression.Evaluate("my expression", #"All text functions scope").