This was my first thought when thinking of unsexy players
On a serious note, the premise for this thread is solid, and here is my thinking. Usually the guys who go from sexy to unsexy seem to be veterans over 30 (i.e. Paul Konerko) who put up a couple less than stellar seasons and are expected to fall off a cliff (which does happen to a significant number of power hitters).
A guy like David Ortiz could be a new trend of sexy to unsexy type, a guy who had a lousy season (albeit one he pulled out of total shambles by smacking a ton of 2nd half HR's and driving in oodles of RBI's) and is suspected by some to have been a former PED user. Take a look at comments in blogs, and if I remember correctly even comments in threads on here, people were attributing his awful start to last year to him no longer "juicing."
The third type of sexy to unsexy type, and a type I love to take end game flyers on, are post hype blue chip prospects. A guy like Delmon Young 3 years ago was being described as a future fantasy stud, keeper gold, etc. While he isn't a perfect example because he still posts an awful BB rate and has some other statistical concerns, he is being completely ignored which to me is a mistake given his "tools," and his uptick in ISO and HR/FB. If it were to "click," with him by chance, he has the raw tools to be a very useful fantasy player. Brandon Wood, Homer Bailey, Joba Chamberlain/Phil Hughes, Cameron Maybin, Alex Gordon, etc are all guys who come to mind that were even as recently as last year considered keeper stars who happen to be getting slept on in a big way this season. In the cases of Phil Hughes and Joba Chamberlain part of that is the uncertainty of their roles this year, that said, they are both going quite late for guys with nice k rates and some serious upside.