A major retrospective at the Tate Modern shows how the artist became the queen of the confessional – but is her work any good?