The chance to reply to this makes it feel like X-mas morning!
As a DM who has led many dives out at Niihau/Lehua I could make a long list of reasons that I feel that Niihau is unreal-fantastic-unique-jaw dropping-must do-unforgettable diving!
The diving at Niihau/Lehua gives you the opportunity to have multiple encounters with the Hawaiian monk seal! Monk seals are one of the most endangered marine mammals in the world, so this is an amazing opportunity. They have very curious behaviors and respond/react to divers very differently than they do on Kauai were they are occasionally seen underwater. It is not uncommon to have a seal(s) with you for 75% of the dive or more.
A combination of things also bring many other large critters to the area. This past season large Galapagos sharks were a highlight on many of the trips. Gray sharks and whitetips are very common and the gray sharks are often seen in groups. Hammerheads are spotted time to time along with blacktips, sandbar sharks, and an rare tiger or two over the course of the season. Mantas and eagle rays are also common to see along with both spinner dolphins and bottlenose. While it is a summer dive - if you get a early run in May it is possible to catch some of the last Humpback whales of the year. Other critters I have seen include the Hawaiian Grouper, sailfish, marlin (1), lagerhead turtles (2), TINKERS BUTTERFLY FISH!! the list goes on...oh, and giant Ulua's (trevally/jack) are amazing!
Critters over there on the whole just seem to be a diver rewarding combination of curious and territorial. Remember, they dont get to see divers very often.
All this and I haven't mentioned reef animals. If you can somehow look down and not out into the blue (I have a real problem with this) you will see countless eels, octopus, nudibranchs, flatworms, cleaner shrimp, banded coral shrimp, red lionfish... It is a naturalists dream location! Any photographer will also think they went to heaven.
Topography is pretty spectacular and the vis' is consistently well over 100ft though mother nature does occasionally throw a curve ball Niihau's way. The "Niihau Arches" dive in particular can be lower vis but still gets you into some 8-12 caves/archways/caverns that you'll ever see in Hawaii. The black coral and longnose hawkfish are incredible inside - along with ghost shrimp, sponge crabs and much more.
As far as the travel to and from Niihau it can be rough but not always. You can sleep on the way over it is usually so calm so that isn't an issue. Coming back can be a kicker but this past season was actually very mellow and I only had 3 rough crossings the whole season. I am not making light of the trip as it is a long day on the ocean with a long trip home - but as a California diver it isn't much different than some trips you may have made to Catalina or Channel islands.
Hope this helps. If I can help anyone with any Niihau questions I am more than happy to do so.
Have a good one! Aloha