This is documented behavior.  The bash(1) man page says, for set -e,

The shell does not exit if the command that fails is part of the command list immediately following a while or until keyword, part of the test following the if or elif reserved words, part of any command executed in a && or || list except the command following the final && or ||, any command in a pipeline but the last, or if the command's return value is being inverted with !.
[Emphasis added.]

And the POSIX Shell Command Language Specification confirms that this is the correct behavior:

The -e setting shall be ignored when executing the compound list following the while, until,if, or elif reserved word, a pipeline beginning with the ! reserved word, or any command of an AND-OR list other than the last.

and Section 2.9.3 Lists of that document defines

An AND-OR list is a sequence of one or more pipelines separated by the operators "&&" and "||" .

First, a disclaimer: Please don't parse the output of find. The code below is for illustration only, of how to incorporate command substitution into an Awk script in such a way that the commands can act upon pieces of Awk's input.

To actually do a line count (wc -l) on each file found with find (which is the example use case), just use:

 find . -type f -name '*txt' -exec wc -l {} +

However, to answer your questions as asked:

Q1

To answer your Q1:

Q1: is there a way to perform command substitution inside awk?

Of course there is a way, from man awk :

command | getline [var] Run command piping the output either into $0 or var, as above, and RT.

So ( Watch the quoting !! ):

find . | awk '/txt$/{"wc -l <\"" $NF "\"|cut -f1" | getline(nl); print(nl)}'

Please note that the string built and therefore the command executed is

wc -l <file

To avoid the filename printing of wc.

Well, I avoided a needed file "close" for that command (safe for a couple of files, but technically incorrect). You actually need to do:

find . | awk '/txt$/{
                       comm="wc -l <\"" $NF "\" | cut -f1"
                       comm | getline nl;
                       close (comm);
                       print nl 
                    }'

That works for older awk versions also.
Remember to avoid the printing of a dot . with find ., that makes the code fail as a dot is a directory and wc can not use that.

Or either, avoid the use of dot values:

find . | awk '/txt$/ && $NF!="." {  comm="wc -l <\"" $NF "\" | cut -f1"
                                    comm | getline nl;
                                    close (comm);
                                    print nl 
                                 }'

You can convert that to a one-liner, but it will look quite ugly, Me thinks.

Q2

As for your second question:

Q2: why is the first incantation above silently failing and is simply printing the filenames instead?

Because awk does not parse correctly shell commands. It understand the command as:

nl = $(wc -l $NF)
nl --> variable
$ --> pointer to a field
wc --> variable (that has zero value here)
-  --> minus sign
l  --> variable (that has a null string)
$  --> Pointer to a field
NF --> Last field

Then, l $NF becomes the concatenation of null and the text inside the las field (a name of a file). The expansion of such text as a numeric variable is the numeric value 0

For awk, it becomes:

nl = $( wc -l $NF)
nl = $ ( 0 - 0 )

Which becomes just $0, the whole line input, which is (for the simple find of above) only the file name.

So, all the above will only print the filename (well, technically, the whole line).