most games feel to me like this tbh. only a strong story, told in the right way, accompanied with the right gameplay will have me covered right untill the end. most games do tend to interest me from the start. how will it be brought or what does the challenge entail, how is technology implemented etc and will fall apart quite soon. challenges that seem to ridicule you as a player/based on prior gameplay or just not my cup of tea.

if i had to name one ... then it would be Final Fantasy XIII... in my opinion it could have been a hellu'va story but fails by opulence

I actually found with Baldur's Gate 3 that it was act two I didn't like as much and felt like a slog. Acts 1 and 3 are amazing. Act 3 had more bugs at launch but it was mostly fine for me, and it's gotten a ton of patches. I highly recommend anyone put off by the act three talk to keep pushing to get there and see for yourself.

As for a game that I think this applies to, I'll say Resident Evil 4. I love the creepy village section with the more methodical RE gameplay, but as it goes it gets more action focused and the story gets more silly than creepy and it also goes on wayyyyy too long. Both the original and the remake had me enamored at the start and then annoyed by the end.

Since there's already been some discussion of games that don't so much fall apart as drag on for too long, I'll mention Streets of Rogue -- a really cool game, overall, in which a normal run takes way too long to finish when playing carefully/optimally (which is arguably necessary to win reliably).
Given the roguelike-inspired nature of the game, you kind of need to "stock up" on as many new traits, trait/stat upgrades, and credits/useful items as possible to be able to survive and do well on the later levels, as well as taking the time to avoid stupid mistakes that could lead to damage, failing objectives or missing out on other XP. Unfortunately, there's little to no time pressure in the game except during some types of disaster (which occur every third level, for the duration of the level), meaning there's no downside to spending as long as you want wringing as much XP and money as you can out of a given level. Consequently, once you learn the game's ins and outs, the last third (at least) of the game can become a largely trivial slog. Luckily, there are "mutators" that you can play with that can shorten runs somewhat and/or make them more challenging, but the default game could've definitely used some sort of more regular incentive (positive or negative) for you to keep moving on to the next level more quickly -- like the Ghost in the Spelunky games, or the pursuing Rebel fleet in FTL.