Forum Discussion
Having Power Bi Dataset as External Source for Excel Data Model
I followed your steps BA_Pete and connection works as expected as long as I select the 'From Analysis Services', not the one below.
Unfortunately, from then on, the experience is exactly the same as the 'normal' method of connecting to a Power BI dataset. i.e. no option to tick the 'Add to data model' and therefore nothing shows up in the Power Pivot tab.
So Anonymous, as AlexisOlson has pointed out, all modelling needs to be done in Power BI to ensure you have all the measures and columns required. Even then, it's still quite limiting if you want something other than Pivot Table/Chart, unless you want to use cube functions to build a table/report.
There are a bunch of random (badly written) ideas on the topic that you could vote on, but I couldn't find any that already had a lot of votes.
https://ideas.powerbi.com/ideas/idea/?ideaid=8c76088a-d468-41e3-a584-e3d535385994
Hopefully things change in the future.
But the thing, when you will do this, it will get it for you as a connection, but not as a data model, right?
Anonymous ,
I don't know I'm afraid. As I said, I'm not able to fully test it currently, sorry.
Based on the fact that Analysis Services is part of the Database source group, my assumption is that it would be connected as an open tabular model and loaded into the Excel file data model as such.
If this method doesn't work, then I don't see any other way to achieve what you're asking for, other than making connections to each of your dataset's tables again and rebuilding the dataset model in the Excel data model from scratch.
Pete