Awesome but Impractical/Tabletop Games/Yu-Gi-Oh!

Reign-Beaux, Overlord of Dark World. He's the big daddy of the Dark World archetype (until they got a new big daddy), who gain effects when discarded by effects (but not costs) and gain better effects when discarded by an opponent's effect! This guy, however, needs to be discarded by an opponent's effect in order to do anything, and while there are ways to force an opponent's effect into letting you discard they're way too inconsistent to rely on as a main tactic. But if you do manage to discard him by your opponent's effect, you get a monster with a respectable 2500 ATK AND you get to destroy all your opponent's monsters OR their spells and traps! However, Goldd, Wu-Lord of Dark World is only marginally less awesome but summons himself when discarded by your own effect, so he's one of the more practical cards in a Dark World deck.
 * Most Tribute Monsters (monsters with 5 or more level stars) in the card game. You can spend an enormous amount of resources on it and lose the whole thing to a simple Bottomless Trap Hole. A good deal of this problem had to do with most skilled players having, at almost any given time, at least three or four different ways of destroying, removing from play, returning to the opponent's hand, or otherwise neutralizing any such monster. Anything that requires more than one tribute to get it on the field just isn't worth the effort considering how likely it is that your monster will be a victim to this.
 * By the time of the GX Era, iconic tribute monster (primarily Black Magician, Blue-Eyes, Red-Eyes) started getting support that bypassed tributing entirely. Even once cheated onto the field this way, the primary use for these cards is still to use support that needs them on the field, rather than them being good there.
 * And then there's the anime-only card, Ragnarok. The effect? If Dark Magician and Dark Magician Girl are on the field, all monsters on the enemy's side of the field can be removed from play. The cost? You have to remove every monster from your hand, deck, and graveyard from play. The cost was probably only there for the undoubtedly awesome visual effect: all of Yugi's monsters appear and swarm the enemy in order to banish it.
 * There's actually a real-world version now. It has the same cost but any two Spellcasters work. The downside? It only destroys one monster on your opponent's field. If this card could ever be used successfully in a tournament, it would be a ridiculous Crowning Moment of Awesome.
 * It could make a pretty effective combo with Return From The Different Dimension though, depending on how you used it.
 * In the card game, we have Final Destiny, which destroys all cards on the field at the cost of 5 discards. Since the maximum hand size is 6, playing Final Destiny leaves you with likely no hand and no field. There are also monsters like Super Vehicroid Stealth Union, a Combining Mecha Fusion Monster made of 4 specific monsters, with an effect that lets it attack all the opponent's monsters while negating their effects... and halving its own ATK, which isn't too high to begin with. Worst of all is the now-banned Victory Dragon, an extremely hard-to-summon monster with poor stats that, if it somehow attacks directly for the win, wins the entire match. However, there's no rule saying your opponent can't just forfeit the duel when you attack, sparing him the match loss.
 * Except in Japan, where there is. That's the reason it was banned in the first place.
 * Stealth Union doesn't wins by killing your opponent's monsters. It wins by the often overlooked stealing effect, which will take down anything non-Machine while working as a crazy strong wall. Sure, 4 specific monsters is a pain in the ass, but the real problem is on the Vehicroid Archetype itself, not Stealth Union.
 * Final Countdown. On the bright side, you win automatically after a certain number of turns, and unlike cards like Destiny Board or Venominaga, the only way to stop it is to win before that happens. But that certain amount of time? 20 turns. And it has a 2000-point cost. Most of the GX-era new uber-archetypes would also qualify, being hard to put together, overcomplicated, focused on one specific thing that was often useless (Fusions, counters, skipping the Battle Phase, discarding, mill) and kind of crappy, with some notable exceptions.
 * Final Countdown can easily become Awesome Yet Practical if you have Spell Economics, which will absorb the life point cost, then throw in a few stall cards like Nightmare's Steel Cage, Swords Of Revealing Light, Gravity Bind, with some monsters that can't be destroyed by battle like Marshmallon, along with a few Pyro Clocks Of Destiny to skip ahead a few turns and it becomes quite easy to get several wins with Final Countdown.
 * In fact, there are several cards that can keep your life points safe for the entire turn one way or another, so it's actually quite easy to build an effective deck utilizing Final Countdown: just fill the entire deck with these kinds of cards. The 2000 life point cost can even bee a boon here, since Frozen Soul, a trap card which causes your opponent's next battle phase to be skipped, requires your life points to be at least 2000 less than your opponent's to be activated.
 * Perfectly Ultimate Great Moth embodies this trope: he's the strongest Insect in the game, but his Summoning conditions are practically impossible.
 * And now the original Egyptian God Cards are being printed for legal tournament usage. Even with support for them, they tend to be useless in the game as they require THREE tributes to summon. Being one of the few things that requires three tributes to summon, it would make them useless if they didn't block any and all effects from going off when they were summoned. Sadly they still fall to a Mirror Force just as easily as any other monster when they attack.
 * Obelisk is considered the best, actually having some protection and good attack without further effort, but Ra is this trope incarnated. First, unlike Obelisk, he can't be Special Summoned at all. Second, he has no protection effect, except when he's Summoned. Third, his ATK and DEF is always 0, unless you pay all your Life Points except 100 to have it gain ATK and DEF equal to what you paid. If you attack your opponent directly, maybe you can finish him off before he can do his Cherry Tapping. Fourth, his last effect allows you to pay 1000 Life Points to destroy any monster. That's really useful, but you need to skip using his ATK gaining effect to use this, which also means keeping a 0 ATK monster completely vulnerable at your side of the field. Quite stark contrast to the anime, where, ignoring the borderline Deus Ex Machina it exhibited later in the arc, Ra's ATK and DEF were equal to the total of the three monsters sacrificed to summon it. Giving up life points to increase it was also an option for when you ended up with it on the field with no stats, however the user could give it however many points they wanted.
 * The legally reprinted Slifer the Sky Dragon has an ATK and DEF determined by the number of cards on your hand (most of the time you will not keep your hand size large) and can be run over by attacking monsters. Also, unlike Obelisk, it CAN be targeted by card effects. However, it can act as the powered-up version of Wanghu so your opponent cannot summon powerful monsters as long as you have a good hand size. Also, unlike Ra, it CAN be Special Summoned even if it only lasts one turn.
 * Horakhty, the ultimate incarnation of the God cards, requires you to tribute one each of the originally printed God cards (again, the Gods are already difficult to summon). Although it grants you victory of the duel, most players prefer finishing his/her opponent off by just using one of the God cards to deplete his/her opponent's Life Points. Its existence is actually considered a bad thing for the god cards, since it ensures future support can't reduce the difficulty of summoning them too much.
 * The one god card that does see play? The Winged Dragon of Ra - Sphere Mode, and only as glorified removal that bypasses immunity to it (as exceptionally few monsters have protection from being used as tribute).
 * Armityle the Chaos Phantom. It requires removing three cards (the Sacred Beasts, who can also fall under this trope) from play, however since each of those three cards themselves require three cards to play, it really takes a total of 12 cards to get out. It gains a whopping 10,000 attack during your turn, meaning any successful attack would almost certainly win you the game (unless your opponent had a +2,000 attack monster or really boosted his life points) since you start with 8,000 life points. As if being ridiculously hard to get out wasn't enough, it only gains 10,000 attack during your turn, can be destroyed by any common methods (except battle), and is actually inferior than the combine might of the cards it requires (the Sacred Beasts combined may have as little as 8,000 attack, but usually will have +12,000 attack).
 * Exodia. It gives you an instant win, but only if you have 5 certain cards in your hand at the same time (The cards can also be played as weak monsters). And you can only have one of each in your deck. The only way to use him efficiently is to have a deck completely built around getting him in your hand. Only once has an Exodia deck topped a notable tournament, reaching top 8 of a 30 man invitational (meaning it won twice, then lost).
 * Consider its really cool looking and effect in the anime, Gandora the Dragon of Destruction is considered this for four reasons. First of all, this card cannot be Special Summoned, which means that you will have to tribute two monsters on your field (or this card, where you only need to tribute it). Secondly, you will have to pay half of your LP to nuke the field except this card. Although it removes the cards from play, most players prefer JD or Demise as they have a much lower cost when nuking the field. Thirdly, this card gains 300 ATK for each card destroyed this way. However, the ATK boost is not impressive unless there are lots of cards on the field beforehand. Forth and lastly, this card is sent to the Graveyard during the End Phase of the turn it was Summoned.
 * Fusion cards partially depend on what card you're summoning. In the early days of the card game, there were worthless fusion cards like Flame Ghost or Fusionist, possibly the most useless cards in the game. Other fusions that are useless unless you get very lucky or are very good are Dragon Master Knight, requiring Black Luster Soldier (a ritual monster) and Blue-Eyes Ultimate Dragon, another fusion monster. Generally anything that requires more than one fusion monster is probably more difficult to use than the average player would have patience for. Even before Synchros, fusion monsters rarely saw play without a method of cheating them out.
 * Even if it has the second highest ATK points of any monster in the game, Machina Force is one of the best examples of Awesome but Impractical due to its extremely difficult summoning conditions of having Commander Covington on your field and sending three different monsters you control to the graveyard. To make things worse, it cannot declare an attack unless you pay 1000 Life Points.
 * Genesis Star God Sophia, from the latest Duel Terminal. It has a heavy summoning cost of requiring a Ritual, Fusion, Synchro, and Xyz monster to be on the field. However, they can be on either side of the field, and can be tributed against the opponent's will a la Lava Golem. Once it's summoned, its effect activates which banishes everything from both players' fields, hands, and graveyards. Neither Sophia's effect nor its summon can be stopped, meaning that if you pull her off, you'll get an instant 3600 shot at your opponent's life points, if not a game win due to their loss of resources (unless they pull a card like Dark Hole or Mirror Force out of their ass). Difficult to summon, by no means splashable, but in the right deck (and when you know your opponent enough to use his monster selection strengths against him), it can make quite a punch.
 * Back in the old days of the game, there was Gate Guardian, a 3750-ATK monster who can only be summoned by tributing his three components, each of which require two tributes themselves. The best bit? Gate Guardian's three pieces combined have twice as much ATK as Gate Guardian, and 3 monsters are harder to get rid of than a single target, so Gate Guardian is impractical even in a deck based around him.