Query with CTE gives error

I am not sure why it isn't recommended. I don't see the documentation saying that and I don't see a list of query limitiations.   

I am evaluating Power BI to determine in my organization wants to buy and use it as a visualization tool.  I am not trying to "test my skills".   I am trying to evaluate products to see how they work (or if they work in this case).  

I cannot import the tables and join them because they are too large. I cannot use Direct Query because it doesn't support many to many relationships.  All I see are a lot of llmitations.

Power BI is supposed to be self-service.  That typically means not writing views or stored procedures (and generally not CTEs) and if a feature is there, I expect it to work.