The broom-sweeping elder monk Lu Tze from Terry Pratchett's Thief of Time spends most of the book bluffing and letting other people do the work for him, repeatedly saying that people should be cautious around wizened little men (i.e. himself). Only in the last few pages of the book does he show that his reputation is perfectly justified, as he is a legendary martial arts master capable of taking down the Incarnation of Time in single combat.
Otto Chriek from The Truth also qualifies. As a vampire who's sworn off biting the living, he's portrayed throughout the book as a silly but sympathetic figure. And then his employer is threatened, and the resulting fight scene borrows heavily from the then-recent The Matrix film. In Thud, he explains that he works hard to appear silly and pathetic, because if he didn't he'd be creepy and genuinely frightening. There's a couple of hints now and then that he might be a retired complete monster.
Magrat of Lords and Ladies, who until that book had been the Granola Girl. She practically embodies this trope the whole way through as she shoots a crossbow bolt through a keyhole and demolishes the Queen of the Elves. The thing about small, furry creatures is some of them are mongooses.
The elf looked down at the crossbow. "I won't beg," it said.
"Good," said Magrat, and fired.
And later on...
The Queen attacked again, exploding into her uncertainty like a nova. She was nothing. She was insignificant. She was so worthless and unimportant that even something completely worthless and exhaustively unimportant would consider her beneath contempt. In laying hands upon the Queen she truly deserved an eternity of pain. She had no control over her body. She did not deserve any. She did not deserve a thing. The disdain sleeted over her, tearing the planetary body of Magrat Garlick to pieces. She'd never be any good. She'd never be beautiful or intelligent, or strong. She'd never be anything at all. Self-confidence? Confidence in what? The eyes of the Queen were all she could see. All she wanted to do was lose herself in them, and the ablation of Magrat Garlick roared on, tearing at the strata of her soul... exposing the core. She bunched up her fist and hit the Queen between the eyes.
Then there's Stanley in Going Postal. A preternaturally neurotic postal worker (he was raised by peas. As in, the vegetable), he's usually just very polite and obsessive about certain topics - unless you push him too far, in which case he has a Little Moment and hits you very hard in the face with something unpleasant. And he's so wound up that it doesn't take much to put him over the edge.
The Archchancellor and most of the wizards at Unseen University count. They're a group of stumbling idiots who spend most of their time eating large meals and sleeping... but don't piss them off. In fact, the whole reason the university exists is to ensure that wizards do not use magic. Transforming someone into a frog is not hard; it's hard to not do it despite knowing how easy it is. Before the university, the general behaviour of wizards was... a bit different. How so? Well, the group word for "wizard" is "war." Take that any way you wish.
Also, Susan, Death's grand-daughter technically counts (particularly by Thief of Time). She may be a teacher and may spend most of her time looking after six year olds, but if you make her angry she can and will bend time and space (more like time and space don't mean anything to her) ... oh and she's technically Death's successor. (And anyone want to take a bet what Death will do to you if you mess with his grand-daughter?)
And the scene in the same book about her abilities as a teacher. She inspired one of her students, who had until then been terrified of the monster under the bed, not only to face her fear, but to go after it with her father's sword. When confronted with parental worries that she'd been introducing children to the occult, Susan replied that she had, "so it won't come as a shock".
Death himself. He's this relaxed, philosophical figure, quite uncharacteristic of the normal image people have of the Grim Reaper... until you mess with the Balance of Life and Death. Then he gets on a fucking motorcycle. That's all there really is to say on the matter.
In "Reaper Man", Death spends most of the novel in retirement, bumbling through the life of a human with the improbable name of Bill Door. Then, when his replacement comes for him Death becomes truly angry, seeing that the new Death is not a reaper and shepherd but a ruler, and sharpens an ordinary scythe with the power of his own fury and takes back his job.
Oh, and Albert, Death's butler. He spends his days complaining, smoking thin, soggy cigarettes, making food that tastes as bad as it is for your health, and oh by the way just happens to be the greatest wizard who ever lived and the founder of Unseen University, Alberto Malich.
Mr. Bent from Making Money, a top-class accountant for most of his life, he nevertheless manages to kill two thugs near the end of the book, at least one of whom had trained at the Assassins Guild.
The Nac Mac Feegle were comic relief through the first three Tiffany Aching books, but in I Shall Wear Midnight — don't go mucking around their mounds with a shovel. Just don't.
Miss Esmerelda Weatherwax is a subversion, as she usually bluffs her way through things. More than one opponent has concluded that her glory days are behind her and she's just relying on misdirection and trickery. Upon being accused of this, she almost invariably becomes a timid, harmless, possibly slightly senile, little old lady who says things like "Oh deary me" and "lawks" ... at which you can start counting the seconds until things go Very Badly Indeed for said opponent.
If Rincewind takes off one of his socks and puts a half a brick in it, it means he's decided to stop running and fight. The first time it happens he winds up beating off an entire army of Eldritch Abominations by himself. He doesn't always win when he does this in subsequent books but it always marks a moment where he's suddenly quit being a Coward and decided to be a Bad Ass instead. In Unseen Academicals, when he quietly starts pulling his sock off another mage notices and immediately brings the impending riot to a stop before Rincewind has to act.
A prime example of this is the last battle in Harry Potter and the Deathly Hallows. The professors and faculty of Hogwarts, along with other formerly comedic secondary characters, upon being confronted with an attacking Death Eater army, finally demonstrate why they are teaching their subjects.
More specifically, don't mess with the only daughter of a woman who's raised 6 boys simultaneously.Especially when one of those sons was just killed.
In the film version, she is laughing when Molly shows up, and after her first attack, goes straight into an Oh Crap while Molly delivers her declaration. Then she actually starts to take her seriously as a combatant, but stops to cackle at her, at which point Molly starts driving her back, then breaks her into little pieces.
Hey, don't forget Professor Trelawney! Something has to be said about the woman who drives off a werewolf using crystal balls as projectiles.
Not to mention Horace Slughorn, the overweight and decadent Slytherin who likes to cultivate powerful connections among his students not only returns to the final battle bringing badly needed reinforcements, but also joins McGonagall and Shacklebolt in personally dueling Voldemort.
Also in the final battle, the thestrals and the house elves (led by KREACHER, of all people) take part in the battle, greatly assisting the heroes.
Dobby in Book 2, and then later in Book 7, where he completely owns Bellatrix and the Malfoys and ends up pulling both a Big Damn Heroes and a Heroic Sacrifice.
Neville Longbottom, who, after being everyone's favoriteloser for 6 years, steps up to the Carrows, leads an underground resistance group and single handedly decapitates Voldemort's beloved snake and Horcrux Nagini after being paralyzed and lit on fire.
The special-release Neville Longbottom Chocolate Frog Card states plainly that he is one of only three individuals ever to single-handedly duel Voldemort and survive (the other two being Harry Potter and Dumbledore). The phrase "Took a Level in Badass" just doesn't seem adequate, somehow.
Victor Cachat is this is his first appearance in From the Highlands. He's the new guy, never done any real fieldwork. His new mentor calls him "wonderboy" and mocks him (good-naturedly) at every opportunity. His idealism and naivete is a constant source of amusement to the other characters, even though the former the only reason any of them would consider talking to him. His uncertainty is quite frankly, adorable and endearing. Then you get to the end bit where he's supposed to fire one burst to scatter the scrags and get out of the way for the Ballroom, and...yeah, not so much. They screwed with his nation and his principles, and he will make them pay, personally. Officer of the Revolution. Sneer and be damned. (He's much less of an example thereafter, since it becomes common knowledge in the military/espionage community - meaning all the main characters - that however cute and sweet he may occasionally appear, only the deeply suicidal get in the way of his principles. He is THAT good.)
Another example is his mentor Kevin Usher, who deliberately built a reputation as a politically oblivious drunk to hide the fact that he's a major player in the Havenite resistance.
Another example from the same series is Shannon "Oops!" Foraker. She's the quintessential techno-nerd and Genius Ditz, apparently a wizard at tactical problems with no particular care for the real world. But once she marks the Havenite State Sec as an enemy, the organization's days are numbered, culminating with an entire main battle fleet's fusion plants being blown by a computer virus. Now remember, kiddies, never mess with adorkable nerd girls.
The Cassiline Brotherhood of Kushiel's Legacy are sworn only to get their swords out to kill (normally, they use daggers and vambraces). You'd better believe that when they do, things are going to get VERY dangerous. Especially if it's Joscelin, and doubly especially if Phedre is in peril...
The most powerful Ringwraith, the Witch-King, was killed by Merry and Éowyn — characters not previously recognized for their fighting prowess. Merry even joked later: "Sometimes it's good to be overlooked..."
There was also Pippin, who in the books took down a whole troll, and not just any troll, but one of those ones bred for fighting.
By the time the third book has happened, Merry is already noted to have hacked the limbs off of several of the URUK-HAI that tried to capture him and Pippin. The Rohirrim weren't leaving him behind because they thought he was unfit for war. They were leaving him behind because they didn't have a horse that could carry him and they running were well ahead of their infantry.
Speaking of Hobbits, one chapter fits this trope perfectly: Scouring of the Shire.
Gandalf himself gets a little of this. He comes across a little bumbling in the first book, a tad of the archetypal distracted wizard. Then he faces down the Balrog of Moria.
Samwise deserves a mention too, driving off the Eldritch Abomination Shelob and taking down an orc slavedriver in his search for Frodo. The sequence where he's killing orcs with a frying pan in Moria is priceless.
In the film version, Aragorn is only hinted at being powerful upon first meeting. He has a sword, is very Ringwraith-savvy, and manages to lead them through the wilderness, but still doesn't really show his awesome side. Then Weathertop rolled around, and a nation of geeks realized the amount of damage you could do with a torch.
Treebeard and the Ents: so slow and ponderous it takes them hours just to get through "hello". Too indecisive to, well, do anything. Until they realize what Saruman is up to...
Harry Dresden generally looks like a very tall and lanky scarecrow with black and quirky dress sense. People who piss him off and hurt those he cares also find out why beings of up to immortal Physical God status are terrified of him. If you hear the word 'Fuego', you're generally about to die a painful death. Moreso if its immediately preceded by "Pyro".
Wizards in general are not usually classed according to the amount of energy they can output, but their ability to control that output. Most of them have some aspect of natural talent that they normally have to mechanically tone down using a tool, or abstain from using entirely. Harry has three separate tools designed to limit the amount of fire he instinctively conjures, so when he starts it up without his shield bracelet or a rod or staff pointed at you, you're not in danger of being set aflame so much as the entire city block on which you're standing.
To put this in context, when Harry Dresden goes to war, Vampire Lords, Faerie Queens, Fallen Angels, and whole supernatural species die.
In Aftermath, Murphy described the moment that Harry becomes this trope - he suddenly stops being a goofy, irreverent nerd and starts being a commanding, primeval force of nature. She's terrified of it and she's the one watching his back.
In the long run, he threatened a Black Court Vampire that if she ever so much as thought about hurting his friends, he'd go necromatic-god mode on her. She hasn't been back.
Sometimes Harry forgets that Thomas can go super-powered vampire at any time. Sometimes he doesn't.
When Molly first breaks out her "DJ Molly" magical light show at Chichen Itza.
You'd think that a known badass couldn't have these moments, but when Ebenezer McCoy pulls the Blackstaff out of a pocket dimension, Elite Mooks start ceasing to exist.
Drusas Achamian in Second Apocalypse is a Mandate sorcerer. Throughout the first book we're told that the Mandate's Gnosis sorcery is much more powerful than the Anagogsis of other Schools, with little demonstration. Then in the second book the Scarlet Spires (another sorcerous School) decides to abduct him and try to torture the secrets of the Gnosis out of him. He escapes his bindings, slaughters all the sorcerers and soldiers holding him captive, beats the shit out of a demon summoned to fight him, and blows up their compound.
The mice in Prince Caspian. They spend the entire book getting laughed off for their apparent tough-guy acts, and then in the final battle of the book, they're out there in the thick of things, crippling enemy legs and finishing them off when they collapse.
The Death Gate Cycle: Alfred faints when confronted with danger. In truth he is a Serpent Mage, one of the most powerful beings in the universe.
It's more like he's consciously taking a level in wimp by focusing his mind on not hurting anyone to the point of literal mental illness. He just naturally collapses back to being a Physical God when someone finally manages to shake his concentration by inadvertently bringing up the things that made him take up the quest for harmlessness to begin with.
Belgarath pretty much lives and breathes this trope. Normally he could easily be mistaken for a drunken, lazy, bumbling, vagabound, with a fairly weak grasp of personal property and a rather sloppy taste in attire. People who know him often call him this and worse, since he can't seem to be bothered take most things seriously. But when things get serious, or you piss him off... you'll see WHY he was chosen as first disciple of the god Aldur, and just what the most powerful sorcerer on the planet, with over 7000 years of experience, is capable. note For starters, it's speculated in-universe by fellow sorcerers that if he was so inclined, Belgarath could stop the sun from moving across the sky.
His daughter Polgara is only slightly less so. She may look pretty innocuous at times, but she takes a lot after her father in the sheer power department (in an early book, she turned the Empress of Nyissa into an eternal snake). She's lived several millennia herself and has been everything from a Nadrak slave-woman to the Duchess of Erat, so she carries her own arsenal of magical and non-magical skills.
Speaking of David Eddings, in The Redemption of Althalus, you have Emmy. She looks like a sweet cat and will purr and love you to pieces... However she is God (well, Goddess), and the entire fate of reality is revolving around a family feud she's having with her two brothers. Piss her off and she can and will eradicate you from existence. Also, Athalus himself probably counts to the rest of the group. He may be a thief and he may joke around with the forces of existence, but you threaten anyone he loves and he will show you why he is Dweia's boyfriend.
In the Demon child series, R'shiel may look and act like a spoilt brat. However she has enough power to destroy a God. And she has a short temper when it comes to people interfering in her plans.
White Jenna: Skada. As a dark sister, she is supposed to fill a supporting role, but in the climatic battle she kills her first man and saves Jenna's life.
In The Cloakmaster Cycle a very young Giff joins the protagonist and considers him his superior. This green "trooper Gomja" is more of a burden, since he only looks at Teldin's mouth and waits for an order. Then in a fit of inspiration and pique, Teldin "promotes" the lad to sergeant — despite currently not having any rank at all and being only a mule skinner before retirement. But this forced Gomja to take responsibility and woke up his tactical mind. Now, not only even a young Giff is still a humanoid hippo bigger and stronger than most men can hope to be, with the hide tougher than studded leather armor, but all adult Giff are mercenary Space Marines and even kids live in preparation for the same, waiting to be enlisted. Hilarity Ensues in short order. Lots of it.
Septimus from Septimus Heap does this against the Toll-Man when trying to get to the House of Foryx in Queste.
Let's consider the Animorphs. Sure, Rachel develops into a walking pile of RAEG and Jake is clearly no one to mess with, with an air of Determinator-ness and devoted leadership. But their support? A wise-cracking comic book geek, an introverted, pacifistic animal nut, and the middle-school bully magnet (and Ax); not exactly the biggest badasses around. That is, until they transform into wolves and gorillas. Tobias's hawk might not be the scariest thing around (though he's damn good with it), but he also knows rhino. And, if worst comes to worst, they all have the set of polar bears, just in case...
The Kingkiller Chronicles teaches us important things, like for the love of Tehlu, do not break Kvothe's lute. Or kill his family. Or threaten innocents. He will call lightning and annihilate you, he will stab you from afar with voodoo, and he will call the Name of the Wind, and rend you in its power.
Rand al'Thor, Dragon Reborn, Aes Sedai, the man who can stare down a thundering Trolloc army and simply kill them all. After nearly being captured by a Forsaken, he gained some measure of control over the pattern itself and nearly annihilated someone just by willing it. Only later in the series does he start looking like that all the time, spending the rest of the time as a farmer wearing fancy coats because someone told him to.
Matrim bloody Cauthon. The cleverest mind alive, backed by hundreds of years of tactical memory, nearly unbeatable combat skills, an anti-magic amulet, perfect luck as a superpower, cannons and repeater crossbows in a medieval setting, all contained in a drinking, carousing, gambling, swearing little well-intentioned man.
Just don't take Perrin's wife away. Just don't. Unless you want him to suddenly martial every force that could hypothetically work together for a combined assault against you. And win with laughably inferior numbers. With him leading the charge.
Try and destroy Egwene Sedai's Tower. Just try. She will unmake you. But other than that she's quite pleasant. Except if you try and manipulate her, because she'll then publicly out-maneuver you in front of your peers. She's only recently out of her teens.
In The Tomorrow Series, Ellie and her friends are perfectly normal, everyday Australian rural and small-town teenagers. Then, after they find out that Australia has been invaded and occupied and they're among the very few Australians who're still at liberty they decide to get serious.
The Last Dragon Chronicles: When attempting to fight Gwilanna, Galen tries to make his human host grow wings. It doesn't end well. It ends with a lot of bloody, gory muscles on show.