Great new feature (preview) is introduced in Apr update of Power BI Desktop. Now we may connect directly to the dataset published in the Power BI service. Other words – create and support the data model in Excel, and generate visualization in Power BI Desktop connected to such model. Exactly the way I prefer to do.

Hi SergeiInteresting approach, any particular reason you wouldn't create the data model in Power BI desktop? Given you have to load it into there anyway in order to publish it to Power BI.com?

Hi Wyn, Sometimes i create directly on Power BI Desktop, but usually prefer Excel. Some minor things - use Pivot Tables debugging and testing model; Evaluate table directly into excel sheet not only in DAX Studio; keep measures in separate sheet placing them witin the grid in functional, not alphabetical order plus ability to copy/cut/paste them. Perhaps something else and perhaps some of above works in Power BI Desktop as well. All that is not critical, if in brief - i'm more comfortable with Excel. The only point Power BI is on couple of cycles ahead of Excel from BI functionality point of view. Hope one day Microsoft solves Power Pivot compatibility issues and both products will be even more synced.

I agree with Excel being easier to debug and analyze the data. I'm getting more comfortable with PBI Desktop but it's difficult to get out of that familiar and fantastic Excel world.I'm not sure the new power bi developments help with that process though

Hi Sergei, Just out of interest, what is the reason behind your approach to keep measures in a separate sheet? I have come accross opinions advocating storing them within data tables to keep as close as possible to the source. ThanksYury

Yury, for me that's easier to navigate and easier to maintain measures. If you have dozen of tables and few dozens of measures it's bit hard to find them if they are in different tables. As for the source usually measures work with several tables, and one set of "basic" measures is the source for other ones. Thus doesn't matter to which table connect the measue, better to separate one. That approach isn't my idea and isn't something uncommon. I guess was introduced some ago by people who used to work with SSAS. To use or not, pros and cons - you may easier find the discussions related to the topic. IMHO, doesn't matter, depends on concrete case and concrete person.

Excel and Power BI – better together | Microsoft Community Hub

14 Replies

Wyn Hopkins
MVP
Apr 04, 2017
Hi Sergei

Interesting approach, any particular reason you wouldn't create the data model in Power BI desktop? Given you have to load it into there anyway in order to publish it to Power BI.com?
- SergeiBaklan
  Diamond Contributor
  Apr 04, 2017
  Hi Wyn,
  
  Sometimes i create directly on Power BI Desktop, but usually prefer Excel. Some minor things - use Pivot Tables debugging and testing model; Evaluate table directly into excel sheet not only in DAX Studio; keep measures in separate sheet placing them witin the grid in functional, not alphabetical order plus ability to copy/cut/paste them.
  
  Perhaps something else and perhaps some of above works in Power BI Desktop as well. All that is not critical, if in brief - i'm more comfortable with Excel.
  
  The only point Power BI is on couple of cycles ahead of Excel from BI functionality point of view. Hope one day Microsoft solves Power Pivot compatibility issues and both products will be even more synced.
  - Yury Tokarev
    Iron Contributor
    May 17, 2017
    Hi Sergei,
    
    Just out of interest, what is the reason behind your approach to keep measures in a separate sheet? I have come accross opinions advocating storing them within data tables to keep as close as possible to the source.
    
    Thanks
    Yury

Forum Discussion

Excel and Power BI – better together

14 Replies

Resources