"A flashy feature that has limited usability for victory"
Uryu Ishida from Bleach possesses an attack like this—the deadly Sprenger, which requires him to set up a pentacle of Scheele Schneiders around his opponent and detonate their stored spiritual energy in a focused blast. He remarks that the attack takes so long to set up that it's impossible to use it in battle without a partner to distract the enemy.
Even his ultimate attack in the Soul Society arc functioned that way. Tearing off the glove briefly gave him immense spiritual power, after which his powers were drained to nothing. Awesome.
And now, we have Soifon's bankai, which is big, clunky, requires three days for full recovery from one use, and above all loud and flashy, not exactly the best tool for a (covert) assassin.
Naturally, it would kill everything, but we all know the reason for it not working out.
The Final Getsuga Tenshou, which Ichigo used to curb stomp Aizen. Funny thing is, he didn't even NEED to fully release it, Aizen had been turned into a pin ball even after going One-Winged Angel. But what makes it really impractical? Ichigo loses his Shinigami powers.
Then there's Ikkaku's Bankai, two massive swords attached to an even larger blade on Ikkaku's back. The weapon gradually gains power as Ikkaku fights, and is incredibly strong when it reaches full strength, but the problem is that his Bankai is very brittle and unsuitable for prolonged usage.
Made perhaps the king of this trope for Bleach in that with the reveal that Bankai very rarely naturally repair themselves, having a Bankai with the side effect of "becomes easy to break" essentialy means he has the self sacrificing price of the above abilities... but a power boost that only puts him on even footing with other captains.
Kyoraku's shikai leaves him Blessed With Suck, as the 'games' apply to him and can be exploited by his opponent. And, while not shown, it's reasonable to assume it would be problematic if he were to use it in the company of allies, because anyone nearby is said to be subject to Katen Kyokotsu's rules. And she appears to change those at whim, with multiple sets in effect at once.
Yamamoto's shikai alone, given that even his Battle Aura has a paralysing effect on anyone lacking the spiritual power to withstand it. There are three people who have been known to fight him when using his zanpakuto. Two are his students, the other is Aizen, who technically didn't bother, being Crazy Prepared enough to bring along a power nullifier.
In Project A-Ko, Mad Scientist babe B-Ko unleashes her killer mecha the Max 5000. It combines from several shape-changing vehicles in a drawn-out Transformation Sequence, she boasts of all its amazing killer powers and exotic weaponry, it's even referred to as "The Blue God of Death". But its gadgets and elaborate transformations don't leave any room for the pilot to control the thing, so it just stands there inert until the Heroic Schoolgirl A-Ko kicks it and it falls down.
The Double Zeta Gundam, a massive (even by Gundam standards) Combining Mecha with many BFG's, the most powerful one built into its head. The catch? it can only fire them about two or three times before the suit's capacitors are completely drained of energy.
MS Igloo is based on this premise, though some Super Prototype like Hildolfr tank turn out to be Awesome yet Practical. The worst offender is the Jormungand's BFG from first episode, being a big target with low accuracy and a very long cool-down period between each shot.
The Zeon loved this, constantly creating new classes of mecha instead of just concentrating on a few like the Gouf and Gelgoog. In particular, the Big Zam was based off of the "super tanks" from World War II that were prohibitively heavy and slow. It could take on long-range roles, but was useless at close-range.
In Victory Gundam, beam weapons have become so powerful, that the equivalent conventional weaponry (read massive Gatling gun) has too much recoil for it to be aimed properly.
Gundam Wing, the Wing Zero, a nigh-unstoppable mobile suit designed for wholesale destruction, rapidly becomes this when used for anything requiring just a bit more precision. Nothing says Awesome but Impractical like trying to defend civilians when your primary long-ranged weapon can one-shot the entire colony you're fighting in, and your ultra-sophisticated cockpit system is busy trying to Mind Rape you into pulling the trigger.
Side materials claim the Wing Zero's Twin Buster Rifle (and by proxy the Wing Gundam's singular Buster Rifle) have variable power outputs and can be altered to "standard" beam rifle levels on a whim. That does little good in a space colony, since any kind of weapon discharge (even machine gun fire) may puncture the colony's hull and spell destruction for everyone within it. The latter was why the Crossbone Vanguard of Gundam F91 primarily used shot lancers (machine guns with a jousting lance attached) instead of beam rifles.
From Gundam X is the Gundam X. On one hand, it's pretty good specs-wise and possesses the almighty Satellite Cannon (designed to vaporize colonies). On the other, the damn thing has hilariously long charging and cool-down times, requires a direct view of a moon-based facility, and needs a Newtype pilot for the first activation. What's more, it's only mid-range weapon is a weird rifle/shield hybrid, making the use of both at the same time impossible. Thank god the Double Xironed out most flaws (it has both a rifle and a shield, and the cool-down of its Twin Satellite Cannon is greatly reduced).
The Phase-Shift Armor of Gundam SEED. When it was first developed, it was Awesome yet PracticalPlot Armor, as it was meant to deflect everything short of beam weaponry, which ZAFT didn't have at the time. However, when ZAFT stole four of the five Gundams, they got their beam weaponry, it was practically rendered moot. This doesn't include the fact that the armor ran on the suit's battery, which meant that it was draining from it and using the high-powered beam weaponry some were armed with drained the battery faster.
PS Armor and its weaknesses are actually a standard theme in Gundam, spawned from the original series itself: Gundams are almost always equipped with super tough armor against any and all non-beam weapons, which allows them to be invincible at the beginning of the series (when everyone is still using machine guns and heat blades) but more or less vulnerable later on when everyone has gained beam weapons. That said, one could say that Gundam armor in general (Luna Titanium/Gundarium/Gundanium/E-Carbon/whatever) is Awesome but Impractical, especially when it comes down to the fact they're usually too expensive to use on mass production units.
In Gundam 00, the Burst Mode of Gundam Virtue's GN Bazooka it powerful, but it takes Virtue five minutes to recharge after firing it. And on the battlefield, five minutes can make all the difference.
00 had so many Awesome but Impractical devices that the whole series practically ran on them. From the Gundam Nadleeh's Trial System (only worked on MS connected to Veda, which were few and far between in the first season) to the GN Drive Tau (produced limited energy and early models were radioactive) to the Gaga (little more than a piloted missile, and it took them by the dozens just to break through a GN Field) to the various side story prototypes that were thrown out at random, one wonders how any of these things made it off the collective drawing board, or why anyone would have wanted to use them (well, except the GN Drive Tau anyway).
Similarly, Dragon Ball Z has Goku's massively powerful Spirit Bomb attack, an attack so ponderous it takes several episodes to fully charge—he also invariably needs help holding off the baddie while he does so.
Goku's Kaiohken can multiplicate Goku's energy several times for a short times but his body is seriously damaged when using it. It's not weird that he stopped using it after fighting Frieza.note Then again, he got the Awesome yet PracticalSuper Saiyan transformation while fighting Frieza, so he didn't really need Kaio Ken anymore.
Also, Piccolo's Special Beam Cannon, at least at the beginning of the series when it takes an inordinately long time to charge.
It's because when he uses it, he's in the middle of not having an arm. According to Piccolo, having both of his arms would've let him charge faster.
Also, in the two advanced stages of the Super Saiyan (not to be confused with Super Saiyan levels 2 and 3, those are different), the Saiyan becomes far more powerful, but as a result he slows down greatly. Goku and Vegeta realized this drawback of the form right away, while Trunks learned the hard way.
Super Saiyan 3 itself also counts. It's the most powerful Saiyan transformation seen in DBZ, but consumes an enormous amount of energy, which has the side effect of reducing the length of a normally 30 minute fusion to about 5 minutes. It isn't even an Eleventh Hour Superpower like the first two. it appears early in the arc and is well outclassed by the villian's later forms, as well as Gohan after his hidden potential is released, who suffers no power drain.
Now we got Super Saiyan God, a form that requires six Saiyans and has such a high power drain, Goku barely has time to use the thing.
Zoids: New Century Zero - Bit Cloud gets the amazing, highly offensive Panzer unit upgrade for his Liger Zero, at the cost that he cannot move (in the anime; in the manga, he can move slowly) and the zoid overheats and he nearly melts in the cockpit.
This covers of the special "Invisible 9" units in Pumpkin Scissors in a nutshell, which feature soldiers transformed into supersoldiers in order to counteract design and equipment failures, or to accomplish things that could otherwise be done cheaper with technology improvements. The 908 High Temperature Troopers, for example, use souped-up flame-throwers that are so powerful they cook the users alive. Their "protective suits", rather than actually shielding them from the heat and dispersing it, are instead filled with special chemicals that keep them pain-free and able to function, though they die quickly and in hideous pain if they take the suit off.
Giga Slave from Slayers. Sure, you can destroy any enemy/mazoku with it, but chances are you're going to bring on Instrumentality or worse, be possessed by the (dark) creator of the Universe and when you manage to dodge all the above, you still lose your magical powers for a few days.
The very first episode of Yu-Gi-Oh! (Duel Monsters anyway) has Yami Yugi winning the match by summoning a monster named Exodia, whose power is defined as... well, winning the match. Obviously it's as powerful as you can logically get, but the caveat is that you need to gather all five parts of Exodia in your hand... which is roughly equivalent to drawing a royal flush of a specific suit, one card at a time, while your opponent tries to kill you, after managing to collect five extremely expensive and rare cards, and then adding them to your deck despite the fact that they are absolutely useless by themselves. When confronted with it, his opponent is suitably shocked and claims that nobody had ever managed to win this way before, which is pretty believable.
Though later in the series we're introduced to a character whose entire deck is built around Exodia and cards to stall the game or draw cards.
There is also the anime-exclusive card "Ragnarok", which Yugi uses to finally get rid of Marik's Winged Dragon of Ra in the Battle City finals. It removes all of the cards on your opponent's field from play, which is good, but under what conditions? First, you need to have two "Dark Magician" family cards in play, which isn't hard to manage. Second, you need to remove every other monster in your hand, deck, and graveyard from play. Presumably, you could smack the opponent for 4,000+ damage with the Dark Magicians afterwards, which is more than the player starts out with, but there are easier ways than THAT.
Yu-Gi-Oh! GX has several Duel Disks that certainly qualify. There's one than spins like a buzzsaw, one that appears to be made of three razor blades, and one that fires cards like bullets out of a gun. A type similar to the latter shows up Yu-Gi-Oh! 5Ds, which also features some very impractical motorcycles.
Speaking of GX, Adrian Gecko plays a few cards in his Cloudian deck that kill him if he doesn't win that turn. Big Summon Cloud sends all the cards in his Deck to the Graveyard just to Special Summon a Cloudian from his hand.
He wins anyway.
Yu-Gi-Oh! 5Ds has Sleeping Giant Thud, a monster that can't be beaten in battle, or spells and traps, and can destroy any monster it battles. Only problem? You can summon it only if you have a LV 1 Monster on the field for 20 TURNS. In an anime that barely gets past 7, this is...near impossible. It's never been summoned past Team Taiyou.
The titular character of Gash Bell has the spell "Jikerdor", which magnetizes and immobilizes enemies. Seems awesome, until you realize you need a large amount of metal for the spell to make contact with, and very rarely do the opponents conveniently fight in a location like that. In fact, a filler arc where the Mooks consisted of iron-suited baddies were the only time the spell became truly important.
Drifting as dramatized Initial D looks good in the anime, watching the cars slide and such. But in practice, drifting is more or less a spectacle rather than an often used technique in racing. Most of the reason is because your tires will wear out very fast.
Averted in the later seasons(stages) when exhibition drifting wasn't really used. The drifting in the later stages were more akin to drawn out versions of rally drifting. 
The Pactio cards in Mahou Sensei Negima! can be used for Telepathy. Cool, right? Except that you can only use them to send thoughts to a single person, who can't communicate back unless they have their pactio card with them, and telepathy is very easily blocked. Several characters point out that a cell phone is generally superior, as you have two-way communication, and can call multiple people with it. Negi also has a very limited mind-reading ability, which requires him to put his hand on his target's head for a few seconds, and walk away with maybe two words, which are likely useless. To be fair, Pactio telepathy has been around for a thousand years or more. On the other hand, telephones are less than 140 years old, and decent cell phones have only been around for less than two decades. So yeah, Pactio telepathy is far inferior to cell phones, but they've been around and in use for much longer. Modern day technology has simply made it redundant.
Also, we may not be seeing Pactio telepathy at its best. All of Negi's own are a probationary version meant for people who are considering making a Pactio - just backed by such ridiculous levels of power that it's possible to overlook the difference. The others have been either involving people equally immature or other "abusers of the system". No reason to think two people who have fully committed to it and spent a decade or two working on the bond couldn't do better.
Well, telepathy is just a bonus power of pactio, after all.
In One Piece Luffy's Gear Third as soon it has been used will reduce Luffy's size turning him into a midget, or toddler as he is smaller than even a small child. As well as reducing his size, his power is also limited, and he loses the ability to use his devil fruit powers. This requires Luffy to run and hide until the effects wear off. This lasts for the same amount of time that was needed to use Gear Third.
However, after the Time Skip, he seems to be now able to use it without shrinking at all, and can use the Color of Armarments to make it stronger.
Also similarly ALL of Frankie's attacks can be considered this as he uses up hiscola using normal attacks, more so when he uses his charged attacks. This causes him to seek more cola, until then his strength is severely reduced, also causing him to employ his teammates help. Also, any edible liquid other than cola can be used as a power source; HOWEVER, doing so can and will alter his personality, to a degree which causes him to NOT want to attack. Therefore it is imperative that his only powersource be cola.
However it was seen in a recent episode Franky was able to overcome his bi-polarness and attack the cyborg animals to protect himself, though only after he was thoroughly attacked.
Um no....that was actually because the tea he used had used was carbonated.
A recently introduced character can punch hard enough to destroy fortresses and (allegedly) could at a minimum severely injure a yonkou. The catch? He has to warm up for an hour to throw a single punch.
In InuYasha, Miroku's potentially phenomenally powerful wind-tunnel, which sucks anything and everything in the vicinity into a void in his hand, is rendered and utterly useless by a permanent swarm of lethally poisonous insects called Saimyosho which constantly buzz around the main antagonists. There are a few instances in which Miroku uses the tunnel anyway as a last resort, but the vast majority of the time he is prevented from using it. To be fair, without the Saimyosho to balance the field, his power would be quite a Game Breaker. Also later on in The Final Act, Naraku orders the Saimyosho to stop protecting Hakadoshi, allowing Miroku to use the wind tunnel to destroy him.
Break Shot manga. Chinmi and gang do ALL SORTS of awesome and cool and flashy shots which involve jump shots, 8 ball combos, air resistance, etc... all to sink a shot that could have been done in 1-2 normal straight shots most of the time. The crowning moment is probably when the 8 ball is in the corner pocket and all Chinmi has to do is tap it in... but he's afraid of sinking the cue ball. Does he do what any normal player would do and tap it on the side or put some backspin on it? No, he does some crazy 20 foot high jump shot!
In the Shaman King anime the main characters develop giant weapons referred as Giant Oversoul (Giant Spirit Control in the dub) that drain his spiritual energy. Due to the excessive use of energy the main character Yoh reduces his Oversoul-Spirit of Sword/Grand Halo Blade so that it will not use to much energy, secretly making it stronger since the energy is controlled and stabilized. In the manga the characters instead learn the third, improved version of the Oversoul, Armor-type Oversouls, that is less tiring, stronger, and maintains furyoku.
The power to steal souls (or portions of them) at the cost of your own
Gaara's Armor of Sand that coats him in a skin-tight, impenetrable armor capable of blocking high-speed taijutsu. but drains his chakra much faster than his active defenses and significantly limits his movement.
The signature jutsu of the Yamanaka Clan, the Mind Body Switch Technique. How can a jutsu that can swap your body with an enemy allowing you to do absolutely anything to them not be awesome — by creating a technique that leaves your original body defenceless, by creating a technique that doesn't allow you to physically injure your opponent in any way because it would injure you too, by creating a technique that only moves in a straight line and is slow enough to be dodged by first year Genin, by creating a technique that uses so much chakra it can't be used immediately afterward forcing you to wait whilst your enemy uses their less stupid and useless jutsu on you. Yeah, awesome technique that.
Which is why the Yamanakas have a more advanced jutsu that lets them take control of an enemy's body without losing control of their own. It's just that Ino hadn't mastered in Part 1.
Naruto's incomplete version of Rasenshuriken. Causes damage on a cellular level and one-shots most of his foes, but it deals extensive damage to Naruto's own arm to the point that he can't use that arm to eat temporarily.
Madara Uchiha can summon a freaking asteroid, and yet the size of the intergalactic projectile makes it impossible for Madara himself to dodge its impact.
Sasuke's ultimate attack until he got the Mangekyo Sharingan, Kirin. Powerful enough to destroy a mountain, it takes an ungodly amount of preparation to use. First he needs to generate enough heat via flame jutsus to heat the atmosphere and form a thunderstorm. Then, he shoots even larger fireballs directly into said thunderstorm to generate a massive potential in the cloud. Finally, he needs to shape the lightning and guide it to his target. And in the end, it didn't work. It's awesome in that it's instantaneous and undodgeable even by extremely skilled and fast ninja... Impractical because it can be blocked just fine.
And for Uchiha Itachi unless in Edo Tensei form where he has unlimited stamina and chakra. It's much worse. Itachi is one of ''the strongest'' ninjas in the whole world, gifted with three incredibly haxxed techniques. But Itachi...has genin level stamina. He has only three uses of it before he exhausts his normal supplies of chakra, more than that kills him. Using the MS five times during his battle with Sasuke is what partially killed him-accelerating his disease AND adding chakra exhaustion to boot.
Hidan's curse jutsu. It is a highly complex technique to follow: first, Hidan must ingest the blood of an opponent, from which he must then undergo that skeleton man transformation and then draw a large Jashin marking (an upside down triangle within a circle) on the ground. So long as he's within the circle, the jutsu is active, whereas if he moves out of it the jutsu is cancelled out; which means he's now stationary. That doesn't sound so bad, right? Well, then there's the real clincher: it can only be used on one opponent at a time. That's right, after all that setup, Hidan can only inflict damage on one target, all the while remaining vulnerable to said target's teammates, who can more or less work to force him out of the circle. Oh yeah, and due to the nature of the jutsu, whatever damage Hidan wishes to inflict on the target he has to inflict on himself first; in other words, if it weren't for his immortality, he would have to kill himself just to take out one guy. Overall, the jutsu seems to only work for sadomasochists (which Hidan happens to be); otherwise there are far more efficient and effective techniques in the Naruto universe.
Kakashi's Chidori was this when he first developed it. The extra speed and power the technique grants its user also causes tunnel vision, making it impossible to avoid counterattacks. His teacher Minato (the future Fourth Hokage) warned him not to use it again since it was an incomplete technique. A Sharingan can remove the tunnel vision, making the Chidori Awesome yet Practical in the hands of a Sharingan-user (if it could actually kill someone).
In Digimon Frontier Velgemon's strongest attack is a OHKO technique. Probably. You can't be sure, because everyone he uses it against manages to escape the blast radius since it takes so long to perform the technique.
MetalKabuterimon has a similar technique - according to Wikipedia, it's the strongest attack among the 10 warriors. But he needs to be perfectly still when performing it and it takes long to charge up.
Excalibur is a variation on this trope. First off, he is quite literally the most powerful weapon to have ever existed, having enough power to let an average student defeat three of the top meisters in the school in only a few seconds. Second, despite having so much amazing power and invincibility, he also has a perfect soul wavelength, allowing anyone to wield him. His powers are pretty amazing too: although other weapons are very unique, he gives his meister abilities that one would usually have to either resonate or become a death scythe to obtain, without even trying. These include making the air vibrate by simply turning into a weapon, giving his meister "wings of light", allowing him/her to fly, not to mention the ability to move so fast that it appears as though they are teleporting. With just one swing, he and his meister can travel through space! What could the downside possibly be? Well, it isn't that his powers are double-edged. It isn't that he consumes energy. It isn't that he overtakes his meister. What could it be? It's his PERSONALITY!!! Excalibur is perhaps the most annoying, arrogant, pompous narcissist to ever exist, and when people call you that while BLACK☆STAR is in the room, then that's a serious issue. On top of that, whoever wants to wield him has to obey one thousand rules, including entries like sitting through his daily 5-hour storytelling time. It's even acknowledged in-universe when the same three meisters all try to wield Excalibur after reading about how legendary his power is in the library, but they all decide it's not worth it.
Only two people were ever even capable of withstanding Excalibur's personality. One being King Arthur (cause even Merlin couldn't stand the Holy Sword), and the second being Hero, who did wield Excalibur for a while, before backing out due to his excessive sneezing fit (which is ridiculous, since sneezing is a thing anyone could expect from anybody). Excalibur himself claims that he partnered with Sherlock Holmes as well, but it's unknown if this is true or not.
Crona and Ragnarok's partnership. The good: first they have scream resonance. It's a unique resonance that greatly uplifts their already amazing powers. Second, Ragnarok's black blood hardens withing Crona's body to make even the strongest of attacks barely fase him. In the rare occasions that Crona does bleed, the black blood will become another offense for Ragnarok to control while Crona attacks. Plus, the more souls Ragnarok eats, like with most weapons, the more powerful he becomes, only the fact that he eats the souls of those who aren't on death's list ends up making him even MORE powerful, and causes him to change form; going from a small creature, to a huge muscle god, to an even more muscular dragon. The bad: The more of these illegal souls Ragnarok consumes, the more insane he and Crona become. Also, when their souls are disrupted, it may cause them to go through soul rejection, as two beings aren't truly meant to share a single body. It is unknown if this can kill them, however. There's also the fact that consuming so many illegal souls may also result in Ragnarok losing his mind and consuming Crona's soul. Later on in the series, Crona and Ragnarok return to Medusa and descend even further into madness, causing them to gain an evolved versian of Black Blood called Mad Blood, which is so powerful it can flood entire cities with black blood, and if anyone tries to break hardened mad blood, they will go insane, the problem is, as stated above, to have such a power, Crona has to be so dangerously close to madness, that even standing near him is a total danger.
Lyrical Nanoha has the titular character's Starlight Breaker. It required a really long charge time so its caster must be protected or the target needs to be immobile, and its strength is only equivalent to the total amount of scattered mana in the area, making this technique only suitable as a last resort. It also practically guarantees victory if it is cast successfully. Hayate Yagami also views her abilities in this regard, as her techniques are mostly large scale heavy bombardment spells that require a full unit of Bridge Bunnies to help her aim if Rein is not around.
Jet Alone from Neon Genesis Evangelion. A nuclear-powered giant robot that doesn't need a pilot...whose main reactor is governed by an easily-hacked central computer. In short, an A-bomb on stilts, and NERV takes immense pleasure in showing the world exactly how unreliable and dangerous it is. By causing it to go on a rampage. Also the designers hadn't figured out how to actually replicate an AT Field, meaning that it would be absolutely useless compared to the Evas.
The Evas themselves would also easily qualify under most circumstances. They're Humongous Mecha which possess incredibly powerful defensive and offensive abilities, however...they're reliant on the pilot's emotional state to function, Piloted by a Forsaken Child, picky about who pilots them, the damage dealt to them is mirrored on their pilots when they're at their best, and they need to be plugged in to a power source to operate for more than a few minutes. What saves them from this in the series itself is both the fact that they operate almost entirely within a fixed area (so they're usually plugged in) and for the bulk of the series are pretty much the only thing capable of killing an Angel, making their utility outweigh their impracticality.
Full Metal Panic!: The Tuatha De Danaan (TDD-1) submarine has a revolutionary stealth engine that allows it to go undetected by conventional sonars. The drawback is that the TDD-1 has to stay regularly on dry dock for several days for maintenance.
Revy's twin shoulder rig for her pistols wouldn't exactly work in real life. With the exception of a sitting position, it'd be too easy to lose the guns if you move too much. That being said, they do earn major style points.
One Heaven's Arena fighter in Hunter × Hunter had the ability to use nen to create an entire duplicate of himself. The ability seems impressive at first, with the fighter being able to go toe to toe with Hisoka and even rip off his arms. However, it's quickly revealed that the fighter's innate nen style is the complete opposite of his ability, and the duplicate ability is so complicated that he can't learn any other nen abilities as a result, leaving him as a one-trick pony that's easily thwarted once the ability's weakness is found.
Panzer World Galient: Galient's Flight Form makes quick travel to other places convenient, but since it has no weapons to speak of, it's completely vulnerable to aerial attacks. Luckily, this is eventually fixed via the Assault Galient upgrade.
Many of the cool but incredibly hard sword techniques in Rurouni Kenshin like Hiten Mitsurugi.