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.
Hey AlexisOlson ,
Were you able to try my DB connection process? I don't have premium ATM, didn't know if you do and could test my theory?
Pete
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.
Many thanks AlexisOlson , KNP for looking into this, much appreciated.
Anonymous, Sorry, it appears as though the correct setup of the PBI dataset is the way to go here. This would be my preference anyway to be honest. As AlexisOlson mentioned, I also find Power BI to be far more user-friendly when setting up data models/measures than Excel.
Pete