Excel to Power BI Webinar Recording and a Call for Your Votes!

August 28, 2015

By Avi Singh [Twitter]

Friends, it was a joy again to host our free monthly webinar Excel to Power BI. As usual, we spent more than an hour on a lengthy Q&A session after the presentation. You can find the the recording at the end of this post.

Call for Your Power BI Votes!

In the webinar, we spoke about how there are two key scenarios for Excel Pros which are broken in Power BI.

1. Importing PowerPivot Data Model into Power BI Desktop

2. Connecting Excel to a Power BI Model

Now you can get a sense of how fast the Power BI team is working, since our webinar was on Aug 18th and on Aug 20th they announced that the latest Power BI Desktop update now supports #1 Smile

That aside, Power BI team is certainly listening to the users. But, Excel Pros need to step up and speak up to get their voices heard. The means to do that is by submitting and voting for Excel friendly Power BI ideas on their support site.

Ones that we would recommend voting are compiled at http://ppvt.pro/voteExcel

Since we submitted the “Connect Excel to Power BI Data Model…” idea (#2 above) it has been steadily climbing the charts. And is currently #1 under Hot Ideas on Power BI. That’s great, but keep your votes coming so that the Power BI team appropriately prioritizes this request.

Keep Voting!

Enjoy the webinar recording using links below.

Excel to Power BI: Webinar Recording Aug 18

Click above or Use the links below to jump to a specific topic:-
00:28 Start of Webinar
01:26 What is Power BI
03:17 Why use Power BI
08:34 How to use Power BI
12:55 Demo – Start
13:32 Demo – Get Data
16:31 Demo – Create Reports
17:22 Demo – Create Dashboards
20:23 Demo – Share Dashboard
27:37 Demo – Mobile App
29:17 Demo – Refresh
31:06 What’s Next


Free Webinar: Excel to Power BI, Tue Aug 18

August 14, 2015

By Avi Singh [Twitter]

Friends,
We’ll be hosting our monthly “Excel to Power BI” Webinar on Aug 18. Short notice, I know. But that means you have no excuse to dally, register today! Invite your colleagues, friends, even your mum. The webinar is really one of the most fun things I do. I always love talking about Power BI, PLUS I get to interact with all of you in the Q&A session (the Q&A session has gone on for an hour or more in our past webinars).

Click buttons below to find out more or to register.

Free Webinar
Tue Aug 18

PowerPivotPro Free Webinar Series

If you would like to stay notified of our future events, please subscribe to us via email.


Webinar Recording and Online Class Aug 3-4

July 27, 2015

By Avi Singh [Twitter]

Friends, it was a joy again to host our free webinar Excel to Power BI. As usual, we spent close to two hours with a lengthy Q&A session after the presentation. You can find the the recording at the end of this post.

Remember, the Power Pivot Data Model is the engine that powers all the pretty visualizations that you see in all Power BI Demos. With a poor data model all you get is poor results; garbage in => garbage out. So join us for our next online class on Aug 3-4 and jumpstart your journey to Power Pivot/Power BI awesomeness!


Power Pivot is the center of the Power BI universe

Online Class
Aug 3-4th

PowerPivotPro Online Class

Enjoy the webinar recording using links below.

Excel to Power BI: Webinar Recording

Read the rest of this entry »


Summer Fun at PowerPivotPro Free Webinar: Jul 22

July 13, 2015

By Avi Singh [Twitter]

It has been a busy summer. My first summer working from home. And with my kids summer vacation, it has been fun and challenging. Fun because I can join an impromptu water gun fight during midday (we have been under a heat wave in Northwest Seattle). Challenging because I am still travelling, training, proselytizing :-) and most of all trying to write a book. Getting to focus is hard and getting some quiet time impossible.

But I would put all that aside to make time for something that I have truly enjoyed – connecting with all of you via our webinars. The Q&A and discussion with you is my favorite part; which stretched the last webinar to 2 hours!

So folks, the next one is less than 10 days away on Jul 22nd. Click buttons below to find out more or to register. This one focuses on going from Excel to PowerBI.com.

Free Webinar
Jul 22

PowerPivotPro Free Webinar Series

If you would like to stay notified of our future events, please subscribe to us via email.


A Hello Heard Around the World

July 2, 2015

By Avi Singh [Twitter]

“Hello Everybody…”

With those words, Rob opened the very first blog post on this site back on Oct 26th, 2009. He spoke about his passion for PowerPivot and for the Excel community. He talked about bringing people together.

Today, I am happy to announce two big milestones in the PowerPivotPro journey: 800 Blog Posts and 2000+ YouTube Subscribers! Get a list of our hottest blog posts below.

PowerPivotPro has touched people across the world, through our blog, our youtube channel, our classes and free webinars. And we hope to do more. We hope to set the world of BI on fire and herald the disruptive new force that is Power BI.

We have not gotten here by ourselves though. And we wouldn’t get to our goal alone either. You have supported us, rallied around us, amplified our message and have given us strength. A BIG Thank You!

As a token of our gratitude, we will send a special offer to our subscribers on Tue July 7th. To receive this offer, all you have to do is make sure you are subscribed to our newsletter:

Subscribe to our Newsletter by July 7th to recieve a special thank you gift

What’s this Newsletter?

So far our blog has been our primary means of communication – for our blog articles, but also for our classes/free webinars/events etc.

You could subscribe to our blog via email, and many of you do. However many of you prefer to consume the blog via other methods – just the website or using an aggregator like feedly (my tool of choice). Also our cadence of two blog posts a week (Tuesday & Thursday) is more email than some folks want to receive.

Finally, we heard from many of you, that you would like to be notified via email of upcoming classes, free webinars and other events.

So now you have a choice, click links below to…

Subscribe to our Blog & Newsletter 2 Informative Blog Posts each week + Newsletter delivered to your email
Subscribe to our Newsletter alone 2-3 Emails max each month
regarding our upcoming classes, free webinars, events AND special offers for our favorite peeps in the world

A Story of 800 Blog Posts

800 Blog posts means that you would find pretty much everything under the sun, just use the search box.


Search Box is your friend! (top right corner)

For e.g. if you search for Cycle Plots, you would not only find my post from June 2015, but also Colin Banfield’s guest post from 2012! Read how we tackled it slightly differently. Similarly you can search for disconnected slicers, cube formulas and more.

Race to 1000 Page Views

Here are the fastest burning blog posts from this year (2015) – as measured by how quickly they reached 1000 views. This analysis is modeled after the Compare Product Performance Since Launch post, check out that post to see how you can run a similar analysis. Look through the top blogs list below and see if you had missed any, or go back and revisit an old favorite. A special thanks to our guest bloggers who appear in the list below. And our partners in crime Matt Allington and Dany Hoter who are regular posters and also appear below.

2015 Blog Post

Days to Reach 1K Views

Introducing Power Update!

2

Datazen = Beautiful Mobile Dashboards to Make Power Pivot Shine

2

Power BI, YouTube for Workbooks: More Affordable!

3

No More Apologies: Excel is the World’s Best Data Tool, Period.

3

Power Update Adds Email Notifications, Macro Support, and 100% Free Version

3

Harness Power Query to Gain Competitive Analysis Insights from LinkedIn (Gil Raviv)

7

Power Query for Excel: Combine multiple files of different file types (Miguel Escobar)

7

The Only “Requirements Doc” You Will Ever Need

8

Dear Accountants: PowerPivot is your friend! (Mike Griffin)

10

VLOOKUP better than Relationships? Hell must have frozen over

10

Our Road Ahead

10

Secret Pot Roast Recipe: Power Query vs. VBA Macros (Willem van Dijk)

11

The Diabolical Genius of “SWITCH TRUE”

16

Create a Custom Calendar in Power Query

22

Schrodinger’s Pivot, or Why CALCULATE() Should Be Your Favorite Function EVER.

22

Power Pivot to SSAS Tabular in less than 30 minutes

25

Some Good Power Pivot / Power BI Computers

28

Is Your DAX/PowerPivot/Power BI Computer FAST?

35

“Beta Test” the New Reference Card?

35

Power Pivot and Basketball Superstars: Many-to-Many and USERELATIONSHIP

38

Power Query Super Charges the Internet

41

How to Compare the Current Row to the Previous Row in Power Pivot

44

Dynamic Age Calculation Using Measures

57

Power Pivot to SSAS Tabular: Automatically Publish Excel Workbooks

90

Power BI as Google Docs Antidote

126


2-Day Class in Indianapolis, Aug 25-26

June 30, 2015

Post by Rob Collie

Power Pivot and Power BI Training

Two Days in Indy to Make You a (Happy) Data Rockstar

Hey folks, I will be teaching our first-ever Indianapolis class in August.  (Yes, we are relocating our secret rebel base, aka PowerPivotPro HQ, from Cleveland to Indy in July).

I LOVE teaching these classes.  There’s a certain vibe to them – of optimism, improvement, and friendly unity.  It’s FUN to meet other people similar to yourself, and to see that you are FAR from alone in the world.

But make no mistake – the name of the game is professional development, and dramatic expansion of your data superpowers.  The fact that it’s fun is merely an enhancer, a multiplier.  It helps hold your attention, and inspire/encourage you to greater achievement.

***Only 10 Spots Available, So Reserve Yours Quickly!***

Since this is a new town, we decided to start kinda small, and only booked a 10-student classroom.

But in the past, Indiana students outnumbered Ohio students even when we held the class in Cleveland (!), so we might fill up quickly.  We’ll find out soon enough.

Reserve Your Spot, Enroll Today

A Previous Class – Many Of These Folks Have Gone On to Do AMAZING Things.

Optional Gathering/Dinner on “Middle” Night

image

Some Folks from last August’s Class Throw 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 usually end up with about 75% attendance, and attendees often describe it as one of their favorite all-time experiences.  Because, you know, how often do you get to go out socially with other data geeks?

Included:  Thumb Drive, Lunch Both Days, and 50% Off Online University

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

thumbdrive

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.

Click Here for More Info and Registration


Time to revisit the Power Pivot Import Wizard

June 11, 2015

by Matt Allington

I have learnt a lot over the last year working full time as a Power Pivot professional.  And some of the things that I dismissed as “not useful” very early on have turned out to be very useful indeed.  The second option in the Table Import Wizard is a case in point.

option 2

Very early on I dismissed this as being “not useful” for Excel users of Power Pivot (although I could clearly see the benefit for SQL professionals that could already write SQL code).  But I have to say I was wrong about this and I think there is a lot of value in this second option for Excel users.

I was re-reading the excellent book by The Italians over the weekend.  I find that reading quality books for a second and third time is a worthwhile and rewarding pursuit.  Often things that didn’t mean a lot the first time you read will have a new meaning and relevance the second or third time around.  Anyway when I got to the part about importing data from a database, there in front of me was reference to the “Design Button” which appears on the “less preferred option 2″ import screen.

designbutton

I have been using this less preferred option 2 screen for some time now (read about other tricks I use here) but I have never noticed this button before.

So how do I use this button?

This button is very useful when you want to import data from a database but you don’t have access to an IT buddy to help you out by writing the SQL code you need. Now it is not the greatest user interface tool that you will ever encounter, but it is very do-able for the average Excel user. 

Let’s consider the following scenario using Adventure Works reporting DB for SQL Server.  In this example, I don’t want to bring in the full low level transactional data from the source database but instead I want to bring in a summary table for a defined period of time.

eg  Give me a table that summarises sales by day and by territory, and also how many invoices there were for the year (say 2003).  So I really want a table something like this to put into Power Pivot.

summary

Here are the wizard driven steps to complete this task.

Go through the normal “Import from Database” steps as you normally would for Power Pivot.  Select your server name and database name in the wizard.

image

Select the previously unloved option 2

option 2

Give your query a name (this will be your table name inside Power Pivot) and then click the Design button.

image

Expand the table navigation pane on the left and select the columns of data needed.  In this case we want order date, Territory Key, Order Number and Extended Amount.

image

In the selected fields section (top right), click the “Group and Aggregate” button and then select the “group by” actions needed for each field.  We want to group by order date and territory key, then do a distinct count on order number and sum up the extended amount.  These are not difficult concepts for Excel users to grasp even if you don’t know database design or the SQL language.  “Group By” simply means “give me a sub total at this level”.

image

Finally you apply a couple of filters to limit the results to the calendar year 2003.

image

After you click OK, the Design Wizard returns the appropriately written SQL code that will be used to fetch the data you need from the database.

image

And this SQL code is not hard to read or understand.  Once again, well within reach for the average Excel user that is learning Power Pivot.  In fact you can even change the SQL code here (to change the returned column names as an example).

Change this part

SELECT
fctSales.OrderDate
,fctSales.SalesTerritoryKey
,COUNT(DISTINCT fctSales.SalesOrderNumber) AS [Count Distinct_SalesOrderNumber]
,SUM(fctSales.ExtendedAmount) AS Sum_ExtendedAmount

to this

SELECT
fctSales.OrderDate
,fctSales.SalesTerritoryKey
,COUNT(DISTINCT fctSales.SalesOrderNumber) AS [Total Orders]
,SUM(fctSales.ExtendedAmount) AS [Total Sales]

 

Then click Finish and finalise the import of your summary table.

From here you can go ahead and bring in more tables of data, or start working on your data model as required.

Personally I think any Excel user that is regularly importing data from a database should invest a small amount of time learning how to read and write the SQL language.  12 months ago I classed my SQL skill as “knows what it looks like, can read it, but not good at writing it”. 12 months later having used SQL quite a bit for Power Pivot data imports, I class myself as “competent”.  So if I can do it, you can do it too. And UI tools like the one above can help you get started, and you will be writing your own SQL code without help before you know it.  Then there will be no stopping you.

Matt Allington is full time Self Service BI professional based in Sydney Australia


Power BI Jun 2nd Webinar Recording Available

June 5, 2015

Inf

By Avi Singh [Twitter]

Thanks to all, who attended our Power BI Webinar on Jun 2nd. We had 400+ (well, 438 to be precise) attend, what ended up being a 2 hour webinar with all the questions being asked.

Be Notified of Future Webinars…

Click here to subscribe to our Newsletter, to be notified of future Webinars/Events.

Webinar Infographic

Here are some fun facts…

Webinar Recording: Build BI Solutions Using Power BI

Read the rest of this entry »


Last Chance to register for Power BI Webinar, Tue Jun 2nd

June 1, 2015

Are you…

  • Confused by all the terms and acronyms around Power BI?
  • Wondering how all the pieces fit together?
  • Interested in leveraging Power BI for your team/org but don’t know where to start?
  • Would like to know what to expect on this Power BI journey?
  • Find yourself or others around you asking “Do I need Power Pivot or Power Query?”
    (Hint: Both! But one more than the other)
  • Find yourself struggling to find the odd one out in this list? Power Pivot, Power View, Power Point, Power Query, Power Map

    Well, if you’re struggling with the last one, maybe you are beyond help. But for all the other questions, this is your last chance to register for our Power BI Webinar tomorrow. Our webinar’s purpose is to shine a guiding light on your Power BI path and get you started on this transformational journey. Click here to register or follow links below.

    Free Webinar
    Jun 2

    PowerPivotPro Free Webinar Series

    Power On!
    Avi Singh [Twitter]


  • 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]

    Friends,

    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