We have polished concrete in our renovated apartment and have had a heap of comments about it. Nearly all have been very positive but we have had some asking what we are doing with the floor!! You will get a 'warehouse style' type finish and this we have found appeals to a lot of people in their mid 20-early30's. There is a fair bit of dust involved however a lot of teh new grinders have built in vacuums so not too bad. If you can plan it get all the cutting/grinding done, then do the rest of your renovations then finish with the final coats so you get the best finish long term.

You're best to do a search in your area to find out costs and who can do it, we found it to be cheaper (about $5k for about 120 square metres) than floating boards or tiling and are really happy with the modern finish it gives. You do get dust but if it was carpet that dust would settle into the carpet and stay there so it can be very clean and easy to maintain if you give it the odd vacuum.

I'd only recommend if you are looking for a modern feel though and make sure the person knows what they are doing. Decide how much aggregate (the pebbles/stones int eh concrete) you want showing as this determines how much they cut back. As other posts mentined there's also a range of finishes so you have numbe of options on the final look.