Excel and Power BI – better together

[Sergei Baklan]

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.

[#2]

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?

[#3]

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.

[#4]

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

[#5]

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 

[#6]

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.

[#7]

Hi Sergei,

thanks for your comments. I have been using a separate measures table approach for similar reasons as you. Initially, I adopted it some time ago when the link between Power Query and Power Pivot was not stable enough, which was resulting in me losing some table connections and having to unload and reload tables. The reasons I am reconsidering this approach are

1. I have not encountered any issues of losing connections to Power Query in the last couple of years

2. I have come across instances, where pivot charts would not refresh after hitting 'Refresh All' button in Excel where measures are stored in a separate table

3. Apparently, the Q&A feature in PowerBI.com works better if measures are stored in database tables

4. If a measure refers to a single data table, it may make sense in keeping it as close as possible to the source

Just thought if a mixed approach may have a merit, depending on project requirements, off-course.

Yury

[#8]

Hi Yury,

Thank you for sharing your experience. Can say nothing about Q&A, as for the rest it looks like i was more lucky, don't remember i had simular issues.  

[#9]

Following on from Yury's email

I've now come across several scenarios where RIGHT-CLICK refresh on a Pivot Table doesn't update if the measure isn't stored in the same table as the rows / column labels in the Pivot.

This sort of makes sense but is something to be wary of.   

[#10]

Hi Wyn,

I agree that could be situations when separate measure isn't updated since re-freshing and re-calculating are different processes in Power Pivot. Especially with right click when you refresh only one conenction, not entire data model. Or when source of data is internal tables in same excel file - when double All refresh could be reqiured.

And it depends on how the model anr reports built

[#11]

By the way, right now received in subscription the Do YOU Want To Be A Report Superstar? from PowerPivotPro.

Best practice #5 -  

1. Dedicated DAX measures table

[#12]

Good stuff :) I probably got the tip from those guys in the first place !

[#13]

Yoiu should check out our site:

www.xlpublish.com

Our product is designed to let you contnue to use Excel the way you have or using the new capabilities of PowerPivot and Power Query/G&T but get all the advantages of Power BI and Power BI Desktop as well.

It ships at the end of June 2017 ..

**bleep** Moffat

**bleep**@xlpublish.com

[#14]

That's funny   Replace the blocked words with the common nickname for Richard which can be used to refer to a Detective or a part of the male anatomy ;) ...

[#15]

I checked and understood nothing