Declaring variables in Power Query M Functions

Question

I am trying to create a reusable function in Power Query that will be used by several tables.

This works well (written in blank query):

let
    is_emergency = (color as text) => 
    if color = "red" or color = "orange" then 
       "emergency" 
    else 
        "not emergency"  
in
    is_emergency

I can call it using a custom column like this =emergency([color_column]).

However - my color column contains a lot of extra spaces so I somehow need to call Text.Trim() on the color-parameter. How to write this?

What I thought would work was to just write this:

let
    is_emergency = (color as text) =>
    color = Text.Trim(color, " "),
    if color = "red" or color = "orange" then 
       "emergency" 
    else 
        "not emergency"  
in
    is_emergency

but this gives me the error Token Literal Expected.

How to write it proper? I am aware I can use the Power Query GUI to create simple functions like this, but my real case is more advanced and I would like to understand the M syntax.

Karl Anka Karl Anka · Accepted Answer · 2018-06-27T10:35:45

I managed to solve it myself after some research.

let
    is_emergency = (color as text) =>
    // you need to put the variable declaration inside a new let - in
    let 
        trimmed_color = Text.Trim(color, " "), 
        // you also need to define the return value and return it in the new let - in
        return_value = if trimmed_color = "red" or trimmed_color = "orange" then 
            "emergency" 
        else 
            "not emergency"
    in
        return_value
in
    is_emergency

Declaring variables in Power Query M Functions

1 Answers