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
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):-
- Ability to work with vast amounts of data from various sources
- Model and analyze using relationships
- Advanced formula language with the new paradigm of “Define Once, Use Everywhere”
- 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. |
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 |
User can view and interact reports using plain old Excel without Power Pivot. |
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.