Query with CTE gives error

FYI, most people start these conversations with "thanks for trying to help me ..."

Writing custom SQL query generally works just fine - it's just recommended that you don't do it that way, that you create a view. Along those lines self-service BI doesn't mean that people are going to write SQL queries with CTEs to access the data, so I don't see not being able to do that in the tool as a major limitation to self-service BI.  The only limitation that you have here is that you need a view created in your database, and that's not a limitation of this product.

To get around the limitation of creating a view, this solution does allow you (as a self-service tool) to take the tables out of your database and do the joining yourself using the query editor GUI.  Your data must be very, very large if you can't get that to work in Power BI, and if your data is that large I'll ask you again, why aren't you doing that in a view in your database?  If you have a nail that you need to put in a wall and you reach in your bag and get a screwdriver, well I don't think that's the screwdriver's fault.

One last point, I realize you're not trying to "test your skills" but I will say that this product is not BI magic dust - it does require some skills to use. If you're the type of person that can write a SQL query with a CTE then you'll be able to figure it out.  Unless you don't want to figure it out.