Forum Discussion
Create and maintain shared model on multiple platforms
Hi KenStewart ,
According to your situation, why don't you use Power BI directly? Pivot/Query is the precursor of Power BI, so you could use Power BI instead of Excel.
Then you could use Power BI Desktop to connect your data source and publish report to service. In Service, you could set auto refresh.
If you must use Excel, you could use it firstly. Then save as an xlsx file and import it in Power BI desktop.
Hey, v-eachen-msft,
Thanks very much for the feedback. I'd be happy to use much of the Power BI family in favor of Excel, but I don't believe it can be replaced for several reasons -- among them are the need to write VBA code and to distribute interactive macro spreadsheets to other users. I'm still trying to work out the best way to do so within the Power BI context.
Are you saying that I can originate Power BI dataflows from within Desktop? I want to be able to refresh reports and dashboards being distributed from Service to other users, but I thought these dataflows had to be created within Service. I also want to take advantage of Quick Insights and Q&A, which I further understand aren't available through Desktop.
As to using Excel first and propagating the model to Desktop then to Service, that was one approach I considered. That gets back to the question of creating dataflows. Right now I'm importing the "raw" ERP spreadsheets into Service using the on-premises data gateway. I use a little DAX for "light cleaning," like promoting headers and typing, then access the entitities from Desktop for final transformations, formatting, categorization, etc.
This appears to give me the most powerful and complete model for creating reports within Power BI. I can also then export the finished model back to Excel. You may be right about modeling in Excel first, but then taking advantage of Power BI modeling and saving back to Excel seems a little circular.
I guess I'm looking for suggestions on how to improve or streamline this process while still enabling the capabilities I need, as described above. An ideal step-by-step procedure might be something like:
1. Import ERP spreadshets into Desktop.
2. Perform all transformations and modeling in Desktop.
3. Export final Desktop model to Service and Excel.
4. Configure and enable dataflows within Desktop.
5. Develop Excel applications and Power BI reports/dashboards wherever most suitable.
I hope this answers your questions and clarifies some of my thinking, which may be wrong. I'd be happy to find out that I'm missing something basic and that there's a much more direct approach in practice.