Beside the theoretical side of Y, what made it interesting to me was the few tutorials on how one could derive it to stop an infinite expansion, which is a feeling I often get when trying to solve a problem, creates another similar problem only slightly different. Now I stop and try to find how to make bind the tail to the dog's mouth.
To be clear: the Y combinator is just one way to define a fixed-point combinator in the untyped lambda calculus. You can define fixed-point combinators differently and achieve the same effect.
I do agree that the realization that it's possible to build recursive functions in the lambda calculus is mind-blowing (and far from obvious!)
The brief description I saw and sltkr's comment look like gibberish to me. Not enough math or FP background I guess. I think learning it is probably going to be part of a larger, mind-altering effort for me when I eventually dig deep into FP to understand it. Lambda calculus, too, while I'm at it.