Excel Pivot Tables or Power BI Matrix ? – Use Both With XLPublish

 

Let’s start by just looking at these two “Tables”:

Excel Pivot Table:

Power BI Matrix Visualization

 

The Same?

Look pretty much the same, eh? Well to the Viewer they are, but for the person distributing this report there is a huge difference in delivery, capability and maintenance between an Excel file with a Pivot Table and a Power BI Report with a Matrix Visualization.

How many managers have told me “I don’t like Pivot Tables!”, or have completely lost their way/minds in Pivot Tables that I have created only to get frustrated and abandon reports because of it ???  Lots sadly.

Also does it really make sense, (or is it actually a serious security problem … GDPR anyone?), for you to have to distribute an entire spreadsheet just so your bosses and those who are interested in your aggregated results can see their Pivot Table data (which as mentioned above they hate anyway) ?

This is a not only a frustration it is actually a serious blocker for moving forward with your models that are so important to your business and its managers and key to your businesses data management “story”.

But Pivot Tables Are Still The Key Tech

The fact is that I strongly believe that Pivot Tables (especially when sourced out of the Data Model using Power Pivot and/or Power Query) are undeniably the best part of Excel’s toolkit – Bar-None.  If you are a serious Excel developer you must be using Pivot Tables!   ( IMHO anyway … ).  I also believe that this will never change despite renewed efforts these days to “clip Excel’s wings”.

But I know that the problems listed above are real and not trivial.

So how can I “have my Pivot Tables and Distribute Them Successfully Too” ?

1. The fact is that Power Pivot natively cannot consume Pivot Tables other than a simple Range of cells with info in them. They do end up in a Table but are crude and limited so that is not a great option to me.
2. You can also not Export a Pivot Table from Excel either (except as a crudely refreshable image object).
3. You can no doubt emulate a Pivot Table in Power BI using the Data Model within Power BI Desktop but that means learning the features of Power BI Desktop’s Data modelling  first … and it also means probably exporting all your detailed source data to the Cloud.
4. Or you can get the XLPublish Add-In and simply “Tag” your existing Pivot Table(s) in your model with one click, Publish to Power BI in one-click, and then create your “Matrix” in your Power BI Report by simply dragging the data to your template in the correct order to recreate your Pivot Table of “results” exactly as it looks in your Pivot Table in Excel.  All of this without publishing any detail data to the Cloud … just the Aggregated data of the Pivot Table(s)
5. Then you distribute this report(s) to your user(s) in Power BI using an App Workspace so they can see the exact results of your Pivot Table (either in a single matrix with hierarchies or as several individual Matrixes and/or Charts each at the level of granularity that your “Consumers” would feel comfortable with.
6. Then when/if your source Pivot Table gets refreshed in your Excel file with new data, you simply refresh all the Power BI objects in Reports or Dashboards dependent on it with one “RePublish” click in Excel.
7. And a couple of more things …with XLPublish you can capture all possible data combinations from your Pivot Table in our own “Cube” datasets and further you can flag XLPublish to capture any number of serial versions of the data that you publish from your Pivot Table.

So What Do You Get?

So look back at the images at the top … Same exact report … But with the data set in Power BI you can go on to reveal this same information in many other ways like for example these very simple visualizations:

Not Filtered:Filtered By Departments and Age Cohorts:

So in short, by using XLPublish and Power BI with your Excel Pivot Tables as a Source you get:

1. No detail data published to the Cloud or to those who receive your Reports or Dashboards.  Privacy is ensured.
2. A great distribution “story” … here’s the same Report shown working “Live” on my iPhone (for what that’s worth) along with the top of my moustache and the bottom of my nose doing my best Wilford Brimley imitation):
3. Your source Excel file stays exactly where you had it in the first-place inside your Network or in a Cloud storage .. wherever you want.
4. You can refresh these reports in real-time by Clicking one button in your Excel file.  Within seconds those with rights to your Report will see their changes wherever they are on whatever device they’re looking at.

So there ya go !!

I have shown you the “Distribution Story for An Excel Pivot Table” that the XLPublish Add-In provides.  This is just one of the many capability that frankly no one else offers at this time and at a price that is hard to beat.  I will show you the “External Data Story” in a future post about XLPublish Add-In

Enjoy !!

Dick