For me, more than anything else, its'a great way to manage soundtracks that come with games that I bought; I rarely buy soundtracks individually, they're usually bundled in a deluxe edition of some sort.
By default Steam reads the Steam Music folder, which is now apparently the default location for any music or soundtrack DLC, but you can add your own personal music folder if you want to listen to tracks while you're playing.
It is odd they it only plays MP3, since that was an issue that was brought up in the discussions during the beta. I suspect this might have to do with playing music during gameplay and whether or not it could detriment performance. I expect they'll gradually add formats since some of the soundtracks that are included with games are in FLAC and OGG formats.
Personally, I love the way it automatically displayed all my game soundtracks instantly. No more digging around in the Steam folders.
When I'm listening to music in-game Steam Music means not having to alt-tab out to change tracks etc. on my default player. Plus, Skyrim still has problems with alt-tabbing so it is not actually an option there. Shift-tabbing to the overlay is a tad easier and doesn't disrupt the game.
I think Steam Music was a logical step in Steam's development. It enhances the overlay, giving you more control without leaving the game (or Steam for that matter). And on the business side, it gives Valve another venue to provide properly organized soundtrack and music integration rather than have it arbitrarily labeled as generic DLC.
