A party develops some energy and momentum as people respond to each other. A "party pooper" is someone who interferes with that energy and stops people from having fun. It means "pooped" in the sense of "exhausted." It's informal, but it's not a "bad word" and doesn't have any "bathroom" associations. "Say, there's a swimming pool in the back yard, let's go swimming!" "But we didn't bring bathing suits." "That's OK, we can go skinny-dipping." "Sure!" "I'm in!" "Let's do it!" "NO. NO. If you guys do that I'm going to call the police. I mean it." "Oh, don't be such a party pooper."

That means, that one is a very bad person to have on a party. Having this person on a party will be a failure for the party.