The simple answer is that the Council knew Anakin was a critical player in the events of the future, but they didn't know how or why. They had no clue what bringing balance to the Force entailed. Looking back, it is obvious, but in the canon, they didn't. In fact, read this quote from the Star Wars: Revenge of the Sith book, by Matthew Stover (p. 213, hardback, first edition):
"Yes. Always in motion, the future is." Yoda lifted his head and
his eyes narrowed to thoughtful slits.
"And the prophecy, misread it could have been."
Mace looked even grimmer than usual. "Since the fall of Darth Bane
more than a millennium ago, there have
been hundreds of thousands of Jedi --
hundreds of thousands of Jedi feeding
the light with each work of their
hands, with each breath, with every
beat of their hearts, bringing
justice, building civil society,
radiating peace, acting out of
selfless love for all living things --
and in all these thousand years, there
have been only two Sith at any time.
Only two. Jedi create light, but the
Sith do not create darkness. They use
the darkness that is always there.
That has always been there. Greed and
jealousy, aggression and lust and fear
-- these are all natural to sentient beings. The legacy of the jungle. Our
inheritance from the dark."
"I'm sorry, Master Windu, but I'm not sure I follow you. Are you
saying -- to follow your metaphor --
that the Jedi have cast too much
light? From what I have seen these
past years, the galaxy has not become
all that bright a place." [My note:
Obi-Wan speaking here.]
"All I'm saying is that we don't know. We don't even truly
understand what it means to bring
balance to the Force. We have no way
of anticipating what this may
All bold emphasis is mine, but italics were in the original text. I typed this by hand, so there may be typos in there, but the gist is there.
This book is considered G-level canon, so I consider it to be a very valid source. More info on the levels can be found on this Wikipedia page.
On another note, he did indeed bring balance to the Force when he killed Palpatine in the end. It's an interesting twist, given that one would think at the end of Episode III that the prophecy was referring to raising the level of darkness to that of the light -- but not so.