Hah this is probably me. The university I work for uses a BI tool (MicroStrategy) to track students/majors/etc. But I usually find it easier to use the BI tool primarily as a way to get a CSV or XLS export of enough source data to answer my question, and then do the actual analysis in Python+pandas. I can probably formulate my query in their weird proprietary query builder, but it seems unnecessarily complicated and I already know how to do data analysis in Python.
If you're talking about Power Query, the best way to use it is to write code directly once you observe what the interface generates. It's a rather nice kind-of-functional language that really comes into its own when you start using/creating higher order functions.
Power Query gave me an intense craving for a form of SQL with a similar facility for functions.
For example, I have a lot of experience writing Oracle SQL, and Power Query didn't offer "grouping sets". But I realized it could be implemented using functions. It would be so great if SQL supported that.
Why aren't you providing your dashboards via Excel, then? What exactly does the fancy BI software do that Excel won't? I have used Qlikview but not Tableau or Informatica, and I'm a bit fuzzy on what people mean when they say "Power BI". Is using Power Query what you would call Power BI? Or does that mean using the data model stuff? Power Query is the part of Excel that I particularly like because data kind of stays where you put it.
Power Query is the only part of this that has seemed useful to me.
The Data Model and DAX in particular seemed to me like distractions that I wasted time on before discovering the M language.
Saying Power Query is mostly unrelated demonstrates that the confusion is not all mine.
I've used both DAX and M (Power Query) via recent versions of Excel. Obviously there are plenty of ways to interface with data.
https://en.wikipedia.org/wiki/Power_Pivot says both Power Query and Power Pivot are part of Excel.
What I'm seeing there looks as though it is targeted at people who make the decision to buy products like this, but not to use them. I'm not seeing what couldn't be done with Excel 365 alone. There are screenshots with verbiage about how well it works with Excel. And they look like...Excel.
I guess it supports R, and maybe makes it easier to seamlessly refresh a report. That's aggravating though, that it makes me suspect Excel might be purposely rough around the edges in order to not displace Power BI.
As I've said a few times, I like M and wish they'd commit to it.
I honestly suspect Microsoft is repackaging functionality that already existed for many years in their "ball of mud" software, with a shiny new wrapper and better defaults, but practically unusable, because people who license software are easily manipulated.
Power Automate is the most awful example of this I've used so far.
You say it's a competitor to Tableau, well, Tableau is a competitor to Qlikview, and I've used Qlikview and Excel enough to consider Excel better in every way that I care about. (My organization uses SharePoint, so that's how reports are usually distributed)
But most people seem to log in once or twice and then never again. And if you ask why it’s often because they couldn’t find the answer to their incredibly specific one-time only question, so they decided it’s all useless and they continue to work on their own personal homegrown BI-suite in excel.
Also, we use Qlik and I absolutely hate it. Luckily I don’t have to work with it too often but whenever I do I feel like I’m always fighting it. Using Power BI and Tableau felt much more like working together to solve issues.