What games change completely with mods?

Factorio has some interesting stuff like mods making it more RTS.

X-COM 2: Long War of the Choosen is a great mod and overhaul that brings totally new aspects and layers into the game that I didn't expect possible for a mod.
One complete new aspect is the infiltration time of missions. Your teams needs days to be ready or else you face much more enemies.
So instead of 1-2 squad teams you have in a normal vanilla game you need to mange much more teams and personel becomes and issue that nearly never was in the original game.

Total War Rome 2 - Has also some amazing Mods, sorry don't know the name at the moment, that completely change how the campaign and resources works.

I always tell someone new to pc gaming to get the entire Half Life series, there are sooooo many total conversion mods, that having the series is like having at least 30+ good total conversion mods.

https://half-life.fandom.com/wiki/Mods

If you enjoyed the original Diablo 2 and haven't played mods with it, I suggest playing Eastern Sun. While it didn't change the story or items, it is a lot harder, but drops are as powerful as they are rewarding... and you have way more storage space. However, thanks to Blizz once again prevents modding D2 Resurrection by forcing players to stay connected to BNet (just like they did with D3), you can play with friends running the same mod over TCP/IP with the original.

• Eastern Sun mod for Diablo 2. See 2009 Youtube gameplay.
• Eastern Sun Rises... a newer version of ES. See some 2021 gameplay.

The Dark Mod.
A spiritual successor to the original Thief games by Looking Glass Studios, with many missions to download.
It started out as a total conversion for Doom 3, hence the name, but it's a standalone (and free!) game these days.