1. Confirm that Employee Number and Manager Employee Number are of the same data type (both should be Text or both should be Number). If they are different, you need to convert them
Table.TransformColumnTypes(Source, {{"Manager Employee Number", type text}, {"Employee Number", type text}})
Or, if they should be numbers:
Table.TransformColumnTypes(Source, {{"Manager Employee Number", type number}, {"Employee Number", type number}})
2. Power Query might not handle exact date-time comparisons properly due to time components. Ensure Effective Date is of type Date in both cases
Table.TransformColumnTypes(Source, {{"Effective Date", type date}})
3. The Table.RowCount method can sometimes return unexpected results if filtering does not match properly. Try using List.Count instead
4. If Manager Employee Number contains spaces or leading/trailing characters, normalize the data
5. If employee numbers are stored as text and case sensitivity matters, you may want to compare in lowercase
Final optimized query can be
let
currentEmployee = Text.Trim(Text.Lower([Employee Number])),
currentDate = Date.From([Effective Date]),
normalizedSource = Table.TransformColumns(Source, {
{"Manager Employee Number", each Text.Trim(Text.Lower(_)), type text},
{"Effective Date", Date.From, type date}
}),
matchingRows = List.Count(
Table.SelectRows(normalizedSource,
each [#"Manager Employee Number"] = currentEmployee
and [#"Effective Date"] = currentDate
)[Employee Number]
)
in
if matchingRows > 0 then 1 else 0
