Connecting Excel do your Power BI Desktop database has many useful applications, for example you can use Excel to do some quick and advanced testing on the values of your Power BI model.
Power BI Desktop has an Analysis Services Instance behind the scenes that you can indeed connect and build Pivot Tables with Excel, the problem is discovering the TCP port of that instance.
A simple solution would be to install DAXStudio, connect to PBIDesktop and then inspect the port on the bottom of the window:
Which is fine if you do it a couple of times, but if you want to be constantly connecting to Power BI Desktop a better alternative would be to:
- Install the PowerShell module “PowerBIPS.Tools” that is available on the PowerShell Gallery
- Register a context menu entry that will save on the folder an ODC connection file to every Power BI Desktop file opened