Unanswered

Custom Formula - Expression.Error - Field Not Found

(0) Share

Report

Posted on by LoganK

Hi all! I have this code in Power Query to identify if someone was a manager during the month/year that is being evaluated against. I have inserted the Manager Employee Number from the list of available columns. Any ideas on how to fix this?

let
currentEmployee = [Employee Number],
currentDate = [Effective Date],
matchingRows = Table.SelectRows(
Source,
(r) => r[#"Manager Employee Number"] = currentEmployee and r[#"Effective Date"] = currentDate
),
count = Table.RowCount(matchingRows)
in
if count > 0 then 1 else 0

Categories:

General Comment

All responses (2)

Answers (0)

LoganK 6 on at

Like (0)

Report

Custom Formula - Expression.Error - Field Not Found

Thanks, SwatiSTW! I read through your response and tried to optimize based on your tips then also using your optimized query. Neither worked, but your optimized query got me closer.

This is my new error even though this field is native and comes with the initial dataset/table.
SwatiSTW 532 Super User 2025 Season 1 on at

Like (0)

Report

Custom Formula - Expression.Error - Field Not Found

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

Community site session details

Custom Formula - Expression.Error - Field Not Found

Helpful resources

News and Announcements

Quick Links

🌸 Community Spring Festival 2025 Challenge 🌸

Markus Franz – Community Spotlight

Kudos to the March Top 10 Community Stars!

Leaderboard

Featured topics

Product updates

Community site session details

Custom Formula - Expression.Error - Field Not Found

Helpful resources

News and Announcements

Quick Links

🌸 Community Spring Festival 2025 Challenge 🌸

Markus Franz – Community Spotlight

Kudos to the March Top 10 Community Stars!

Subscribe to this forum!

Select categories

Leaderboard

Featured topics

Product updates