'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:
- ""
- "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 strcat
ed 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 1:[1]
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 |
Solution 2:[2]
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.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
Solution | Source |
---|---|
Solution 1 | David דודו Markovitz |
Solution 2 |