Busy Day Today!
What can I say folks. The Power Pivot “biz” continues to gain steam (one indicator: last week recorded the highest traffic ever to this site) and that means busy days here at PowerPivotPro HQ.
So I will keep this brief. First a quick note about the University, then read on for Power Query!
PowerPivotPro University (aka “Advanced Power Pivot” on Chandoo.org) Opens Monday
Highlights, again in brevity style:
- Like Power Pivot itself, it is accessible to beginners but slowly builds you up into a modeling ninja.
- Features the same highly visual and entertaining style that you’ve come to expect from the book and here on this site. NOT dry or technical – VERY human.
- Represents six full months of work by me – it’s like I’ve offered my entire brain for purchase.
- The first wave of students seems to have enjoyed it:
Click image for more feedback from the first wave of University Students
Ode to Power Query – Topic of MANY Future Posts.
A VERY Common Situation – You are Given a Data Table but No Lookup Tables
This happens all the time. In fact it happened to me last night. I was looking at “wave one” students’ progression through the University lessons, and the data I have is one big fat CSV exported from the LMS software.
Web “data sources” are just like that. They say, “here you go, here’s your big wide CSV file. Now we can wash our hands of the whole analysis and reporting business and dump it on you.”
And actually, I LIKE it like that. I don’t want my various web applications fumbling around and giving me terrible analytics. Let ME do it.
But do they EVER give me the lookup tables? Heck no. List of course elements? List of Students? Nope. Just one big CSV Frankenfile.
Lookup tables offer SO many benefits I don’t have room to list them here. (Covered extensively in the University of course, cough cough).
But making Lookup tables manually… sucks. Who wants to go through that process over and over and over? Not me.
Power Query to the rescue!
Remove Duplicates in Power Query – Lets You Create Lookup Tables from Data Tables
(And the Lookup Table STAYS UP TO DATE On Every Refresh!)
More on this later, but for now, go download Power Query…