Yeah the small systems are often very efficient, but you can't really use them for file serving from HDDs. USB (and even eSATA) enclosures are not a suitable replacement for internal SATA ports. Their controllers & firmware are not stable enough for heavy concurrent use and often crash or become non-responsive.
Idling an old Xeon or a 12100 at 4W is also a bit more challenging than a 1.1Ghz Celeron.
For the record, the PN40 not only has 1 SATA internal port, it even has an NVME-capable M.2 slot. I make no claims of the power consumption of the latter since I never used it.
If you want a shitton of SATA ports you obviously have to go somewhere else but then even the power consumption of the idling SSDs will start to be troublesome (and when in use will likely necessitate another PSU).
I also disagree with the assessment of eSATA (since it is basically external SATA), and think the claims of problems with USB to SATA controllers are very exaggerated. They all have terrible power management though; if I had to use one, I'd just cut USB power entirely on idle, which would btw also help with any controller issues.
M.2 SSDs have almost zero idle power draw, if that‘s what you meant?
HDDs can spin down and will not draw much in that state. I think it‘s a reasonable assumption for a file server at home that it will spend most of time in spindown.
I have seen some problematic reports on external enclosures on Reddit, but maybe they do work fine in many cases.
May be almost zero, but it's not zero! I have never tried DRAM-less NVME SSDs, but I have some samsung SATA 840-870 ones (tried several on this range) that consume around 0.2W on the most idle state they support. Considering my budget was 2W, it is not negligible...
Idling an old Xeon or a 12100 at 4W is also a bit more challenging than a 1.1Ghz Celeron.