Update on Table Queries

March 17, 2014

Guest post by Scott Senkeresty

After completing this post on table queries, I celebrated by curling up with Microsoft Excel 2013: Building Data Models with PowerPivot.  It’s a great book by Marco Russo and Alberto Ferrari.  (I’ve read Rob’s book now multiple times, and I was even the tech editor on it, so it was time to step up and read the other leading book).

When I got to chapter 14 in the Italians’ book, two things happened:

  1. I saw page after page that looked incredibly similar to my blog post… making me feel like a complete imposter.
  2. I saw “the next level”.

The Italians don’t just bring data into a table via DAX.  They further manipulate the data (with standard techniques excel pros know and love), and then, link this newly created table back to their power pivot model for further analysis with DAX!  And it all responds to Refresh.   Totally.  Awesome.


TopN as viewed by DAX Table Queries

March 13, 2014

 
Guest post by Scott Senkeresty

image

Get it?  “Median?”  SO Funny!

Intro

Rob is on-site with a client this week, so the reins, mic, baton or other appropriate metaphor gets handed to me today.  We get to today’s topic by means of a discussion on calculating a median in DAX:

Scott:  That sounds easy.  Just use TopN to grab half the numbers in ascending order, then another TopN against descending values to grab the final value(s)  (Glossing over odd vs even number of data rows)
Rob: What are you going to do about ties?
Scott:  <Blank Stare>

It turns out that calculating a median in DAX is pretty tricky.  Rob contends that sneaky street fighting tactics are required to deal with ties…where in my heart of hearts, I believe an elegant solution exists.  Hopefully we can get to the bottom of that in a future blog post.

Read the rest of this entry »


Automatically create data dictionary for your Power Pivot model

March 11, 2014

By Avichal Singh www.linkedin.com/in/avichalsingh

There are many reasons why you would want to document your Excel Power Pivot or SSAS Tabular model in an effective manner:- to better understand your model yourself as it moves from simplistic to advanced, facilitate the use of your model by other users, ability to transition model development to another person and a few more.

Diagram View is good, but you can do better Smile. You could automatically build a data dictionary – listing all the Dimensions, Attributes, Measures and their Description – and maintain it with almost no effort at all. Watch the video and read the rest of the article to find out how.

Video walkthrough showing how to automatically create data dictionary for your Power Pivot model

2-Day Public Class in Cleveland April 9-10

March 6, 2014

 
If you’re in the Ohio/Midwest area, I would love to meet you in person and help you on your Journey Through the Land of Data.

Only 18 seats in the class, so it will be “intimate.”  Lots of interaction, fun, and obviously…  mind-blowingly powerful techniques.  You know the drill – act fast and reserve your seat today, etc etc. Smile

Syllabus, pricing, and registration information here

(Apologies, no “real” post today – but only because I am so “slammed” with real work.  There are guest posts in the pipeline where if I just could have gotten free for a few minutes…)


Creating dynamic lookup-tables with unique values using Power Query instead of a database

March 4, 2014

 

Guest post by Lars Schreiber

Hello PowerPivot Community,

I’m quite sure most of you know a scenario comparable to the one in the following figure:

 

Bringing two fact-tables with different date-columns together using a 'unique-months'-dimension

Two scenarios (in two tables) – in this case actuals and budget – have to be put together to do some math on them. The only problem you have is the different level of planning regarding the time dimension. While the actual figures are on daily basis, the budget was planned on monthly basis. As you could learn on this website many times before (e.g. here) you need another lookup-table with a unique list of months to bring both tables (actual & budget) together. And this is where Power Query can help you a lot.

 

Read the rest of this entry »


The Ultimate Date Table–Revisited

February 27, 2014

 
Guest post by Dominik Petri, who I am stoked to say is translating the book into German!  Today he shares a cool “hybrid” technique that blends two of my favorites things – Data Market and Power Query!

Take it away, Dominik…

Selecting a DateStream calendar from Power Query Navigator taskpane

 

As you might already know from Rob’s “The Ultimate Date Table” post, you need a separate calendar table to unleash the power of Power Pivot’s time intelligence functions. You want one for free? Fully customizable? Running up to a variable end date? Updated automatically every time you open your workbook? The time has come…

Get a great calendar table – for free

Boyan Penev has put together various calendar tables that you can download from Azure DataMarket directly into Power Pivot – for free. Again, see Rob’s post for details. So far, so good. But it is a pain to filter the data range you want to import. But there is Power Query!

Read the rest of this entry »


Speed: Another Reason to “Trim” Calendar Tables

February 25, 2014

image

An 11,000-Row Calendar Table Spanning from 2000 to 2030:
Most of the Time This is Harmless Overkill

A 60x Speed Improvement From a Most Ordinary Place

I’ve been doing some work lately for a client who really pushes the DAX envelope.  One of the top-three models I’ve ever worked on in terms of complexity, no doubt.  And really, my role is just to help fine-tune it and add a few bells and whistles.  They built this sucker themselves and I am way impressed.

Crazy stuff.  Formulas that use outlier dates from one Data table (“fact” table) to then subsequently filter another Data table (via its related Calendar table), but then wrap that up inside a MAXX inside a SUMX…  and it all makes perfect business sense.  It’s magic.

But speed ain’t its strong suit.  We tried all the usual tricks – “de-importing” unneeded columns, replacing calculated columns with imported versions, etc.

And it was still way too slow.  Then we tried something even simpler, and things got 60x faster.

Read the rest of this entry »


Closing Midnight Tonight (US Pacific Time): PowerPivotPro University / Chandoo Bundles

February 21, 2014

Final Reminder!

PowerPivotPro University, aka Advanced Power Pivot on Chandoo's site.

Click Image to Register

And again, here’s the Q&A posted last week.  Happy learning!


What is “Modern Excel?”

February 20, 2014

Modern Excel:  Is it Just Power Pivot?

So we started this User Group…

…and, you know, it’s a real thing.  We’ve got active chapters now in five US cities, a sense of “blueprint” for spreading it rapidly to many more cities worldwide, and nearly 700 members in the LinkedIn group.  (What, you haven’t joined yet?  Darth Formulus finds your lack of faith… disturbing.  A problem easily remedied yes?)

But wouldn’t you know it.  All you analytical types started asking “hey, what IS Modern Excel, exactly?  What is considered ‘fair game’ to discuss at meetings?”  “And what the heck is the point of this LinkedIn group – you’ve made virtually no announcements whatsoever.”

Ambiguity?  That will never satisfy humans with Compulsive Data Crunching Disease.  And I wouldn’t have it any other way.  I thrive on this sort of thing.

As an aside, constantly being forced to be precise (by my peers) at Microsoft is one of the most formative and useful experiences of my life to date.  My first 22 years on the planet had been fueled strictly by Instinctive Conviction – which in hindsight is merely another form of “coasting.”  But I’ve since come to believe that if you cannot communicate your opinions effectively, not only will their impact be forever limited, but actually, even YOU don’t understand what you THINK you do.  Sloppy communication often masks sloppy thinking.  22-year old Rob arrived in Redmond with an ample supply of both.  It was as much fun as a multi-year waterboarding, but they straightened me out.

Excel 2007 Desktop is a BIG Dividing Line

Read the rest of this entry »


Three posts last night!

February 18, 2014

 
For those of you who don’t like to scroll vertically, here’s a Table of Contents of everything posted in the last 24 hours.  Click each handy hyperlink for auto-navigation purposes:

  1. I’m Speaking Next Week in the Twin Cities – the MSBI User Group.  Topic “Fact or Fiction:  The ‘Free Lunch’ Promise of Power Pivot.”
  2. Bundled Offering of PowerPivotPro University and Chandoo courses closes this Friday – and make sure you check out the cool “Super Mario Power Pivot” images sent to me by a student.  He described himself as “leveling up” with my training and I’m going to steal that phrase for perpetual use.
  3. Inaugural Meeting of the Nashville Modern Excel User Group is THIS THURSDAY (Feb 20) – Be there, Music City. :)

Nashville Modern Excel UG Meeting–THIS Thursday!

February 18, 2014

 
image

All Right Music City!  Let’s Go!
(Click Image to Register)

Please join us for the inaugural meeting of the Nashville Modern Excel User Group.  Agenda:

1. Exclusive video intro from Rob Collie (former Microsoft engineering leader and founder of PowerPivotPro) – Why Now, What IS “New Excel,” the MASSIVE Opportunities Awaiting, and a Map to the Road Ahead.

2. Meet and greet – meet your fellow Excel professionals from the Nashville area.  You will be surprised at how “Not Alone” you are.

3. Discussion led by Will Harvey – who drove 4 hours to the Atlanta meeting last year (!) – covering things like:  Where are you on the roadmap?  What are the challenges to making progress?  What would you like to see from the group?

Revitalize your career.  Win with data.


“Level Up” Your Data Powers with PowerPivotPro University and Chandoo Course Bundles :)

February 17, 2014

 
Power Pivot Pokemon Last Week, Power Pivot Mario This Week

An Enthusiastic PowerPivotPro University Student Sent Me These Over the Weekend Smile

Signup Closes Friday:  Bundle PowerPivotPro Univ. w/ Chandoo Courses

Just a reminder that the joint discount/bundle offering with Chandoo closes this Friday:

Read the rest of this entry »