KQL query to only join tables with a column value

Question

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

Answer 1

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