You can download the data model, the excel frond ends and the data sets in this folder.
The techniques used in the spreadsheet for connecting to PowerBI Desktop are not officially supported by Microsoft, ( as of 24 may 2018, Microsoft don’t mind people using this approach ) but I think are useful to understand how the MS BI stack works, especially if you are coming from an Excel Background, and Installing SQL server developer is either intimidating or not allowed.
I really appreciate if you vote for this idea to make this scenario offically supported by Microsoft
As far I am aware there are two approach to access PowerBI desktop, or more correctly the SSAS instance launched by Power BI Desktop.
The live connection is very interesting but it has the drawback that if you close PowerBI Desktop the pivot table stop working, so you can not share the spreadsheet, one solution is to use cube formula as they are persistent, if you don’t use know what a cube formula is then you are missing of the most powerful feature of Excel.
But what if you want to have a pivot chart, or a pivot table that you can keep using even if you lose the connection, or if you want to share the results with people that have not access to the data model, turn out it is possible, welcome to Excel Pivot cache
- Invoke the function SSAS_QUERY
- The Parameter is optional:
- Either you write a DAX Query to retrieve the fields you want, MDX is supported too, personally I find MDX more suitable to import measures with different dimensions, I understand that DAX support this scenario too using crossjoin but I never manage to make it works correctly.
- or Just click ok, than you can browse the SSAS cube, you can select any dimensions and measures you want, but mind that for a big cube, a query fetch the result faster.
- Keep Powerquery as a connection only.
- Insert a pivot table, use an external data source, choose connection, select the Powerquery Query, and voila
Excel cache the data in the Pivot, not only that it is extremely compressed.
Notice here PowerPivot is not used at all, Excel is acting as visualisation layer to PowerBI Desktop, leveraging two well known capabilities cube formula and Pivot cache.
Microsoft plan to release SQL server v.next this year, and then we can deploy the data model built with Power BI /Powerpivot /Powerquery into a production system, that’s what I call a natural growing path from self service to corporate BI.
Edit Dec-17 : i add a new file that use only VBA, so Powerquery is not required, it can be useful if you are still on Excel 2007, or you can’t install Powerquery.
Project Controls blog 
Can you use this to access data models from multiple workbooks too?
LikeLike
unfortunately no, there is no way to access Excel Data model from PowerBI desktop, ( Tableau can do it, go figure), you can always use DAX studio to extract Data though.
LikeLike
I have a situation here which I think could potentially use this method but I am not 100% though. So would like to hear your view here. I used PQ to cleanse and summarize a dataset which was subsequently loaded to Excel’s Data Model where I have built my power pivot tables. Is it possible that I can use a separate Excel file to build power pivot tables based on this Data Model which resides in a different Excel file? So it is kinda like Front End: Excel, Back End: Another Excel’s Data Model created by the PQ? If so, how can I access to this Data Model. Many Thanks!
LikeLiked by 1 person
no you can’t connect to an Excel data model, what you can do instead if your tables are not big, is create first data model, show the tables in the sheet using linked table, and then connect the second excel data model, I initially used this approach, but once my data become really big, I start using the PowerBI desktop approach
LikeLike