skip to Main Content

By Avi Singh

Folks, thanks for attending our webinar. We did have a full house with 200 attendees. Here is how things started…

“Welcome to our webinar: Excel to power pivot: *BEEP* the gap. A lot of users *BEEP* Excel but don’t *BEEP* Power Pivot. We’re here to *BEEP* you up about the possibilities that Power *BEEP* offers…”

Okay, that wasn’t much fun. For some truly funny unnecessary censorship watch “This Week in Unnecessary Censorship” (Caution:  If you’re at workplace, you might want to use headphones).

I did sort out the matter eventually with the help of Citrix Support. Apparently there were issues with the last automatic update of GoToMeeting software. Uninstall/Reinstall restored my ability to turn off those annoying beeps.

Q&A from the Webinar

We published the Q&A from the last webinar and I’ll try to keep that up. So here’s the Q&A from the session on Mar 17th.

TIP: If you have other questions, use the search box. We have 750+ blog posts and most likely you will find something helpful
image

List of Questions (click to jump to answer):

Q: If the tables are not related, can you create a Pivot Table for each unrelated table separately?
Q: Why wouldn’t you use Microsoft Access instead?
Q: How can we automate the refresh of a Power Pivot report without using server components?
Q: How many rows/columns can Power Pivot handle? My data set is wide and flat
Q: How easy is it to share Power Pivot data with non-Power Pivot users?
Q: What are good practices for preparing the tables? The tables can have blanks, right?
Q: In my calculations, Can I exclude some of the rows where the information is not applicable and calculate for the remaining rows?
Q: Can we combine/leverage VBA with Power Pivot?
Q: How do you filter out certain fields to not bring in to PP?
Q: Why is PP not available on Excel 2013 Professional?
Q: Many questions around Date table and creating Date tables
Q: Other questions discussed
Q: Will webinar be recorded?   

Q: If the tables are not related, can you crate a Pivot Table for each unrelated table separately?
D. van Eyl

It is very common to have multiple data tables distinct from each other in the same model. Example Sales data, Budget data, Inventory data, Website traffic data etc. It is valuable to keep these data sets in one model so you can analyze them together.

Your data model should not look like mushrooms where each data set has its own lookup tables


Multiple Data Tables should NOT look like mushrooms

It should look more like a web with a set of data tables and a set of lookup tables. With the data tables connected to the relevant lookup tables.


Multiple Data Tables should form an Interconnected Web with the Lookup Tables

Another scenario for “unrelated” tables is the Disconnected Slicer trick – where you use an unrelated table, but use DAX to harvest values and then control some Power Pivot behavior based on that. You would find many examples our blog for this technique: Search for Disconnected Slicer
This article is a good example: Simplifying Time Calculations and the User Experience using Disconnected Slicers

Q: Why wouldn’t you use Microsoft Access instead ?
S. Schwantes

When comparing plain old Excel to Power Pivot, I cited four key advantages (See Office Sway Infographic):-

  1. Ability to work with vast amounts of data from various sources
  2. Model and analyze using relationships
  3. Advanced formula language with the new paradigm of “Define Once, Use Everywhere”
  4. Part of the Power BI toolset and these tools shine together

While Access does allow you to create relationships the other three points still apply. Power Pivot performs way better than Access. Better compression, lower file sizes, faster performance. Then of course, there is the DAX formula language and the Power BI toolset.

Let me make one other point, Power BI is the direction in which Microsoft is investing.

“Power BI is the future direction of Microsoft. You will be better served as a professional or an organization by going with Power BI than a different technology”

That is apparent from all the communication going out from Microsoft. One attendee of the Microsoft Convergence conference reported “Power BI” being mentioned numerous times by all key speakers including Satya; and of course James Phillips was there. And Convergence is not even a BI conference (Dynamics/ERP focused apparently).

Thus you will be better served as a professional or an organization by going with Power BI than a different Microsoft technology (or non-Microsoft technology 🙂 for that matter)

Q: How can we automate the refresh of a Power Pivot report without using server components?
B. Bryant

With Server Option: Let me first speak about how you can refresh using the server options. The three server options are SSAS tabular, SharePoint, Power BI. Each of those would allow you to schedule an auto refresh. This article considers some advantages of going the server route.

Without using the Server Option: The easiest way to refresh your workbooks is Power Update. It’s point and shoot in its ease of use. There is a trial version you can take for a ride.

Beside that you can use VBA macros or other programming to refresh your workbooks. You can read some comments about this on the Power Update page. Not for the novice though.

Q: How many rows/columns can Power Pivot handle? my data set is wide and flat
C. Switzer

In this webinar and the training classes we often throw around big numbers – million rows, hundred millions rows, billion rows. But the reality is, it comes with the fine print: Individual results may vary. I don’t want to make it sound like one of those late-night commercials, promising six-pack abs for everyone. Power pivot can truly handle an enormous amount of data. But you may need to optimize your data set.

A data set that is wide and flat is not the best shape for Power Pivot. To give your data six-pack abs, you need to go from flat to star.

“Give your data six-pack abs!”

Few ways of doing this, Power Query is perhaps the best. Watch the video here “Un”-Flatten your data using Power Query or read this blog article Flat to Star Transformation using DAX Query.

Q: How easy is it to share Power Pivot data with non-Power Pivot users?
M. Hodge

Of course, you can always share your XLSX file with the Power Pivot model and Pivot Tables/Charts with anyone (via email or other means). The question is what functionality can be expected if user does not have Power Pivot and what would be the remedial options.

When Using

Without PowerPivot…

Comments

Excel 2010

User can open the file and view the Excel Pivots/Charts. But can perform no operations on the Pivots (filtering/ using slicers etc.)

Power Pivot is a free install. So a minor annoyance but easily overcome.

Excel 2013

User can open the file.
View Pivot Table/Charts.
Interact with Pivot Tables (filter/use slicers etc.)
User cannot author model or make changes to it.

Excel 2013 has the “Data Model” built-in to all versions. It’s just the Power Pivot add-in (the authoring environment) that is only enabled for ProPlus SKUs.

Server Option
SSAS Tabular, SharePoint, Power BI

User can view and interact reports using plain old Excel without Power Pivot.
They can even do so within the browser (no need for Excel) via Excel Online or Power View, Power BI Reports/Dashboards.

This is the way to go eventually. Read this article on Server Option.

Q: What are good practices for preparing the tables? The tables can have blanks, right?
D. van Eyl

You have to pay special attention to blanks for the key/ID columns (e.g. ProductKey) used to connect your data and lookup tables in a relationship. Especially on the side of the lookup table. If you have multiple blanks in your key/ID column in your lookup table you would get an error when attempting to create the relationship.

The relationship cannot be created because both columns selected contain duplicate values

Everywhere else blanks are acceptable, but perhaps are still undesirable. For example if the product color is blank for 90% off your data set, it would not look good in pivot tables or charts.

Q: In my calculations, Can I exclude some of the rows where the information is not applicable and calculate for the remaining rows?
S. Raichura

Scenario A: There’s some erroneous data in your data set which always needs to be excluded. I would highly recommend removing those at the source or filtering them out when pulling that data set into Power Pivot

Scenario B: Data is not erroneous but just not relevant in some contexts. Example in our AdventureWorks data set, let’s say the head of bikes department is looking at reports. In her case, she may want to exclude everything but bikes. This you can do by
    – Using a filter/slicer to filtered it out in the Reports
OR
    – By defining DAX measures using CALCULATE that filter to only show the “Bikes” data
      e.g. Bike Sales:= CALCULATE([Sales], DimProduct[CategoryName]=”Bikes”)
   

Q: Can we combine/leverage VBA with Power Pivot?
D. van Eyl & M. Duncan

I am glad you asked. Of course there is plenty you can do on the Excel side with Power Pivot driven model: See some VBA Examples

For specifically working with Power Pivot, read Adding tables to a Power Pivot model from VBA (in Excel 2013). Here is a quote:

“Can you build a model from scratch? Can you add a new table to an existing table? Can you add calculated columns? What about calculated measures? , Can you change a connection for an existing table in the model?

The short answer to these questions is Yes, Yes, No, No, Yes”

Q: How do you filter out certain fields to not bring in to PP?
M. Manning

Use Preview and Filter when importing data to either filter dataset or unselect columns you do not want to include in Power Pivot. Later you can go to Design > Table Properties to make the same changes for an table that already exists in Power Pivot


Click to enlarge image

Q: Why is PP not available on Excel 2013 Professional?
P. Garay

Sigh! We have raised our voice: see Change.org petition to Microsoft to “FREE” Power Pivot
Hopefully things will be straightened out for Office 2016.
D. van Eyl suggested “Protests @ Microsoft, Washington” 🙂

Q: Many questions around Date table and creating Date tables

Many ways to do this, starting with in plain old Excel.
Here are some recommended articles, just search on “Date Table” for more.
Create a Custom Calendar in Power Query
SQL Date Tables in Power Pivot
The Ultimate Date Table–Revisited
The Ultimate Date Table
Using Query Editor to Create a Date Table

Q: Other questions discussed   

– How does one get past an IT department that says “We cant do 64bit” …..sigh.
– How to show daily sales and Month-to-Date sales in the same Pivot?
– Is it possible to refresh the Power Pivot data automatically upon opening the file?
– Would you want to edit a date table to remove unneeded dates for performance improvement?
– Is it possible to extract an excel datafile directly from a webpage without having to download it?
– Bigger Power Pivot models with lots of measures tend to slow down in the authoring environment while Pivot still run at stellar speed
– Would Power Pivot work only with fact and dimension tables?   

Q: Will webinar be recorded?

We do not record our webinar sessions. We recommend you subscribe to our YouTube channel, where we post recorded content about Power Pivot and Power BI. Or subscribe to our blog to stay notified about future webinar events.

Avi Singh

Avi Singh has personally experienced the transformation and empowerment that Power BI can bring - going from an Excel user to building large scale Power BI solutions. His mission now is to share the knowledge about Power Pivot and Power BI.

This Post Has 7 Comments
  1. – How does one get past an IT department that says “We cant do 64bit” …..sigh.

    Pray, please tell more

    1. My advice is to start writing “value adding” data models. When they stop working, take your PC to IT and ask them to get it working because the information is business valuable. Offer them the 64 bit solution – will work, but you are happy to take any her solution they can put forward (there are none). Escalate through management as required.

      1. Good suggestion Matt. I’ll add to it

        > Indeed build a solution within the limitations. You can still go far with 32 bit if being careful: Pull in limited data (2 years instead of 20), only the columns you need, summarize if you must (typically a cardinal sin when fetching data into PowerPivot) e.g. Get summary data at Daily or Monthly level instead of details on each transaction

        Then let a set of users become rabid fans of what you are producing. Have them pester IT, instead of you having to make the case 🙂

        > Other Options: Adopting Server Option can mean that only one person (You, the Model author) may need 64-bit. Everyone else can happily use 32-bit. Your IT team may be more amenable to that. You can ask if they have an “Exception” process (most IT teams do). Offer that they do not have to provide support for the machine running 64-bit Office – you will take the risk and handle any issues.

        Hope that helps.

        1. The 64 bit “Sigh” question was mine on the Webinar; very much appreciate the follow up advice as at times it seems that I have discovered a new circle of “Technical Hell” that old Dante somehow overlooked.

          My organization is going to go full bore PowerBi via Office 365 but the rollout is seemingly unending and with no known completion date. In the meantime, my intended users have a collection of machines that include, and I kid you not, Excel 2003, 2007, 2010 to some random folk who actually have 2013. Obviously upgrading those as we go but some of those machines are not surprising almost as old as the software; which means extremely limited CPU’s and RAM. Of course, we also have the “free” sharepoint with all that implies (to be upgraded soon I am assured), an existing BI team that is “wary” to the extreme of “whatever it is I am doing,” and an IT team that has scoffed at the thought of giving Server access to “some random Ops dude.” Management is supportive but unable to really grasp any of the technical stuff; they love and want what I put out but are unable to provide much other then a mumbled and embarrassed “let us know if we can help” along with a plea to “please don’t piss off anyone in IT ok?”

          To recap, I have fairly advanced modeling ready to roll out to an organization that is about 6 months to a year away from being able to support it. My audience is potentially spread out over a wide geography and most have machines that will literally roll over and die just by sniffing at anything complicated.

          That said the journey, as painful as it has been, is slowly but surely bearing fruit. In large part that is due to accepting the current reality and exploiting it as far as possible exactly as Avi points out.

          Specifically, I had the foresight to get as beefed up a machine as I could last summer when I first stumbled onto Rob and Bill’s excellently enticing books. As a side note, yes, I still sit up late at night and alternatively praise and damn both of them for inflicting this on me along with a host of other experts and their siren call of possibilities. That includes Ken Puls, Boyan Penev, Javier Guillen, Avi, Gerhard, etc who all dance around in my dreams spitting out various Dax and M tips. Yes, Rob is always dressed up as Frodo and throws apples marked with GFITW around which admittedly freaks me out…

          Where was I, oh yes, my machine: it is still nothing special mind you, but it has an I5 and some additional RAM and it can, if I nurse it along and streamline things as possible, handle the basic model of what I am doing without crashing too much (aside from the daily warnings that I might want to upgrade to 64 bit, gee thanks Microsoft!)

          My brainstorm, and what I hope might help anyone else reading, was to take my machine and use the model to spit out a simulation of the better structured queries I hope to someday get from our BI team in the form of pivot tables (summarizing Daily data to monthly, etc)

          I then use Power Query to grab the resulting data on those Pivot tables and load them into a far less complicated model that my end users, at least the ones I have upgraded to 2013, can actually use from Sharepoint.

          Note, this is clunky. It is inelegant. I had to scrap all of the sophisticated stuff I had done for grabbing the data in mass thanks to unending webaccess issues on the antiquated SharePoint trying to interact with the as yet non-existent 365 stuff for permissions. The whole thing really offends my senses as I had to rewrite everything in the most basic straightforward way to simplify everything possible. But…it works.

          As the result, we currently have integrated reporting on sales history and forecasts, aged inventory, what is coming up demand wise historically in the coming weeks and months broken out by geographic area’s and vendors for our branches and purchasing teams.

          Good stuff so far; mere tip of the spear for what is coming though.

          Move their cheese? Ha! No, I am not just going to move their cheese; I am going to kick their stinky old worthless Data Cheese to the curb and hopefully replace it with a permanently running model that sits there every day and mocks how worthless their old cheese truly was.

          So the biggest obstacle is eventually one of patience. Build the framework, get folks using it, build the pressure from multiple sources to say “what is THAT? It is always live and updated? How? Why dont we have THAT? Where are my Analysts….why dont you give me THAT? ”

          One nerd at a time….the Powerpivot army slowly builds.

          1. Dave, that’s quite a story! You should turn this into a blog post as a guest author on our site 🙂

            I hear you. It frustrates me equally. When I was inside Microsoft you always have access to the latest and greatest of their software so this issue does not even arise. When I started engaging with users outside of Microsoft, there is no end to such tales. You can get away from a lot of this by having a SharePoint setup with Power Pivot/Power View enabled. Expert users can build and upload their models and Users view all reports on the web (they don’t even need Excel installed). But that can run into complications too. There is also hope from PowerBI.com (the cloud service).

  2. If there are any attendees that are working through Power Pivot data models for their business and have some ideas on how they would like to implement formulas with DAX (Power Pivot’s expression language), you can post your questions or comments at

    https://powerpivotpro.com/the-faq/

    It is my experience that, when posted with an example of what you are trying to do, a friendly moderator or other site visitor will help out.

Leave a Comment or Question