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.

Tales from the Trenches: My personal experience with Power Update (by Tim Rodman)

Guest Post by Tim Rodman, currently blogging about reporting in Acumatica ERP @ www.AcumaticaReports.com

***Update #1:  a Free Version of Power Update is now available.  More info here.

***Update #2:  There is now a forum for Power Update questions, located here.

Intro from Rob: I’m what you might call a “gift horse optimist” – strongly positive outlook, but when the hoped-for thing finally arrives, I find myself closely inspecting it, testing it, before I trust it enough to advocate it to others.  I went through this same process with Power Pivot itself – I “saw” its gamechanging power in 2010, but it was a full eighteen months before I finally dropped all disclaimers and just started calling it far better – period – than anything we’ve had before.”

Similarly, I’ve long known that Power Update would be a MAJOR win for us in the Power Pivot and Power BI communities.  But I am willing to advocate it now only because I’ve watched others – like Scott, and Tim below – use it successfully, in production environments, in recent months.  (Also see my post last week “introducing” Power Update in case you missed it).

Take it away, Tim…

I first found out about Power Update two months ago via a LinkedIn post by Christian Floyd.

It took me a while to realize that he wasn’t talking about a theoretical future idea, but an actual product, something that exists today. Click the picture below to see the entirety of my foolishness. It wasn’t until I talked to him directly that I realized what Power Update really was and I was immediately interested.

image

He got me a beta version of Power Update and I began testing it at the company I work for: a manufacturing company in Cleveland, OH called The Robbins Company.

Our Background

We started using Power Pivot at The Robbins Company back in 2013 and I wrote about our experience on this blog (click here).

Read the Rest

Introducing Power Update!

Post by Rob Collie

***Update:  check out Scott Senkeresty’s review of Power Update over on Tiny Lizard.

***Update #2:  a Free Version of Power Update is now available.  More info here.

***Update #3:  There is now a forum for Power Update questions, located here.

Power Update: Refresh any Power Pivot / Power BI Workbook, from Any Data Souce, and Publish to Any Location (SharePoint or Otherwise)

A brand-new software utility designed from the ground up as
a “Companion” to  Power Pivot, Power Query, and the entire Power BI stack.

Definitely Click on the Image for Larger Version – Surprises Lurk Therein

Do Any of These Sound Familiar?

Common Problems with Power Pivot and Power BI Scheduled Refresh

Power Update Helps With ALL of These (And a Few More, Too)

“What IS It?”

OK, a few things:

Read the Rest

Power Query for Excel: Combine multiple files of different file types

Guest Post by Miguel Escobar Twitter | Youtube | Blog | Website

Power Query Magic: The Ultimate and easiest way to consolidate multiple tables, sheets, text and/or csv files

Power Query Magic:  The Ultimate and easiest way to consolidate multiple tables, sheets, text and/or csv files
(Click for Full-Size Version)

At some point in the life of an Excel user, we have all faced a similar dillemma. How can I combine multiple sheets, tables, csv or txt files? (can I combine them all together??)

How we used to solve this scenario

Back in the day (before Power Query) we actually had some ways to do so but they were not so user-friendly and they relied heavily on coding or some tedious way of doing it. The most common ways were:

  1. Using SQL Statements to join multiple files
  2. Creating a VBA code that will do the job for me
  3. Going with the tedious way of combining the files manually (perhaps with Excel or Access)

But now we have an easier and optimized way of doing this..let’s find out how

Read the Rest

Power Query Adds SalesForce Connectivity: Totally Awesome, But Trouble Looms Ahead

Post by Rob Collie

SalesForce Data Into Power Pivot / Power BI? YES!

This is what it looks like when Microsoft Does Something Epically Awesome…
…But Dark Clouds Loom – Read on to Learn Why

We LOVE This!

Seriously, we do.  It’s AMAZING.  Multiple of our clients are going to jump all over this.  It’s going to change their culture – AGAIN.

If Power Query can connect to it, that lets you pull SalesForce data directly into Power Pivot.  No more export, save, import/paste/etc.

SalesForce Data Into Power Pivot / Power View? YES!

Power Query Can Import SalesForce Data Directly Into Power Pivot, Which Means You Can Then Visualize in Power View, For Instance

So, the primary point of today’s post is to make you aware of this new capability, and tell you how to get it.

Read the Rest

“I Modified an Existing Table in Power Query and Now it Won’t Refresh”– A Fix

Guest Post by Ted Eichinger

Note, this fix to re-establish a broken connection is performed using Excel 2010

It’s the same old story, I mashed and twisted some data through Power Query, pulled it through Power Pivot, spent hours creating calculated columns and measures, made a really nice Pivot Table with conditional formatting and all the bells and whistles.  Then I show it to the Business Manager that requested it, and he wants me to add a field that I didn’t pull in through Power Query.  I’m going to have to change my Power Query which is going to break my Power Pivot connection, and then I’ll be spending hours to rebuild my model from scratch!

This happens to me more that I’d like.  I’ve searched the far reaches of Google, only to find a lot of threads asking about how to fix, but I’ve never seen an answer.  Most threads end with no,  you just can’t do that.  Just playing around on a hunch, I figured out a way to fix it without having to rebuild the model!!!  Some already know the “fix”, for those that don’t , I’ll walk through the method that’s been working for me.

If you want to follow along download the file below, I’ve used some public data on camera lenses so anyone can refresh the data model (use Anonymous access if prompted), or follow along just using the pictures.
Yes You Can Example.xlsx

In this example a Business Manager wants me to add the field, “Closest Focus” to the Power Pivot table.

Power Pivot Rejected by Business Manager

This is the Power Pivot table that requires an additional field

This should be easy, just open the Power Query (in the example file) named, “Macro photography lenses”, and delete the “RemovedColumns” step and the “Closest Focus” column should now be back.

 Power Query reveal column

“Closest Focus”, has now been added.

Save that Power Query.  Open the Power Pivot window, there is only one table, let’s refresh it.

Power Pivot Error After Editing Power Query

The dreaded Power Pivot error!

This will always happen anytime we make any kind of change to a Power Query that is connected to a Power Pivot table.  If you’re working with Excel Power BI tools you’ll eventually see this error.  The error detail reads:

The operation failed because the source database does not exist, the source table does not exist, or because you do not have access to the data source.

More Details:

OLE DB or ODBC error: The query ‘Macro photography lenses’ or one of its inputs was modified in Power Query after this connection was added. Please remove and re-add the connection. This can be done by disabling and re-enabling download of ‘Macro photography lenses’ in Power Query..

The FIX!!

Read the Rest

Forecasting in Power View and Power BI

Guest Post by Avichal Singh

Intro from Rob:  Never fear, last week’s series is still slated for completion, and in a special way.  Watch this space on Thursday for some fireworks.  For now, please enjoy Avi’s thoughts on the new forecasting component of Power View / Power BI.

PASS Business Analytics conference saw the announcement of a pretty cool Power View feature: Forecasting. I felt lucky to have been there and also to have had the opportunity to attend both of Rob Collie’s sessions (Data Revolution, Industrial Strength Excel). The Data Revolution session, I must say, was unlike anything I expected. No DAX formulas, no bullet points; just a path to data nirvana Smile

The Power View forecasting feature was cool enough that I just had to play with it! I wanted to try it out with a few real world data sets. I ended up using Climate Data and Stock Market performance.

– First a quick look at the Power View forecasting functionality
– Then I show you how I built the files using Power Query (The more I use that tool the more I like it)

You can find the link to the finished Excel file here. You can also watch me walk through the whole process in the video below:

[youtube http://www.youtube.com/watch?v=-rqQBJFMofw&hl=en&hd=1]
Video Walkthrough: Forecasting in Power View and Power BI

Power View Forecasting in a Nutshell

In the ‘cloud first’ spirit that Microsoft has been following, the forecasting feature is only available in the online Power BI site (See microsoft.com/powerbi for more and to sign up for a free trial). To enable the forecasting feature, after opening your file on the Power BI site, you need to switch to the HTML5 mode by clicking on the icon at bottom right.

clip_image001 Click on this icon to enable the HTML5 mode with forecasting functionality

Power View Forecasting in a Nutshell

Read the Rest

The 3 Big Lies of Data. (And Power Pivot vs. Power View, Power Query, Q&A, etc.)

How Power Pivot, Power View, and the Other Power BI Tools Relate to Each Other

Power Pivot is the Engine that Turns Data Into Information!
But We Can’t Understand This Properly Without Examining the Three Big Lies of Data

Goal:  Answer Four Frequently-Asked Questions

So many things to say this week. Let’s jump in.  Here are the questions I ultimately aim to answer, which are questions I get basically everywhere I go:

  1. How do all of the Power BI Components relate to each other?  Power Pivot, Power Query, Power View, Power Map, Q and A, etc. = Power Confusion for some folks.  I get it.
  2. Has Power Pivot become less important, now that we have all of these other new “Power *” tools?
  3. Which tool should I learn first in the Power BI family?
  4. Should I consider abandoning this stuff altogether in favor of <hot new technology X>?  Tableau, Hadoop, R, etc.

In order to answer these, first we must confront some insidious lies that we are told every day.

Examining:  The Three Big Lies of Data

We want data tools vendors to lie to usThe world of data, today, is clouded by Three Big Lies.  These lies originate with all of the tools vendors – Oracle, IBM, Tableau, etc., and yes, Microsoft too is very much playing along.

Even though the Vendors are the Purveyors of these lies, they are NOT “at fault” for them.  Because the world actually WANTS to be told these lies.  BADLY wants to be told them, in fact.  And because the audience is so receptive to these lies, the vendors naturally learn to tell them, and tell them well.

Vendors who DON’T learn to tell these lies?  Well, those vendors don’t win many customers.  And then those vendors disappear.

So while the lies COME from the vendors, the PROBLEM, really, is with US – the people who BUY the tools.

Read the Rest

Get Your Name Printed in Power Pivot Alchemy :)

 

***UPDATE:  Pre-order window moved to Tuesday April 1st, and moved from Amazon to MrExcel.com:

0) Add the pre-order window to your calendar

1a) MrExcel.com Physical Book Page (USA Orders Only!)

1b) MrExcel.com eBook Page (All Countries)

***BONUS:  In addition to getting your name printed in the book, ALL pre-orders from MrExcel.com will include IMMEDIATE access to the “rough cuts” version of Alchemy in PDF form.  Think of this as the 99% complete version of the book, a “final beta” of sorts.  You can start reading next week, and then receive the final version when it’s ready in a few weeks.  (Immediate access to the PDF is included with pre-orders of the physical book OR eBook).

image

image

About 160 People Got Their Names Printed in the First Book, and Seemed to Really Enjoy It.
Time to Do That Again for My Long-Delayed New Book, Alchemy.

The long tug of war draws to a close…

Yes folks, it’s basically done.  For over a year now, Bill and I have taken turns playing the roles of “Busy Guy Who Keeps Putting it Off” and “Impatient Guy Who Wonders Why the Other Guy Keeps Dragging His Feet.”

For the record, it looks like the game is ending with me holding the hot potato.  Bill will forever remind me that I was the last hold up, I know this.

Order Tuesday April 1st Between
12 and 1 PM US Eastern Time

Pre-order the book on MrExcel.com during that 1-hour window and we will include your name in the book before it goes to the printer!  (Yes we still have a narrow window for changes).

0) Add the pre-order window to your calendar

1a) MrExcel.com Physical Book Page (USA Orders Only!)

1b) MrExcel.com eBook Page (All Countries)

(No Need to Send Screenshots Since We’ll Have Your Name on the Order)

Read the Rest