This picture (Powell waving a vial of white powder on the floor of the U.N.) says it all (for me): http://twitpic.com/r12s0. He let himself be bullied. He failed to stand up for himself (and us) when we needed him most.
I was disappointed precisely because I do believe he is a good man and wants to do what is best. I too thought he was destined to be president some day. He would have been a candidate I would want to vote for.
Many times we learn more from failure than success. I say in this instance, Powell has learned a great deal and could be a better leader because of it.
In the end, his mistake was following and not being a leader when he should have been.
This doesn't mean you should groupthink or quash dissension. The failure of the Bush Administration was that Bush apparently did not do a good enough job of facilitating a real discussion and let certain folks steamroll Powell. That is not leadership either. In any case, it is wrong for people to fault Powell for what he said at the UN, from a leadership perspective.