The original KotOR and KotOR II featured something no one has brought up yet. Each time you received a major bump in power, it was because your character's story arc brought you to it with a significant event. In KotOR I, this was represented by your character first undergoing Jedi training, with long discussions about what that meant, little subquests, and so on, finally granting you a huge spike in abilities by giving you your Jedi powers. Then, even though it had no mechanical benefit, the big reveal occurred just as you were hitting the final echelons of the game's power curve. This was an important point. It mechanically did nothing, but it made the player feel that the small, incremental boosts in power they were receiving mattered more. KotOR II tried to recreate that feeling with the protagonist reforging a lightsaber and then finally ascending to Super Jedi Prestige Class status.
Unfortunately both games were hampered by severe balance issues.
ME 2 had a similar event in the moment when Shepard picks up his/her Infinity +1 Gun on the Collector's ship, as a well-chosen weapon can outright change how you play your class. (An adept, sentinel, or engineer picking up a Mattock becomes a new character.) Unfortunately, the spike in power was not well-related to the plot event. ME 1 did a much better recreation of that feel with Shepard becoming a Spectre, and then using that event to unlock the best power tree in the game. It then followed up with the awful slog on Luna which rewarded the player with a prestige class for no reason other than they had beat down a bunch of drones. Had a similar event occurred with, say, Miranda unleashing previously limited cyberware that was installed in Shepard because of a crisis, that would have had more bite (and would make a good foreshadow of Joker doing the same to EDI later). Skyrim likewise tried to do something similar with getting the shouts, but IMHO Bethesda's open world style is lousy for narrative and the event is not very engaging. BG II unleashed your beast but tied it to the Reputation mechanic in a way such that it was a useless ability, so the player rarely found it worth activating. You were essentially punished for acting in any way except as a LG or NG character in game, whatever your alignment might actually be. Batman: Arkham Assylum and City do an alright job with this and their RPG elements. New powers and abilities are brought in at plot relevant events, making the progression seem natural and saving the player from being overwhelmed by having too much stuff dropped on them from the word go. However, it did lead to the out of character moment where Batman, the most crazily over-prepared character ever written, doesn't have his basic gear on him.
You know who pulled off a great moment of progression driven by narrative? FF VI. Learning the fate of the espers and then getting the magicite which teach magic and change how the game is played from that point forward? Take notes, devs. You were shown how to do it right at least as early as the SNES days.
So, yeah. Progression can be made far more meaningful and important when tied to the narrative, but lots of games absolutely failed to deliver on this. Otherwise, it's just a bunch of rather meaningless numbers going up.