PowerPivotPro

PowerPivotPro is Coming to Boston

May 15 - 17, 2018

AVAILABLE CLASSES

**Use the discount code “3ORMORE” when signing up 3 or more people.

MAY 15 - 16

Foundations: Power Pivot & Power BI

Super charge your analytics and reporting skills with Microsoft’s dynamic duo. Designed to handle huge volumes of data, these tools will transform the way you work. Two Days in our class and you are EMPOWERED!

Overview:

  • Not just the “hard” skills, but also the “soft” stuff (when and why to use it, how to get the best results for your organization, etc.)
  • Learn Microsoft’s secret weapon behind Power Pivot & Power BI: DAX
  • You don’t need to be an IT professional – most of our students come from an Excel background
Boston Public Training Classes - PowerPivotPro
Boston Public Training Classes - PowerPivotPro

MAY 15 - 16

Level Up Series: Advanced DAX

Foundations taught us how to remove repetitive, manual work and make impactful insights. Advanced DAX is about making it rain money by better informing decisions!

Overview:

  • Taught completely in Power BI Desktop
  • If Foundations is a 101 course, hands-on work experience with DAX is 201, and Advanced DAX is 301.
  • This class will teach you how DAX really works, how to build complex reports that are still digestible, and how to use that information to drive your business.

MAY 17

Level Up Series: Power Query for Excel & Power BI

Copy-paste? Dragging formulas down? SAME THING EVERY WEEK?… No more. Teach your computer how to build your reports for you. Set and forget!

Overview:

  • This class will teach you how to connect to all of your data (no matter where it lives), shape it so DAX can run automagically, and have your computer remember the steps so you never have to do it again.
  • You don’t need to be an IT professional – most of our students come from an Excel background
  • Taught simultaneously in Excel and Power BI
Boston Public Training Classes - PowerPivotPro
PowerPivotPro Logo

Microsoft Excel Power Query for self-service BI professionals.

Counting Overlapping/Shared Twitter, Facebook, Instagram, etc. Followers

Post by Rob Collie

From Last Week’s Client Work

Last week a client asked us to solve a somewhat unusual problem:  given any two lists of Twitter followers, tell us how many followers “overlap” between the two lists.

Two Lists of Twitter Followers:  How Do We Find the Overlap Using Power Pivot / Power BI / DAX?

How Many of Han Solo’s Followers Also Follow Leia Organa, and Vice Versa?
(Randomly-generated Twitter handles are funny.  I particularly like “@Gommo” and “@Xxfok”)

Loading the Data:  Using Power Query

Let’s use Power Query to perform the import this time, both because we’re using PQ a lot more around here now that we have Power Update, and because we’re gonna need PQ for the more complex steps later.

Note that all of the steps below are performed using Excel 2013.  (I find Power Query to be a bit too clumsy in Excel 2010.)

Power Query, aka Power BI Data Import

Importing from a Table Using Power Query:  Step 1
(Unchecked “has headers” because of the “Han Solo’s Followers” Row)

Read the Rest

Harness Power Query to Gain Competitive Analysis Insights from LinkedIn

Guest Post by Gil Raviv

Intro: Many experts had proclaimed LongForm Journalism was headed towards extinction in the digital age. In fact it has found a new resurgence and a new audience in the recent years. Thanks to that we can still get articles like This Old Man (featured on NPR’s best Longform Journalism list). This blog post is in the same spirit. This is not a bite-sized learn a cool new trick. We do have tons of those on our site and they’re great. But sometimes you want to sit-down and eat a seven-course meal. Enjoy the feast! Take it away Gil…

In this blog post we will show you how to use Power Query in Excel to import data from LinkedIn and gain amazing competitive analysis insights based on company search. To get your attention right at the start, we will conduct this tutorial and analyze a domain that we all know so well and love – Power Pivot. We will analyze companies who specialize in Power Pivot.

We will show you step by step how to utilize Power Query to extract information from LinkedIn including company size, founding year, location, specialties, and more.

Build your own customized Competitive Analysis Dashboard

When we are done, you can download the workbook, read below how to get LinkedIn access token in this tutorial (Step 1-6), and start using the workbook as your dashboard for competitive analysis. You can use its parameterized queries to search for companies in any domain, refresh the workbook and get a tailor-made dashboard for the competitive posture of your interest.

Before we start, here are few screenshots of what you can get at the end.

Screenshot below shows the world distribution of the 70 companies who specialize in PowerPivot (Created with Power Query and Power Map).
image

Next screenshot shows the distribution of companies by founding year. It’s interesting to see a gradual incline of younger companies who specialize in PowerPivot from 2009 to 2013, and to see a decline in 2014. It seems that last year fewer companies were founded with PowerPivot as a specialty.
image

Next screenshot shows company distribution by Specialty. You can see the most common specialties for companies who specialize in PowerPivot (e.g. Business Intelligence, SQL Server and Excel).
image

I am sure that by now, we got your attention 🙂

Read the Rest

Secret Pot Roast Recipe: Power Query vs. VBA Macros

Guest Post by Willem van Dijk

Intro by Avi: I have never met Willem van Dijk in person, but in my mind he has a persona of a scrappy boxer/standup comedian. A priceless combination. He would have you rolling on the floor no matter what. He brings home the reality of a business user trying to get some business intelligence. Your day job is something else – Finance, Marketing, Accounts, Sales, Product planning etc. But you do what it takes to get the data and analysis that you need. Here’s to the fighting spirit! Take it away Willem…

I love to hate secrets just about as much as I hate to love secrets…

Why? Because knowing a secret is great for the ego, yet too much ego is not great for the soul.
Which is why I love reading posts on PowerPivotPro as it is all about sharing secrets which, in essence, are no longer such.
Ironically, at times what one believes to be a secret often results to be common-knowledge, or worse, common-sense.

In my inaugural post I promised that I would share a Pot-roast recipe (of the secret kind) so here we go…

TopSecretppp

Read the Rest