A prime example of this, for me, is Future Trunks from Dragon Ball Z.
His arrival on the scene is one of the most captivating moments in the entire series. He comes out of nowhere and decides to face down Frieza, the most powerful enemy we've seen up until that point, and not only wins, but takes him - and his father, for that matter - down effortlessly, and with style. Not only that, but this purple haired badass can go Super Saiyan, something that very recently we've only seen Goku, the hero, able to accomplish, and only through immense turmoil. Also, he had a sword. Nobody else had used a sword in the Z portion of the series up until this point. (Gohan had one as a kid, but that hardly counts).
His intrigue doesn't end with his stylish appearance. He's introduced as Vegeta's son (!) from the future (!!) and his mother is Bulma (!!!) His future is a post apocalyptic wasteland, and he and Gohan were the only survivors, fighting against not one, but two threats far greater than Frieza ever was. He's come back in time to set things right, and he's going to fight alongside the heroes we know to help fix the future that went wrong.
Except he doesn't ever really do anything again, really. From the moment he comes back in the Android arc to the moment the Cell arc is over, the only thing of note he does is A) Allow Cell to enter their timeline inadvertently and B) Fight Cell and lose, making a fool of himself in the process. He gets a cool design overhaul with Saiyan armor like his father and a ponytail, but it doesn't do him any good, and he goes back to his original style eventually anyway.
To be fair, it's not at all Trunks' fault that he doesn't get to shine during the Android and Cell story arcs, and unlike the other Z Fighters (I'm looking at you, Vegeta and Goku) he is trying his hardest at all times and never wants to prolong fights for any superficial reasons. He has the drive to finish the fight, without any of the Saiyan love for battle that so often bites them in the end. This is a pretty consistent trait of his from his introduction up through his latest appearance in Dragon Ball Super, which is part of what makes him stand out so much from the other characters in the series. I'm rambling a bit: the reason he doesn't get to shine is because his signature weapon - his sword - breaks early on in the story, leaving him without any real style of his own, and his one real moment in the spotlight has him using a form of Super Saiyan that is largely useless. This was to show Trunks' respect for his father and his pride, as well as Vegeta's knack for what's useful in a fight; Vegeta realized the form was useless right away, showing us that as powerful as Trunks may be, he is still naive and inexperienced. You can't blame him. Unlike Goku and Vegeta, he hasn't had any formal training, and also unlike those two, he is only a seventeen year old kid.
I'm sure that, if pushed to the same extent that Gohan was during his fight with Cell, he could have easily taken his place as the first Saiyan to reach Super Saiyan 2. But, that isn't how things played out, and Gohan needed his moment in the sun. This mysterious warrior from the future that was able to kill Frieza so easily is shoved to the side for the rest of the series. He does get one more moment of glory in his own timeline when he kills the Androids, but for all intents and purposes he did almost nothing to help the Z Fighters actually fight the Androids beyond giving them the heads up that they exist. Dragon Ball is notorious for sidelining its characters, but you'd think someone like Trunks would fare better than he did. His present timeline counterpart - the much less appealing Kid Trunks - does far more than he ever did, even if he never received the same level of praise and popularity as his future counterpart.
I will say, even though his last act in Z was the equivalent of returning to the first area and fighting level 5 monsters when you are level 50 in an RPG, he again showed his brutal efficiency and no nonsense fighting style, making his exit from the series just as stylish as his entrance.
Now that Trunks is back in the latest arc of Super, I hope he gets his chance to be useful. He's already shown that he took out his timeline's incarnations of Dabura, who was around Perfect Cell's level of strength, and the wizard Babidi, as well as reached Super Saiyan 2 on his own. In addition, he's apparently learned to channel ki through his sword, shooting a blade beam at new villain Black. He's definitely no slouch, and he's already shaping up to be a key figure in the story.
Poor Trunks. His future can't seem to catch a break. I'm glad he's back, but there's one thing that bugs me. Why is his hair suddenly blue!?