kql

How to get only distinct values from list


What I have: A datasource with a string column, let's call it "name". There are more, but those are not relevant to the question.

The "name" column in the context of a concrete query contains only 2 distinct values:

  1. ""
  2. "SomeName"

But any of the two a varying amount of times. There will only be those two.

Now, what I need is: In the context of a summarize statement, I need a column filled with the two distinct values strcated together, so I end up with just "SomeName".

What I have is not meeting this requirement and I cannot bring myself to find a solution for this:

datatable(name:string)["","SomeName","SomeName"] // just to give a minimal reproducible example
| summarize Name = strcat_array(make_list(name), "")

which gives me

 | Name
> SomeNameSomeName

but I need just

 | Name
> SomeName

I am aware that I need to do some sort of "distinct" somehow and somewhere or maybe there is a completely different solution to get to the same result?

So, my question is: What do I need to change in the shown query to fullfill my requirement?


Solution

  • take_any()

    When the function is provided with a single column reference, it will attempt to return a non-null/non-empty value, if such value is present.

    datatable(name:string)["","SomeName","SomeName", ""]
    | summarize take_any(name)
    
    name
    SomeName

    Fiddle