There is no “Pivot Table” in Power BI but there is a “Matrix Visualization” which is almost equivalent to a Pivot Table in Excel. It offers nearly the same features as Pivot does in Excel but they are named differently and of course placed differently.
I think it is the wrong youtube link (a useful one for other reasons though 🙂) but, it is the right solution. Thanks! I was a bit discouraged when I followed your instructions and saw that Edit DAX... was greyed out and also when I saw an MDX query in the Edit Query... dialog. However, sure enough, when I typed in a DAX query it works!
You can also export a connection file for the connection to the PBI service and edit the xml in the odc file.
First export the odb connection...
Then edit the XML...
Then just launch the odb file and excel will start with the connection you need. Then you can right click on the connection and enter your dax query in the Command text window of the Connection properties (you could also do this in the XML) and your table will load.
If you want you can use the Performance analyser in PBI Desktop to copy the query of a visual and paste that into the Command text.