There were several:
My rankings. I’m going to include Switch as a part of both.
1. Gamecube to Wii - this was the emergence of an exciting new era for Nintendo, like a sleeping giant awakening from its 5-10 year nap in a ditch. Motion controls, online store, and everything sleek breathed a lot of new excitement and life into Nintendo consoles unlike any time in Nintendo’s history… it was like the square of all the massively hyped games like Super Mario Bros 3, Donkey Kong Country, and Ocarina of Time combined into one massive release. It’s also the first Nintendo console where I couldn’t play all the games I wanted to—something I’d only experienced on PSX before… although, now it’s commonplace for me (Steam and Switch, I’ve nowhere near the time I need to play everything I want. Even if I had 18 hours a day).
2. SNES to N64 - and this was probably the biggest “holy crap” moment, but it was too short lived. It was probably about 5 or 6 months before SNES became my primary Nintendo console again, and remained so for almost all the rest of the generation.
3. NES to SNES - could have been first place if Sega hadn’t done it first. But, all the same, SNES felt like a different kind of beast than the SMD, the graphics and sound were crisper, and it felt more advanced.
4. Wii U to Switch - I think the ability to finally play handheld mode, have the screen clearer, and being able to leave the room was a big thing. Being able to commute unashamed of being in my thirties and playing a video game console (as opposed to mobile) was a nice plus :D But that seems like handheld territory, really the big advantage over the Wii U were, IMO, the joycons, and the much faster experience. This felt like a proper Wii successor that just came out 4 years later than it should have… still, 4 years later than it should is still not even close to too late.
5. N64 to Gamecube - was pretty flat. The games weren’t impressive, as Dreamcast an PS2 already had great lineups. PS2 had FFX, Gamecube’s answer: Celda. I think part of my lack of hype for this console was that I was long done of N64 by the end of the generation, and Gamecube felt more like Nintendo’s floppy answer to Playstation 2 or Dreamcast (both consoles I already had) than a proper trendsetting Nintendo console.
6. Wii to Wii U - the only time I felt like I downgraded my system generation over generation. Technically it was better, but nothing seemed to work as well as it did on the Wii, everything was much slower and clunkier. There always felt like there was a lot less there than what the Wii had before it.
1. 3DS to Switch - this was a monumental leap.
2. DS to 3DS - Glasses free 3D blew me away, and I still like it to this day. The library wasn’t as strong as DS, but the leap in capabilities I feel is underrated… also, I really liked the Streetpass games. And no one had an aneurysm or a stroke, no one went blind, this system faced a lot of undue BS from the press, which probably hurt the library a lot… all in all, it actually had one of the stronger libraries of a Nintendo console, but it fell well short of the DS, commercially.
3. GBA to DS - this one took a bit longer to get going, so it wasn’t an immediate “wow!” as the early games weren’t spectacular or different until Brain Age came out and kicked off the casual Revolution (which gave rise to popularity of game series like Animal Crossing, a casual game already existing, and also the Streetpass games later. Casual gaming is still very alive on mobile). The fact that DS was kind of like a direct update to GBA SP helped it early on.
4. GBC to GBA - not a giant change here, and GBA was a bit of a late bloomer, not becoming great until the GBA SP because the visuals were so dim on the original model. IMO, GBA to GBA SP was more exciting than GBC to GBA. That’s why this gets last place by a distant margin despite being a great upgrade once the SP came out.