If my query is returning:
| Id | Column1 | Column2 |
|---|---|---|
| 123 | Value | |
| 123 | Value | |
| 456 | Value | |
| 456 | Value |
and I have a second query that returns:
| Id | Column3 |
|---|---|
| 123 | 50 |
| 456 | 75 |
How can I join the two querys by Id without the Column3 value appearing for every row where an Id is present - rather, for every row where it is present AND has a value only in Column1. For example:
| Id | Column1 | Column2 | Column3 |
|---|---|---|---|
| 123 | Value | 50 | |
| 123 | Value | ||
| 456 | Value | 75 | |
| 456 | Value |
You can calculate Column3 using the case() function with the logic you've described.
For example:
let q1 = datatable(Id:long, Column1:string, Column2:string)
[
123, 'Value', '',
123, '', 'Value',
456, 'Value', '',
456, '', 'Value',
]
;
let q2 = datatable(Id:long, Column3:long)
[
123, 50,
456, 75,
]
;
q1
| join kind=inner q2 on Id
| project Id, Column1, Column2, Column3 = case(isempty(Column1), long(null), Column3)
| Id | Column1 | Column2 | Column3 |
|---|---|---|---|
| 123 | Value | ||
| 123 | Value | 50 | |
| 456 | Value | ||
| 456 | Value | 75 |