CODEWITHSUNDEEP
kql
Fildor
Fildor

Reputation: 16059

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?

Upvotes: 0

Views: 5726

Answers (2)

Fildor
Fildor

Reputation: 16059

Wow, just as I posted the question, I found an answer:

datatable(name:string)["","SomeName","SomeName", ""]
| summarize Name = max(name)

I have no idea, why this works for a string column, but here I am.

This results in my desired outcome:

  | Name
>   SomeName

...which I suppose is probably less efficient than David's answer. So I'll prefer his one.

Upvotes: 0

David דודו Markovitz
David דודו Markovitz

Reputation: 44911

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

Upvotes: 1

Related Questions