Getting only unique values within a category in Kusto Query Language (Azure Monitor Logs)

Question

I have data in this format :

Category Session_ID  Step_Name

  A         100        1
  A         100        2 
  A         200        1
  A         200        1    <--
  A         200        1    <--
  A         200        2
  B         300        1
  B         300        1    <--

I need to remove the duplicate values of step names within each Session_ID. For example in ID = 200, there are three '1's which need to be changed to one '1', so the final data looks like :

Category Session_ID  Step_Name

  A         100        1
  A         100        2 
  A         200        1
  A         200        2
  B         300        1

Undertrump

Maybe Distinct is working for: | distinct Session_ID, Step_Name – Takishatakken

Yes, this works, thanks. Can you put this as an answer. – Undertrump

Answer 1

You should use distinct operator in your case:

your_table
| distinct Category, Session_ID, Step_Name

then you can get the expected output like below, it works at my side:

Category Session_ID  Step_Name

  A         100        1
  A         100        2 
  A         200        1
  A         200        2
  B         300        1

And for your question in the comment, if you use the above query, the record like "A 100 1" would consider as one entity, and only if there are 2 or more exact same record like "A 100 1" would remain only 1 record if using distinct.

And if there is another record like "B 100 1", then the both 2 records "A 100 1" and "B 100 1" would remain.

Profundity

If we have a Timestamp column instead of Category, how do we proceed in that case? - considering repeat steps within a session ID will have different timestamps and will be considered as unique. – Undertrump

If you have a Timestamp column instead of Category, then what's your expected result? – Profundity

The repeated step with the latest timestamp should remain (within session_id) and the earlier repeated steps should be removed. (the step with the second arrow should remain and the step with the first arrow must be removed) – Undertrump

@user75252, could you please post a new issue about that? and provide the link when it's done :). – Profundity

Sure, I will do that. – Undertrump

This a more expanded question : #58519976 – Undertrump

@user75252, I'll look into this issue later. – Profundity

Let us continue this discussion in chat. – Undertrump