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

“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