My first thought on seeing this Thread was "Sure, love 'em!" - because I live in Puget Sound, which gets almost all of its energy from the Columbia River hydroelectric complex.
Then I realized after a bit of reflection that in several thousand hours playing Civ VI, I can count on the fingers of one hand the number of times I've actually built a Hydroelectric Power source in any game. - And it's for all the reasons already mentioned by others here: not many good sites for such power sources, comes too late in the game, takes up a District slot which may be better used for something else, etc.
And the negatives are particularly awkward because they are Artificial:
1. As stated, the world's first hydroelectric power installation was in 1878. The world's first electricity-producing coal power plant wasn't until 1882, and by 1889 there were over 200 hydroelectric plants in the USA alone. Effectively, hydro and 'conventional' coal-fired power was Simultaneous.
2. Virtually all the dam-produced reservoirs have multiple uses. Lake Mead produced by Hoover Dam is also a major recreation lake. Grand Coulee not only powers Seattle and Puget Sound, it also provides irrigation water for a large part of central Washington state, which is a massive agricultural production zone - if you are eating an apple in the American hemisphere, chances are it came from hat area. Similar examples could be applied to virtually all the major hydroelectric dam/reservoir complexes. And note that one of the first 'major Dam' complexes known historically, the Marib (or Mar'ib) Dam complex in modern Yemen, (1750 BCE approximately) created an irrigation reservoir that watered enough land to feed 50,000 people - that's a very large city by the standards of the first 3 - 4 Eras of the game - dams of all kinds and purposes are badly mis-modeled in the game: too late and not important enough for Food and population.
3. Size and output. Of the top ten power-producing complexes in the world as of 2020, 8 are Hydroelectric. The other two are numbers 8 and 10 on the list, and they are Natural Gas and Nuclear, and the nuclear plant is virtually tied in output with the world's largest Wind powered generation unit. Hydroelectric on any comparison of installations is far more powerful than any other source being regularly used today: coal, gas, wind, - and that's only assuming conventional hydroelectric dams on rivers producing reservoirs, the Civ VI model. There is a proposal on the books for a Tidal hydroelectric complex that would produce 4 times the electricity of the largest current power plant anywhere.
Hopefully, Civ VII will include a 'redesign' of the mechanics for dams, hydroelectric, coal and other Power sources to allow them to produce the way they should and reflect their IRL importance.
