The power supply actually has two AC-to-DC circuits, one that supplies a trickle of power when the unit is in low power states and one that provides full power when the system is in high power states. The former - called Standby Power - is there to allow the NUC to maintain its state, power CMOS (so it isn't draining battery), etc. and etc. The LED is an indicator that the power supply is providing this standby power. If this isn't working, the NUC might still operate, but something is still wrong. If this always the case? That is, if you completely unplug the unit from A/C power and then plug it back in, does the light come (back) on? If not - and presuming you still have a warranty in place - you should get the NUC (base unit and power supply) replaced by Intel. If you warranty is expired, a replacement power supply can be purchases fairly cheaply (unless we are talking Skull Canyon or Hades Canyon) through Intel's spare parts service. Places like Amazon typically also carry power supplies that can be used as replacements.
Hope this helps,