I have a use case where I would like to pass two arguments to a function generated by Function.ScalarVector (see https://docs.microsoft.com/en-us/powerquery-m/function-scalarvector).
I would like the second argument to, optionally, capture two or more columns of a table, e.g. ScalarFun([Col1], {[Col2], [Col3]}). I would expect this to pass a list of lists to the function itself. Alas, it does not.
Consider this example, if I define a function to simply capture the generated input list (which we'd normally pass on to a function doing something useful with the list) we can see that passing a list of two values and a concatenation of two values generate very different behaviour:
let
ScalarFun =
Function.ScalarVector(
type function (col as any) as any,
(t) =>
let
buf = Table.Buffer(t)
in
List.Transform(buf[col], each List.Distinct(buf[col]))
),
TestTable = Table.FromColumns(
{{"a","b","c"}, {"x","y","z"}},
{"Col2", "Col3"}
),
#"List syntax" = Table.AddColumn(
TestTable,
"List1",
each Text.Combine(List.First(ScalarFun({[Col2],[Col3]})), ";")
),
#"Concactenation syntax" = Table.AddColumn(
#"List syntax",
"List2",
each Text.Combine(ScalarFun([Col2] & [Col3]), ";")
)
in
#"Concactenation syntax"
Given this output I can see that the syntax where we combine two values into a list ScalarFun([Col1], {[Col2], [Col3]}) actually operate row by row. Concatenating two text values however works just fine (the function gets passed a list containing the entire column).
A list is just a value like any other, so I don't understand why my preferred syntax doesn't work?
Update:
It's clear to me that the function does not behave in the way I anticipated due to lazy evaluation. I see the same issue if I try to use a record. Passing a list in this rather convoluted way 'works' however:
Text.Split(Text.Combine({[Col2],[Col3]}, ";"),";")
I'm not going to post this as an answer because I don't fully understand why this (silently, without an error) breaks the intended behaviour of Function.ScalarVector.
