You may find this seminar that @sam.mckay did last week useful in addressing some of your questions:
I am one of those people you referenced to the top of your post – I’ve used Excel extensively since the late 1980s, but barely use it at all anymore except to bring data into Power BI. When I want to break down a complex measure or otherwise drop back into Excel, I typically use the Analyze in Excel external tool that @AntrikshSharma mentioned above:
I still occasionally use Excel for quick and dirty analyses, but for anything with some complexity to it, I now do it in Power BI from the start.
I hope that’s helpful.
- Brian