Blog Article & Copyright by Lukas Hillesheim
This article shows how a KQL dynamic array can be transformed to a table.
1. Problem
Sometimes when you write KQL queries you might encounter a syntactic challenge where a list of values e.g. [a, b, c] is given that should be used as table expression. As an experienced BI developer you might wonder, why an KQL array cannot implicitly be treated as an singleton of a KQL table.
In SQL you can access a table that an OOP developer might see as an equivalent to an iterable object. Requesting a single column returns an output that can be treated as an array object and interchangeable as an tabular object. The only difference between both objects is the dimensionality.
The following is possible in the SQL language, but not in KQL:
- select [letter] from [letters];
Figure 1: Singleton - Single Column as Array
The KQL engine does not implicitly perform a conversion from a dynamic array expression into a table. The following causes an error:
- dynamic(["a", "b", "c"])
Figure 2: Error using dynamic array as table expression
At the time i wrote this article there was no built-in function like array_to_table. But it is possible to implement your own conversion with the tools that the KQL language provides. There are three ways that this article is going to show: 1. a conversion only for the currently given array, 2. a dynamic conversion where an dynamic array is passed to a lambda expression and 3. a stored function named array_to_table that makes your conversion reusable.
Solution 1: AdHoc Conversion of a Given Array
In the following code sample an array object named array1 with three letters is defined. With the help of print the array object is returned as an tabular object. This is the key point: in KQL the print-operator is not a kind of debug command like in other languages, but a way to return anything as table. In our sample the object returned by print contains the respective array that can be unzipped using mv-expand. The print-command allows us to optionally apply a column name to the tabular result, namely letter.
Figure 3: Solution 1
Solution 2: Dynamic Conversion
In this solution an array is passed as parameter thereby making the conversion dynamic. The array that is passed contains a list of integers:
Figure 4: Solution 2
Solution 3: Swap-Out the Business Logic to a Stored Function
In the following solution a stored function with an argument of type dynamic is created. The function contains the same code as above but has the advantage to persist the code and make it reusable.
Figure 5: Solution 3