The UK painter’s first major museum show, at the Hepworth Wakefield, offers recognition of a talent whose ambiguous works can shift between the humdrum of the ordinary and the ecstasy of revelation