ST:NG The Buried Age by Christopher Bennett
This is the story of Picard from the end of his command of
Stargazer to taking command of the
Enterprise.
This ties in to many episodes and characters, even some obscure ones, expanding on the canon without violating it. As a bonus the author uses a lot of real science (the Acknowledgements at the end explains some sources).
While I'm at it, Picard awakens an ancient species from hundreds of millions of years ago, though far more ancient and advanced than the Federation. Still, the Old Ones don't instantly speak English (an improvement of how ST normally does it), though the language barrier is more of a speed bump than a hindrance.
Overall, it actually feels closer to genuine science fiction (even including energy beings) to me rather than science fantasy as I normally see ST.
To sum up, Guinan knows that her life depends on Picard taking
Enterprise (so Picard and crew can go back in time to save her as happened in
Time's Arrow), which Picard is reluctant to do after the disaster of
Stargazer. Her prodding sets up a chain of events that looks to undo any chance of Picard ever taking
Enterprise, and creating an existential crisis that threatens the entire Federation and also threatens an intergalactic war. I couldn't see how the situation would resolve, but it did so well and in a quintessential Star Trek way.
I'm going to rate it
9.5 out of 10.
I'll add that it features many of the characters from the series (Janeway also makes an appearance) which helps explain how and why Picard picked his crew (even explains his fish as a stress reliever that Troi suggested), though Janeway was unavailable to him. It also explains why Data doesn't start to learn to act more human until after getting on
Enterprise (actually starting in the book but under Picard). I'd say that it's part of the paradox featuring Guinan as it would seem unlikely he'd choose the same crew without the events in this book (which come from Guinan trying to tweak the timeline and it blowing up in her face).
(Riker doesn't even get a cameo, but it explains why Picard chooses the one who argued his captain, risking a court martial, as Picard gets too obsessed and vengeful, making poor choices that endanger the Federation itself, and shades of how Picard would become toward the Borg years later.)
(Q gets a brief moment--unknown to Picard--and this is when Q takes an interest in Picard and decides to "test" him, thus setting up the first canon episode.)
Guinan has an especially important part to play, particularly near the end as she's trying to solve what seems an impossible problem while there's still time to save herself and the Federation.
That said, it takes about 100 pages for the actual story to begin. I found it bearable and worth it, but I can see how it would annoy others.
But I think anyone who loves Star Trek would enjoy this one.