Comes up occasionally in DC's Legion of Super-Heroes comic regarding the Legion of Substitute Heroes, a group of misfit heroes rejected for Legion membership because their powers are lame. A particularly notable example appears in Gail Simone's four-issue "For No Better Reason" storyline: after all hell breaks loose on Earth, Legion support staff member Chuck Taine sends the team the only backup he can find — apparent C-List Fodder from the Legion Academy. Who proceed to kick butt and take names.
In the "Superman and the Legion of Super-Heroes" storyline in Action Comics, not only do a group of third-rate villains take over the world, but they're stopped in large part by the Legion of Substitute Heroes, a team that raises What Kind of Lame Power Is Heart, Anyway? to an art form. The Subs don't have crappy powers (one of them has all the energy projection powers of the ENTIRE EMOTIONAL SPECTRUM, for example, from the GL comics), they just have sucky methods of using them. Stone Boy, sure, he has crap powers. But Fire Lad, Chlorophyll Kid, and Rainbow Girl? Just not the best methods of application for their powers. When they use them well? It's pure awesome.
In the Silver Age Superman story "The Most Amazing Camera in the World", a criminal, having gotten ahold of some Kryptonite, has the Man of Steel at his mercy. Jimmy Olsen, observing this with horror, runs into the room, brings down the crook with a flying tackle to the knees, and relieves him of the Kryptonite.
In The Astounding Wolf Man, Cecil assembles a crew of all of the series' badasses to storm a corrupt research facility. Before they go jumping out of their plane, we see Wolf-Man, Gray Wolf, Vampire Girl, Mecha-Maid, Agent Hunter, and Donald lined up with loads and loads of dakka and BFGs. Oh yes, dangerous indeed.
Plastic Man in the Justice League comics was often this. Despite nominally being the comic relief, he kicked bad guy ass so often that Batman classed him as possibly the most dangerous member of the League. At one point, someone writes Plastic Man off as a useless addition to the team, whereupon Batman points out that this is someone that has survived being shattered into millions of pieces and scattered on the ocean's floor for thousands of years, and is not to be trifled with. Notably, in Frank Miller's The Dark Knight Strikes Again, one character refers to him as "Immeasurably powerful. Absolutely nuts," and asserts that he could kill them all with ease. When the Martian Manhunter turned into a crazy, homicidal Burning Martian who took out the rest of the JLA with ease, Plastic Man was the one who took him on. Single-handedly. It turns out that the main reason Batman wanted Plastic Man on the team was to have somebody who could stop the Martian Manhunter if he went rogue. Because he's Batman. In JLA issues 52-54 (collected in the Divided We Fall TPB), in which the Justice League were divided between the superhero selves and their civilian identities, Plastic Man pretty much became a dadaist lunatic unable to concentrate on anything, more or less a Flanderized version of his usual personality. Eel O'Brian, on the other hand, was a mean as hell Badass Longcoat who rallied the civilian identity squad together and acted as their leader, not to mention delivering a brutal mocking as he beat the crap out of Bruce Wayne.
Empowered is a case of this happening to the main character. Four out of five times, poor Emp winds up tied up, beat up, and/or humiliated. That fifth time, though, is generally a thing of beauty, up to and including the point where she imprisoned an unstoppable monster in a set of power-draining alien bondage gear after it tossed her more-powerful teammates around like rag dolls. Plus the fact that she uses her head, but she has a definite Crowning Moment of Awesome in Volume 4 when she introduces Fleshmaster/dWARF! to the true meaning of a Curb-Stomp Battle.
The whole point of one section of The Dark Knight Returns is the lead up to a shopkeeper realising that when a little old lady is mugged outside your store, all it takes is to shout a bit and get one hit in on the mugger to make it right.
In the Buffy the Vampire Slayer Season 8 comic Wolves at the Gate, we have Dracula. While he is technically as dangerous as any other vampire, he is mostly played for laughs both in his one appearance during the TV show and during the comic. However, in the final battle of the issue, he delivers a chilling reminder of just how dangerous he can be when he's derided as an old man by one of the enemy leaders along with an awesome speech:
Dracula: "Did you forget who I used to be? I've killed more men than God's plagues combined. And that was before I started eating people for fun. The fields of Europe used to stream with the blood of my enemies. Trust me... the vampire's the least of your concerns. It's the old man you need to worry about."
Spider-Man himself is constantly underestimated. He's not the strongest or toughest guy, certainly not the most powerful, and makes endless jokes and is generally a goofball but his combination of powers and wits makes him surprisingly formidable especially when he gets serious. He's taken down all the X-men at the same time, tackled the Fantastic Four and beat the herald of Galactus when he finally decided to pull out all the stops.
Rogue of the XMen is usually reluctant to use her powers to drain someone, given all the grief it's caused her and others; if she ever takes off her gloves, that's the sign she's been pushed hard enough that she's willing to say the hell with it and use them. (Seeing as the power requires skin-to-skin contact to use.)
In Don Rosa's "The Magnificent Seven (Minus Four) Caballeros", the villain is leaving on the only canoe available to leave Donald Duck, Josť Carioca, and Panchito Pistoles trapped in an ancient ruin, to be eaten by a giant anaconda sooner or later. As the three despair of stopping him or ever getting out, Donald mentally hears everyone back home whose disrespectful attitudes he's effectively escaping on this adventure scolding him for failing again. He gets so angry that he swings on a liana over the piranha-infested river to reach the boat and viciously attack the (armed) villain. His example also inspires the other two to get dangerous and do things such as defeat the giant snake with an umbrella.
Disney Italy's creation of Paperinik years. The basic idea is Donald Duck as either an avenger of himself, a Super Hero, or a combination of the two, Depending on the Writer (it was originally an avenger of himself, being inspired to freakin' Diabolik). Not depending on the writer, he's always awesome: a typical instance of avenging Paperinik is the first story, where he stole Scrooge's money-filled bed while he was sleeping on it (he pointed out stealing some of the money bags lying around the room would have been just as lucrative but not as awesome as he was doing it), humiliated the police multiple times, ran everyone through a merry chase, and (accidentally) framed Gladstone for all of this (you may understand why stories with the Avenging!Paperinik play him as The Dreaded); and a superhero, Paperinik is pretty much Batman, only somewhat goofier and a lot more workaholic (Batman doesn't visit crooks of other towns to scare them in staying out of his way, after all); and as a combination of the two... Well, one time the Beagle Boys organized a marathon with Paperinik's identity (that they didn't know) as the prize to successfully convince every single person in town to leave while they sacked it, and Paperinik's interference ended up getting them caught red-handed and beaten up by the entire population of Duckburg (he could have stopped them easily in other ways, but it wouldn't have been as punishing). ThenDisney Italy decided Paperinik needed toTake a Level in Badass, and created the series Paperinik New Adventures.
The second Blue Beetle, Ted Kord, was a goofy practical joker who didn't seem to take things seriously most of the time and who's only weapons were ridiculous gadgets and some martial arts training, so he was usually seen as a second-rate Batman. The thing is, "second-rate Batman" is still way more badass than any normal human being has the right to be, and he was still an extremely competent fighter and a Gadgeteer Genius bordering Tony Stark-levels of intellegence. It's also worth noting that while the other two Blue Beetles gained superpowers from the Scarab, Ted was always a Badass Normal, and he still managed to keep up with them.
Booster Gold is an interesting version of this: he used to be a vain gloryhound who was only in the superhero business for the money and fame who could pull through and save the day when needed, but then certain events (like his best friend's death) caused him to Take a Level in Badass and made him want to be a superhero that was taken seriously, but then he found out that he had to protect the time stream from people trying to mess with the past and so that none of them ever suspected him of being a serious threat and tried to erase his existence he had to keep acting like an incompetent idiot. So while he used to be this, and he would seem to be this trope to everyone else if and when he manages to save the day, it's actually more Obfuscating Stupidity.
Many members of Justice League International are this as well, when they're not being Flanderized. These include Elongated Man Ralph Dibny, who had the same powers as the aforementioned Plastic Man, a woman who once commanded Darkseid's legions (Big Barda), a god-level genus who could get out of any trap ever devised (Mr. Miracle), an ice goddess (Ice), a woman with fire powers (Fire), one of the most skilled martial artists in the world (Black Canary), a man who could create things out of pure willpower (Guy Green Lantern)...most of the time they were just hanging around trying to bumble their why through real world problems, but these were people who regularly teamed up with Power Girl, Martian Manhunter and Batman himself.