Power Pivot Online Class Jun 8-9: Join the Party

May 15, 2015

By Avi Singh [Twitter]

Last online class we hit an important milestone where we had our first attendee from South Africa. Why is that important? Per Google trends, South Africa is the hottest place for Power Pivot!

South Africa finally joined our party!

Besides the geographical distribution, really what kind of folks are attending our classes? Take a look at the word cloud below based on the business titles:-

Among class attendees, Analyst dominate but we have folks from many walks of life

Sure, ‘Analyst’ dominate but if you look carefully, you’ll find – CFO, CTO, Student, Marketing, Accountant, Finance and even a Director of Football Operations :-) That is the goal of this class and my personal goal too. To spread this revolution far and wide, touch folks from all backgrounds and all titles. All are welcome! The Online Class is specifically designed for the beginning user.

Your next opportunity to attend our Online Class is on Jun 8-9th. Use links below for information/registration. Keep in mind, Online Class attendees are invited to a Q&A (Office Hour) session each month to aid them in their Power Pivot/Power BI journey after the class.

Online Class
Jun 8-9th

PowerPivotPro Online Class

Free Webinar: How to Build BI Solution Using Power BI

May 11, 2015

By Avi Singh [Twitter]


Rob made a joke in one of his classes, that “Microsoft gives you the parts to the Porsche”. It’s a great product, but some assembly required :-) The biggest roadblocks we see to Power BI adoption (the toolset, not necessarily the powerbi.com service) are

  • Lack of awareness
  • Lack of clarity on how to get started

    For our past webinars, we have attempted to focus on Excel users who are unaware of the benefits of Power Pivot. For our next one, we will attempt to target folks, who are aware, but unsure as to how to proceed. We will try to provide you the missing manual to put together your Porsche!

    The webinar is targeted towards the audience below. For more information/registration, click the links below.

    Free Webinar
    Jun 2

    PowerPivotPro Free Webinar Series

    Managers/Decision Makers: Who are looking to select a toolset to build their BI solution. Or those who have selected Power BI toolset but looking for a roadmap to implement that solution

    Technology Professionals/Excel Users: With Power BI, Excel users can go toe-to-toe with top BI professionals and roll out complete BI solutions and not just ad-hoc reports/analysis. Learn the roadmap to transform your career.

  • Just around the corner: May 4-5 Online Power Pivot Class

    April 27, 2015

    Friends, the goal of our Instructor-Led Online class is to reach the folks, we are unable to reach via our other training options. I am happy to say that attendees for the past classes have spanned the globe. Our next class is just around the corner on May 4-5 (Mon/Tue 9-11AM PST). If you would like to jumpstart your Power Pivot and Power BI journey from the comfort of your own office or home, go ahead and sign up.

    Class Attendees span the Globe!

    General feedback has been positive, with a 4.3 out of 5 Course Rating.

    Here is what one attendee had to say

    “The class was great! Just the amount of time so as not to interfere with our hectic work schedules, and the hands on homework really helps drive understanding. The instructor was also fantastic and did a great job pausing for questions and explaining the material in layman’s terms.”

    Do note that this is a basics course targeted towards beginners. Do evaluate all the learning options and choose one that best fits your needs and learning style. A unique feature of the Instructor-Led Online class is that you are invited to a Q&A session each month, where you can bring in any of your questions.

    “You are invited to Q&A Sessions each month to help you along your Power BI Journey”

    The idea is to to offer continued help, as you progress on your Power Pivot/Power BI journey after the class.

    Power On!
    Avi Singh

    In-Person 2-Day Class, May 12-13, Cleveland

    April 17, 2015

    Post by Rob Collie

    2-Day In-Person Power Pivot Class

    Hey folks, just a quick reminder that I’m teaching a class next month here in Cleveland.

    (Someone registered today from New Mexico!  If they can make that trip, anyone can.)

    Optional Gathering/Dinner on “Middle” Night


    Some Folks from the August Class Throws the “Excel Gang Sign” at Dinner
    (Can You Spot MrExcel Himself, Bill Jelen?)

    On the night of the first classroom day, we’ve been running an informal gathering at a local restaurant, and many people reported that it was one of their favorite social outings ever.  Just being surrounded by people with similar backgrounds, mindsets, and problems is a unique experience, especially for the Excel crowd.

    Totally optional, but we’ll be doing this at all classes.

    Included:  Thumb Drive and 50% Off Online University

    All Students Receive a PowerPivotPro thumbdrive containing the materials from the course:


    And also a 50% off discount code to the online video course.  Past students have found it to be a helpful “reinforcement” to the live class (as well as covering topics that we don’t have time for in two days), so we have decided to offer it as a bundle.

    May is actually a great time to visit Cleveland too.  Hope to see you there.

    Click Here for More Info and Registration

    Another Previous Cleveland Class

    “Beta Test” the New Reference Card?

    April 7, 2015

    Post by Rob Collie

    Power Pivot / Power BI / DAX Reference Card

    RefCard Pt 2: Captures/Condenses Important Tips & Fundamentals from Our Years of Teaching
    (Five “Sides” Pictured Above, Sixth “Side” is Still in Development)

    PDF Preview Available Later This Month

    Short Version:  drop us an email at the address below, and later this month we will send you a preview of the new reference card in PDF format.

    Drop Us a Note to Get Your Preview Copy

    “Completing” the First Ref Card

    Creating the first reference card might be the smartest thing we’ve ever done. 

    We can say that without hubris, because it was actually NOT our idea, it was Bill Jelen’s.

    Still though, we have the wisdom to recognize Bill’s genius.  The reference card has been a CRITICAL component of our classroom teaching for years now:

    Power Pivot / Power BI / DAX Reference Card

    Ref Card Part 1 (Laminated Version) is Only $2.95 at MrExcel.com – Click Image
    (Preview Participants will Also Receive a PDF of Part 1)

    Final “Product” is TBD

    1) Given that the new reference card is six sides, it ideally would be printed in some sort of “tri-fold” laminated format, and in full color of course.  That’s might be pretty expensive to produce, so we’re still noodling on how to do it.

    2) Plus, it would make sense to merge it with Part 1 to form something more like a quad-fold.  Hmm.

    3) We want your feedback on Part 2 so we can improve it before we do anything rash like print 5 thousand copies.

    4) I’m also contractually obligated to say that we WILL improve the aesthetics of Part 2 as well.  Because, well, *I* am designing Part 2, and while I might be a decent communicator, I’m not exactly a graphic designer.

    Anyway, Sign Up for the Free Preview PDF Smile

    Drop Us a Note to Get Your Preview Copy

    Best way to learn Power Pivot: get over analysis paralysis

    March 30, 2015

    I have loved reading ever since I was a young kid. In my adult life though, reading fell on the wayside, as life got busy. Now, I have worked as a Business Analyst for a long time, and the role suited me naturally. However at times I fall victim to analysis paralysis. My inability to get my reading fix, was one casualty. Let me explain.

    I felt that since I was soooo busy, I had to make the best use of my time. I wanted to pick the BEST business books, the BEST self-help books, the BEST fiction books that would enrich me. Unfortunately that meant, I would occasionally go on the hunt for the “best”, give up after a while, and repeat the cycle; without ever actually reading many books.

    It was years before I realized my folly. The way out was the “Librarian’s Picks” section at our local library. I would trust their judgment, and started picking several books from that section. I did more reading in that one year than I had done in the previous ten years.

    If you are anything like me, you have thought about attending Power Pivot training, but have vacillated for this long while. Let me say, a Live Instructor-Led class is the most intensive way to jumpstart your Power Pivot journey. There is still time to enroll in our class on Apr 1-2 in Seattle.

    Live In-Person Class
    Apr 1-2

    PowerPivot for Excel Live Class (Seattle)


    Power On!
    Avi Singh

    Course Prep: Excel 2010 and Excel 2013 PowerPivot Differences

    March 20, 2015

    By Avi Singh

    Matt Allington, our friend from down under, had suggested the idea that we make some basic information available as course prep to students who enroll in our Live Instructor-Led classes (Upcoming: Seattle Apr 1-2, Online Apr 6-7, see Choosing your training option). Offering a course prep would enable us to get to the real fun stuff even quicker. I have taken that to heart and publishing some information in this post below and a Course Prep playlist on our YouTube channel. This is meant as a prep for students enrolled in our class, but may be helpful to others as well.

    Course Prep Video Playlist (more videos to be added soon)

    Key Differences between Excel 2013 and Excel 2010 User Interface

    Launch Power Pivot
    Create New Measure (Calculated Field)
    Edit Measure (Calculated Field)
    Insert a Pivot Table connected to Power Pivot Model
    Add Excel Table to Power Pivot
    Power Pivot Home Tab
    Power Pivot Existing Connections

    Launch Power Pivot

    PowerPivot > Manage
    PowerPivot > PowerPivot Window

    Read the rest of this entry »

    Thanks for attending the *beeping* webinar: Q&A From Webinar

    March 18, 2015

    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.

    Read the rest of this entry »

    Excel Users Everywhere: Free PowerPivot Webinar Tomorrow Tue Mar 17th

    March 16, 2015

    By Avi Singh

    Excel users are everywhere! Even at Toastmasters – a nonprofit educational organization focused on communication and public speaking. The other day, I had dropped in at our local Toastmasters as a guest, after a long break. It felt good to be back. The meeting had the familiar structure – speakers, evaluators, table topics. When it was over, as we were heading out, I struck up a conversation with another attendee. As members come from varied backgrounds, I didn’t know how technical I could get when describing my vocation. Thus, I spoke in general terms about my work at Microsoft, then leaving Microsoft to pursue a path of my own. And then I asked…

    Me: So what do you do?
    FTM (Fellow Toastmaster): Well, my job title says Quality Assurance, but that doesn’t quite describe what I do.

    Me: Happens all the time, so what do you really do?
    FTM: Well, I do a lot of data analysis.

    Me (excited now): What tools do you use?
    FTM: My company has a lot of data stored in these large databases, but they didn’t want to make a big investment in BI. So I just pull the data and analyze it in Excel.

    Me: Did you know that Microsoft has released a new set of tools, called “Power BI”, which supercharges Excel?
    FTM: Can it handle multiple data sources?
    Me (trying to keep off the smug smile): Yes, indeed.

    FTM: Can it handle large data sets?
    Me (giving up now, a wide grin plastered on my face): It can do that and a lot more. You should attend one of our upcoming Webinars…

    Free Webinar: Excel to Power Pivot – Cross the Gap

    I have seen how transformative this path can be. From my own journey (read or watch) and from many others I have witnessed since joining PowerPivotPro. I believe the three steps below are needed to really set a person or organization on this path:-

    1. Awareness of Power Pivot and it’s superpowers
    2. Learning basic Power Pivot
    3. Using Power Pivot with your own data set

    Typically after the third step, you have experienced the power and are motivated to pursue this path! But there are many who never make it to that step. My mission is to change that for as many people and organizations that I can.

    Join me for our upcoming webinar tomorrow, where I lead you to the promised land :-)

    Mar 17

    We’re planning the next event in April covering a new topic. Subscribe to our blog to receive updates on future events.

    Listen to Me Interview MrExcel (Bill Jelen), and See Us Both at the PASS Biz Analytics Conference

    March 10, 2015

    Post by Rob Collie


    I Interviewed Bill Via Phone – Me in the Arctic of Ohio,
    Bill in his Winter HQ Down in Florida

    It’s not often that you’re asked to interview one of your friends, but that happened to me last week.  The folks at PASS thought it would be interesting if I interviewed Bill Jelen, and then we published that interview on the PASS Business Analytics Conference site.

    An excuse for me to call up Bill and jaw for awhile?  “Sign me up!”  And off we went.  I was personally “hooked” by the conversation for a full 40 minutes.  Most of Bill’s answers, I had never heard before, and they stimulated some serious “wow” thinking for me.

    We found out, afterwards, that the PASS folks were expecting a 10-minute interview.  Har har.  Bill and I later chuckled, in email, at the futility of such expectations.  “Let’s get two of the biggest chatterboxes we can find, put them on the phone together, and keep them to 10 minutes.”  But the results were good, and PASS decided to publish the whole thing.

    They were also gracious enough to share the audio file with us, so I’m posting it here today:

    (Fancy Inline Audio Player Should Work on Most Browsers, Or Let You Download the MP3)

    Highlight:  Bill STARTED in Analytics!

    I can’t believe I had never heard this before, but Bill first started using spreadsheets as a reporting tool.  (Not as an accounting or financial tool).

    Even more delicious:  he was using spreadsheets as a replacement for an extremely expensive reporting solution that didn’t work!

    That was in the 1980’s.  How little has changed between now and then!  Before the advent of what I call “Modern Excel” (aka Power BI), the world was in EXACTLY the same state 20+ years later.  (And in places that have yet to grasp the potential of Power Pivot and its kin, things STILL have not changed.  But at least now we have a way out of the decades-old mess).

    Anyway, definitely listen to the interview.  Lots of interesting nuggets in there, such as the observation that more than half of analytics pros are women.

    See us both at PASS BA Conference Next Month!

    It’s not too late to join us both, plus other Modern Excel experts, at the PASS Analytics conference next month in Santa Clara.

    Click to Visit the Modern Excel Speakers Page

    Next Month’s Speakers Include Quite a Few Proponents of Modern Excel / Power BI

    $150 Off – Discount Code

    Register Here and Use Code BASPROB

    Excel to Power Pivot Webinar Countdown – in 10 days

    March 6, 2015

    By Avi Singh

    Happy Friday! Our next webinar is coming up in about 10 days from now, on Tue Mar 17th. Make sure you save it to your calendar. See webinar page for more information. Participants would also have an opportunity to sign-up for our online class at a discounted rate.

    For the webinar, we have doubled the capacity from 100, up to 200 attendees – so if you were unable to get in on a previous webinar, take heart! There would be an opportunity to ask questions as well. See Q&A from the past webinar.

    Here is what we would cover:

    Walkthrough key steps in using Power Pivot and explain how Power Pivot supercharges regular Excel
    Do it Yourself! We would provide you dataset and instructions to try those steps yourself at your own pace and build your first data model. Best way to learn is by doing.
    Q&A: There would be opportunity to ask questions and seek guidance regarding using or deploying Power Pivot or Power BI
    Resources to continue your journey

    See links below for more details:-

    Mar 17

    We’re planning the next event in April covering a new topic. Subscribe to our blog to receive updates on future events.

    PowerPivotPro Expands to Europe/Africa/Asia, Welcomes Dany Hoter!

    March 4, 2015

    Post by Rob Collie

    My Business Intelligence “Yoda” Figure Joins the Team!


    Master and Student, on a Fact Finding Mission in Denmark, 12 Years Ago!

    In late 2002 back at Microsoft, I suddenly found myself in charge of most of the new BI-focused features of Excel (in the product wave that ultimately became Excel 20007).

    The interesting thing is, I had ZERO idea what BI was back then.  I mean, I wasn’t even sure how to spell BI, as the old joke goes, heh heh.

    I wouldn’t have survived that era without the patient tutelage of Dany Hoter.  Dany was our resident guru on the team back then, and still plays that role for Microsoft engineering teams to this day (he moved back home and now works for Microsoft Israel, but he and I met when we both worked in Redmond WA).

    If He’s Still at Microsoft, How is He Joining PowerPivotPro?

    imageBecause Microsoft is an amazing place, and Dany is an amazing person.  Dany is so valuable as a mentor – both for new hires and for veterans joining the Excel and BI “game” for the first time – that there’s a LOT of incentive to keep him on the engineering teams.  He makes everyone better.

    So when he asked about doing something different – mentoring/teaching people outside of Microsoft, his management team was very supportive.  He keeps his job at Microsoft and can utilize his veteran-level “time off” to share his talents with organizations who are starting to adopt the Power BI suite of tools.

    Win win for Microsoft, when you think about it.  Kudos to them for supporting it.

    Extends Our Geographical and Timezone Reach


    With Matt in Australia and Dany in Israel, the Sun Never Sets on PowerPivotPro!
    (Click Here for More on Matt)

    It’s been VERY hard for us to get over to Europe (for instance) to consult or teach classes – the travel time and expenses tend to be too high.  And even our remote (screenshare-based) consulting and assistance is often a bit awkward to schedule with the Europe/Africa/Mideast timezones.

    Dany fills that need in a big way – I mean, he taught me, and continues to teach Microsoft employees to this day.  How could we possibly get any luckier?

    You can contact Dany directly at his new email address:  DanyH@PowerPivotPro.com

    More to Come Once I (Rob) Get Caught Up

    I’m WAY overdue on sharing this announcement, and this is intended to be short (so that I actually get the post written and published rather than delaying further).

    So there’s more to be shared here for sure:

    1. I interviewed Dany recently on Skype, and need to transcribe that for posting here.
    2. I’ve got more to say about why Dany is PERFECT for this role, how he fits in with our personality and philosophy, etc.
    3. A rundown of Dany’s many skills.  He’s fluent in basically everything, from SQL to DAX to M, and that’s just the tech stuff.  His wisdom, experience, and people skills are just as important.
    4. Dany has guest-blogged for PowerPivotPro before – you can find some of his articles here.

    In the meantime, here’s his email address again:

    Contact Dany Via Email