Search code examples
excelpowerquerymlet

M (PowerQuery), set the value of a non-primitive variable in a let statement

I'm writing a custom M Language (PowerQuery in Excel) function to query a RESTful interface. This interface has a large number of optional parameters.

Starting with a simple case- I handle an optional limit passed as a simple (primitive) value as follows-

/* 
 * RESTful API Get from the named endpoint
 */
(endpoint as text, optional limit) =>
let
    // query limit
    // If limit is supplied as a number, it will be converted to text
    // If limit is not supplied it will be set to the value "1000"

    limit = if limit <> null then Text.From(limit) else "1000",

As the full API has many paramaters I wanted to use a Record to pass them to the function, but then I realised I don't know how to persuade M to write the default values into the parameter record.

I tried a couple of options.

  1. Direct access-
(endpoint as text, optional params as record) =>
let
    params[limit] = if (params[limit] = null) then "1000",

the result is a syntax error-'Token equal expected'

  1. Merging the new value of limit as a Record with "&"
(endpoint as text, optional params as record) =>
let
    params = params & if params[limit] = null then [limit = "1000"] else [],

result syntax error-'Token Literal expected'

I'm clearly missing something about the syntax rules for let statements, I know I need a variable = value assignment, and it looks as if putting anything other than a plain variable name on the LHS to write elements inside a structured value is not allowed, but i'm not sure how to acieve this otherwise?

Solution

  • Not sure exactly what you want here, but to create a List of Records where some Records have a default parameter and others do not, you could try something like:

    (newParams as record) => 
let 
    default = [limit=1000, param2=2, param3=3],
    final = Record.Combine({default, newParams})   
in 
    final

    With regard to Record.Combine, the beauty is that the right hand record will override the left hand record if both are present; and it will just add to it if nothing is present.

    So something like:

    let
    Source = [limit=400, param3="x", param7=246],
    conv = fnParams(Source)
in
    conv

    =>

    enter image description here

    Depending on the required format of your output string, you can build it using List.Accumulate. eg:

    let
    Source = [limit=400, param3="x", param7=246],
    conv = fnParams(Source),
    list = List.Accumulate(List.Zip({Record.FieldNames(conv), Record.ToList(conv)}), "",
        (state,current) =>state & "&" & current{0} & "=" & Text.From(current{1}) )
in
    list

    => &limit=400&param2=2&param3=x&param7=246