Several of the scientists Kim Possible helps out on her adventures. The prize has to go to Dr. Freeman from "Car Trouble," a charmingly childish and eccentrically unfettered scientist who created a house full of funny AIs just so he could have off-kilter conversations with them. In his first few minutes has a dance party with his toaster (an advanced AI, but still). Then Drakken breaks into his home. This does not immediately stop the dance party - in fact, in general he doesn't seem to mind being kidnapped at all. He eventually proves too kooky for Drakken to use mind-altering technology against, so he has to resort to trickery to get the guy to do want he wants.
Nearly all of the villains count, some more than others. Before he was fired for refusing to shave his mullet (or rather he quit for being told it had to go), Motor Ed was "widely regarded as the most brilliant mechanical engineer in the country." Motor Ed.
It may run in the family as his cousin is Drew Lipsky aka Drakken.
Mayor Adam West from Family Guy is a subversion. He doesn't keep his job because he's so good at it. He only keeps his job because of corruption and the stupidity of the city's populace. It is often made very explicit he is quirky AND terrible at his job.
Glenn Quagmire, who, despite being a raging hypersexual, is an excellent pilot (and quite serious when on the job). The only time he was ever fired from his job was due to Peter's antics. Later that episode, he gets his job back after helping land a plane via Telecom.
This trope is neatly summed in the cutaway with the Land-Ho Prankster.
Major Johnny "The Mayor" Bledsoe from The Oblongs, the mayor of Hill Valley and a masked professional wrestler who never breaks character.
SilverHawks, a sci-fi cartoon by the makers of Thunder Cats, has Colonel Bluegrass. Despite his rank, he looks, talks, dresses and acts like a stereotypical cowboy from the Wild West, but he's also an ace pilot described as being able to master anything that flies. If that wasn't strange enough, he's also a skilled guitar player, and is so fanatic about it that his futuristic guitar is not only a weapon, but actually integrates with the piloting mechanism for the Miraj, the team's spaceship.
In Transformers, this is a type commonly found in the Decepticon ranks. From ace gunfighter/closet coward Slugslinger to preening victory obsessed egotist Drag Strip, many of the more stable Decepticons find themselves grudgingly accepting the obvious personality disorders around them because the people with them are among the most effective warriors.
In the case of Starscream, his quirk is that he wants to supplant Megatron. It is very much due to his superior skill that he is kept on as a Lieutenant, although later incarnations have Megatron far less patient with him.
The Autobots aren't devoid of quirky robots, either. For starters, we have Silverbolt, leader of the Aerialbots, who is afraid of heights... which is a bit of a problem when you and everyone in your combiner team turns into an airplane. Broadside, a triple changer who transforms into a jet and an aircraft carrier, is both acrophobic and prone to seasickness (according to All There in the Manual but never witnessed in any show or comic, mind you.) Also among the heroic forces are the egotistical Sky Lynx, neat freak Wide Load, and Siren, who has No Indoor Voice, among others. And this is just in G1.
Rattrap from Beast Wars is often insubordinate, keeps complaining about his job and single-handedly gets on the nerves of every single one of the Maximals, IE his own side, especially Optimus Primal, IE his boss. Even though they're all stranded, the only reason he's not kicked out of the base to fend for himself alone in the wild against the Predacons is because his skills as a spy, infiltrator, sharpshooter and saboteur are absolutely unparallelled, to the point where when Optimus is gone, Rattrap's actually put in charge because he's just that good.
In fairness, the Maximals eventually learn to love Rattrap, who often makes fun of people as a sign of endearment. A much more straight example is Tarantulas, who is completely open to his boss Megatron that he hates him, is working against him, and hopes to destroy him. However, the vast majority of Megatron's other troops are idiots, while Tarantulas has scientific know-how that outclasses Megatron's own. Megatron needs Tarantulas and counts on the fact that when the time comes, he'll be able to outgambit him - he does.
Inferno is utterly insane, insists on calling Megatron "my Queen", and really likes to set people on fire. Megatron mainly kept him around due to his loyalty (a rarity for a Predacon) and how good he was at, well, setting people on fire.
Many of the Decepticons are like this. The series is more 'cartoony' than most, but The Powers That Be had a rule: every Decepticon is a real threat. Blitzwing is hilarious and argues with himselves... and has fire powers, ice powers, and Macross Missile Massacre powers. Lugnut's fanaticism as he declares the awesomeness of the GREAT and GLORIOUS Lord Megatron with extra Large Ham-ness is hilarious... but his firepower makes the aforementioned Blitzwing look like nothing, and if sending out missiles literally by the dozens won't kill it, he's got a punch that can flatten a city block or two. Swindle's pretty much Honest John's Dealership... but he kept the best toys for himself. Starscream's more bombastic than ever, but he managed to bring down Ultra Magnus moments after getting thrashed by Megatron for the usual reasons.
Starscream's clones all have some aspect of his personality tuned up to the max, some, like Skywarp and Thundercracker are about as effective as they seem, and Ramjet is incredibly predictable. Slipstream takes down the beefier Bulkhead, and continues to display battle proficiency despite her snippy attitude. Sunstorm manages to free Ramjet during the fight, studies and then takes down Prowl, complimenting him all the way.
How could anyone forget Georges Hautecourt from The Aristocats? Tarara-boum-di-ey!
Wildcat from TaleSpin. When actually in touch with reality, he demonstrated MacGyver-level feats of improvisational engineering.
Baloo isn't far behind, he's lazy, slovenly, fiscally irresponsible, clumsy, never graduated grade school.... and is the best pilot you'll find anywhere.
Mr. Smee from Peter Pan is a bumbling idiot who can't even fight his own shadow. He offends/provokes his captain regularly, messes up at every turn and more! by all reason he should have been keelhauled long ago. But for some reason, Hook lets him stay. This might also be an example of Beware the Silly Ones because it's entirely possible that he has more control over his captain than we migh realize.
Shore Leave of The Venture Bros. is a Camp Gay with a porn stache and a sailor's hat. He's also a former top agent of the OSI, a crack shot, and an overall ass kicker when he wants to be. BOOM, yummy!
Kyösti Pöysti, the main character of Pasila, is a criminal detective who sucks on a pacifier.
In Kung Fu Panda, Po starts out as a humorous bumbling glutton prone to extremely awkward moments. By the end of the first movie, and remaining so throughout the second, he becomes a Bad Ass kung fu master capable of plowing through hordes of mooks with ease and going toe-to-toe with other masters... and who is still a humorous bumbling glutton prone to extremely awkward moments.
This is further shown in the animated series, where Po is still disobedient, undisciplined, gluttonous, clumsy, accident prone, and the biggest fanboy on the block, but can learn highly advanced techniques after seeing them one time (Including one that blind anyone who sees it performed... which he not once, but repeatedly after learning it, forgets to close his eyes so he doesn't blind himself.) and can even master techniques from reading about them that Master Shifu is unable to perform.
The few times that Dale Gribble of King of the Hill has been shown doing his exterminator job, he's really good at it.
Despite Bill's manyflaws, he's one hell of a barber.
In the early days of The Simpsons, Krusty The Clown was this. Everyone tolerated his Jerk Ass behaviour because he was considered a universally beloved entertainer on the level of Elvis Presley. In later seasons, he's become a has-been whose popularity keeps sinking, and who's constantly at the risk of being fired by his network.
Rainbow Dash is rude, crass, tomboyish, eminently egocentric, and prefers to spend her time either napping or practicing flying stunts rather than doing her weather management job - but she's one of the greatest flyers in Equestria, can perform moves other pegasi only dream of (plus one that was supposed to be impossible), can clear the skies in ten seconds flat, and is ultimately dependable to the point of being the literal embodiment of the Element of Loyalty.
Rarity is a renowned fashion designer, commissioned by pop stars and royalty alike, and a massive Drama Queen prone to fainting, freak outs and assorted conniptions. And despite her usual prissiness and tendency to panic at the slightest hint of getting dirty; she has repeatedly demonstrated that she can throw down with the best of them.
"Swarm of the Century" is a good example of this. Throughout the episode, Pinkie seems to be occupied with a meaningless scavenger hunt instead of helping her friends deal with the parasprite infestation. In the end, it turns out that she knew exactly how to deal with parasprites and was working on that plan all along (and assumed that her friends knew that), so from her point of view she was the one dealing with the problem while her friends kept playing silly games.
O'Farrell from Fillmore! is a Cloudcuckoolander of the first order, at one point delivering a curiously philosophical ramble on the Zen nature of being unable to see your own butt, but he's pretty damn good at crime scene photography. To a lesser degree, there's also the Cowboy Cop protagonist, whose skill at catching the villains is probably the only reason he isn't going to be paying off destruction of school property for the next million years from the carnage-strewn chase scenes that happen usually two, maybe three times an episode.
Rick of Rick and Morty is a scientist (although not by profession) capable of building extraordinary inventions, but is also a drunk with no moral or ethical boundaries.