That's a bit of a loaded question, I think. Let me try to untangle it first. You present your own political opinion as fact in the way you formulate the question; that opinion being "a very capable and effective President should not be impeached by Congress". Well, whether or not that is a good idea is probably something our friends over at /r/PoliticalDiscussion could have a field day at, but I am more interested in the historical aspect of this.

You have, by the way you stated the question, implied that not only do the current political leaders of the United States share your sentiment, but also the political leaders of the early 1970s. A dangerous assumption to make, as the political world of Nixon is one where Reagan, Bush Jr and Obama, all very culturally significant Presidents, did not yet shape political public opinion in the United States. Now, to reflect back from that not-yet-changed public opinion onto the leadership mentality is in my view justified. Your addendum in the description of your question about contemporary American leaders is rather irrelevant to the question at hand.

The Representatives and Senators that chose to support the prospect of Impeachment in 1974 were not aware of what America's political leadership would do over the next decades. Thus, it posed no role in their decisionmaking. The more interesting question would be how current leaders are influenced by Nixon's resignation, as there is an actual historical continuity there, but alas, that's not the question you posed.

So, to get into the weeds here. The Watergate Scandal was a big deal in America's public perception, and journalists really kept the chase on. The example to look at might be Carl Bernstein and Bob Woodward, who would go on to co-author All the President's Men and The Final Days, both about the Nixon administration.

Now, I will assume that you already know the general timeline of the investigation, from the humble beginnings over the dismissal of key political figures to the prospect of impeachment being raised to the House over the SCOTUS decision to force Nixon to make his tapes available to the public and, at last, the "Smoking Gun". The timeline itself is not as important as the cultural impact it had - it was a continuous buildup of scandal and intrigue around the government. And, with that buildup, you had a gradual erosion of popular trust in the government on your hand.

In government, it's "every man for himself" really.

The U.S. Two Party System is a powerful system to lock people in place, but if you as a political leader mess up, don't be surprised if your cronies jump ship if they smell the opportunity to further their own career by burying yours. Many attempts of this happened in American history, for example the 1976 Primaries where Ronald Reagan got very close to dethroning the actually incumbent Gerald Ford for the nomination of his party. It ended up working out for Reagan, who strengthened his profile as a passionate conservative against Ford's more moderate demeanor, and Reagan swept the 1980 and 1984 elections afterwards.

So, back to Watergate. Nixon had lost his backing with Congress. Not only were there the Democrats - of course he couldn't count on them -, but there were Republicans who felt that their own electorate was disgruntled with the ongoing Watergate scandal. Nixon had plummeted in the popular approval rating from his all-time high, a 66% in January of 1973, to a 24% in January of 1974. Such a shift could not just be explained away by Democrats and Independents abandoning their pro-Nixon feelings in favor of a more distanced stance. And so, the Democrats and Republicans compromised on 6 February 1974 to give formal authority to the Judiciary Committee to launch an impeachment inquiry against Nixon, the vote being 410-4. This was done under the premise of bipartisan desire for truth and closure and did not actually mean Impeachment - yet. That would follow, one scandal involving the President trying to throw off investigators by releasing edited transcripts later, on 27 July 1974. The Articles of Impeachment that succeeded went as follows:

1. Obstruction of Justice: { yes: 21 D, 6 R | no: 0 D, 11 R }

2. Abuse of Power: { yes: 21 D, 7 R | no: 0 D, 10 R }

3. Contempt of Congress: { yes: 19 D, 2 R | no: 2 D, 15 R }

So you see that the Democrats were far more united in their opposition to Nixon than the Republicans were in his support.

And at last, before the Senate even got to make a decision on impeachment (which is a very important note to make - we don't know for sure if the required Two Thirds Majority would have been reached, although it was reported to Nixon as likely), the "Smoking Gun" tape was released to the public in early August. It was the last nail in the coffin and Nixon saw himself forced to announce his resignation to prevent the possibility of impeachment.