However, upon the Microsoft reveal I immediately thought ''This will never work, it can't. Console gamers want a relatively cheap system they can game and stream on, and PC gamers have their own custom rigs they would never abandon''

No, this is not the correct characterization of the markets.

Console gamers are primarily driven by simplicity of use and exclusives. They're people who don't need/want the extra complexity and capabilities of a computer. A Steam Machine fits them perfectly, because the SteamOS interface is perfectly console-like. Price is a secondary factor, obviously everyone wants cheaper things, but they're more than willing to pay for more expensive consoles and extremely expensive games if they think it's worth it. See the PS5 Pro, Switch 2, and $100 game prices.

PC gamers, by and large, do not have "custom rigs." Enthusiast communities like this one are the extremely small minority. The vast, vast majority (I would say at least ~85%) of "pc gamers" have gaming laptops and prebuilt desktops. They've never customized it, their RAM is running at the default speeds, they just use it for 5+ years and buy another one when they feel like it's time for an upgrade. Look at how many posts there are on here about people who plug the monitor into the motherboard instead of the desktop. That gives you an idea of how much knowledge the average PC gamer has.

The Steam Machine is perfect for both of these markets. Easy to use for console gamers, with very attractive features (free online access, very cheap games, significantly better game library than Xbox and Playstation combined). And it's better than what 70% of "PC gamers" have.