expansion that comes to mind for me is Diablo 2's Lord of Destruction
expansion. The added character classes, expanded skill trees, item revamps and additional act all improve upon the base game of Diablo 2 in meaningful ways. I know vanilla D2 has its stalwart defenders, but to me the game wouldn't have been anywhere near as popular as it is had it not been for the expansion pack.
Blizzard did pull off a similar feat with Diablo 3's Reaper of Souls
expansion. The industry and gaming community had written off D3 but Blizzard really turned the game around with the DLC and subsequent content updates. It's still not perfect, but it's certainly worth playing.
Another personal favourite is ME2's Lair of the Shadow Broker
DLC. It builds on Liara's persona in interesting ways and gives the relationship between her and Shephard more nuance and complexity. It makes it, to my mind at least, one of the most realistic and well-written relationships in video games. The actual gameplay in Lair wasn't always perfect, but some of the setpiece missions were certainly fantastic.