PowerPivot Alchemy
by
Rob Collie & Bill Jelen
Holy Macro! Books,
PO Box 82 Uniontown, OH 44685, USA
PowerPivot Alchemy
2014 Robert Collie and Tickling Keys, Inc.
All rights reserved. No part of this book may be reproduced or transmitted in any form or by any means, electronic or mechanical, including photocopying, recording, or by any information or storage retrieval system without permission from the publisher. Every effort has been made to make this book as complete and accurate as possible, but no warranty or fitness is implied. The information is provided on an as is basis. The authors and the publisher shall have neither liability nor responsibility to any person or entity with respect to any loss or damages arising from the information contained in this book.
Author: Rob Collie & Bill Jelen
Layout: Tyler Nash
Cover Design: Jocelyn Hellyer
Indexing: Nellie J. Liwam
Published by: Holy Macro! Books, PO Box 82, Uniontown OH 44685 USA
Distributed by: Independent Publishers Group, Chicago, IL
First Printing: May 2014. Printed in USA
ISBN: 978-1-61547-021-1 (Print), 978-1-61547-214-7 (PDF), 978-1-61547-334-2 (ePub), 978-1-61547-114-0 (mobi)
Library of Congress Number: 2013907451
Contents
Dedications v
Acknowledgements vii
Foreword ix
Introduction xi
Chapter 1: Dashboard Tricks and Visualization Techniques 1
Adding a Last Refreshed Date Readout 1
Normalizing Your Measures to First/Average/Max Values for Charts 4
Bubbling Up Exceptions with Sarah Problem 10
Ranks and Exceptions That Bubble Up to Subtotals 12
Chapter 2: Slicers: The Gateway to Interactivity 23
Initializing Slicers That Contain Too Many Values to Scroll 26
Wingdings and Other Symbolic Fonts in Slicers 28
Using Macros to Change Slicer Fonts 40
A Simple Trick for Combatting Stale Slicers 42
User-Friendly Report Sorting with Slicers 46
Adding State Alpha Sorting to the Sort-by-Slicer Trick 50
Conditional Formatting Controlled via Slicers 60
Catching Slicer Selections in a Formula 69
Chapter 3: Conquering Common Calculated Column Conundrums 75
An Excel Pros Primer on Calculated Columns 75
Subtotaling Calc Columns and the EARLIER() Function 76
Referencing the Previous Row and Similar Calcs 79
Referencing Rows Within Range of the Current Row 80
Totaling Data Table Values in Lookup Tables 90
CONTAINSX: Finding Matching Values in Two Tables 92
CONTAINSX Revisited: What Is the Match? 94
Common Calendar Conundrums 97
Custom Calendar Conundrums 100
Chapter 4: Modeling and Portable Formulas 103
Integrating Data of Different Grains 109
A Mystifying and Awesome Solution for the Many-to-Many Problem 117
Calculating Same-Store Sales in Power Pivot 120
Same-Store Sales Continued: Using Store Open/Close Dates 123
Campaign Analysis: Seasonally Adjusted Measures 128
A/B Campaign Analysis with Start and End Date Slicers 137
Customers and Website Visitors: Percentage Who Return 145
Calculating Average Customer Age 151
Moving Averages, Sums, etc. 157
Moving Averages Controlled by a Slicer 160
Chapter 5: Power Query 171
Combining Multiple Worksheets or Workbooks into a Single Power Pivot Table 171
Using Power Query to Merge CSV Files 176
Using Power Query to Unpivot a Table 186
Using Power Query to Create a Lookup Table from a Data Table 190
Creating a Calendar Table: Advanced Usage of Power Query 193
Chapter 6: Power View 199
How to Use Power View 199
Chapter 7: Power Map 221
Contents
Dedications
Bill:
to Steve Cox. Sometimes, it IS rocket science...
Rob:
To my grandparents: Brooks, Martha, Bob, and Marie. For showing
a certain little boy that silly rules were made to be broken.
A Note on Hyperlinks
You will notice that all of the hyperlinks in this book look like this:
http://ppvt.pro/
Where is something that is short and easy to type. Example:
http://ppvt.pro/1stBlog
This is a short link and is intended to make life much easier for readers of the print edition. That link above will take you to the first blog post I ever published, which went live in October of 2009.
Its real URL is this:
http://www.powerpivotpro.com/2009/10/hello-everybody/
Which would you rather type?
So just a few notes:
These short links will always start with http://ppvt.pro/ which is short for PowerPivotPro, the name of my blog.
These links are case-sensitive! If the link in the book ends in 1stBlog like above, typing 1stblog or 1stBLOG will not take you to the intended page!
Not all of these links will lead to my blog some will take you to Microsoft sites for instance.
The book does not rely on you following the links the topics covered in this book are intended to be complete in and of themselves. The links provided are strictly optional more info type of content.
Acknowledgements
Rob:
Kai Hankinson and Brad Miller - for encouraging me to bet on myself, and for being so generous with your valuable time.
Scott Shealy and family - for much-needed investment and support at a critical juncture.
The Great Chandoo - for inspiring me, for always being yourself, and for giving me a way to fund the repair of the Great Trampoline Incident.
James Williams and Lori Robinson - for putting me back together (see also: Great Trampoline Incident).
Bill Jelen - for continuing to be a fantastic friend and business partner.
Dany Hoter - for being a constant force of sanity, warmth, and mirth for more than a decade.
David Churchward - for being a stand up guy in a world that often seems to lack them.
Anthony Martin - for being the most matter-of-fact of moral compasses, and your willingness to gamble on new approaches.
Miguel Escobar and Dominik Petri - for spreading my brand of quirk to corners of the world I cannot reach.
Mike Miskell, Donovan Kelsch, Tom Phelan, Jeff Simpson, Brad Bush, Ellen Su, and Neelesh Raheja - for reinforcing, yet again, that these methods work at the highest of levels.
Kevin Collins - for a much-needed dose of old school.
Matt Allington - for being my kind of crazy. You are going to love your decision.
Zack Barresse, Denny Lee, Kasper de Jonge, and Miguel Escobar (again) - for an amazing set of guest lessons.
Jocelyn Collie - for climbing into a lifeboat with me and paddling hard. (No, not that kind of paddling, you sicko).
Bill:
Thanks to Rob for proposing this book. Bruce Springsteen taught us the importance of getting the product right: The release date is only a day. The album is forever.... This book may not sell as many copies as Born to Run, but I am using that quote to justify why the book came out when it came out. Kitty Wilson is an amazing copy editor / project manager and I appreciate her efforts in this book. Tyler Nash put up with layout changes with a smile. Thanks to the Power BI team at Microsoft. I can always count on Igor Peev to answer a quick question when I am stuck. Thanks to the crew of the Red Falcon. Parts of this book were hatched at the Cozy Corner Cafe in Merritt Island Florida. Thanks to Georgia, Krissy, Lucy, and Corrine there. Also thanks to Donnie who will rock out a blackened mahi even at breakfast time. My family always puts up with me being late for dinner, whether that is my dad, Robert F. Jelen, my son Zeke Jelen, or the lovely MrsExcel herself, Mary Ellen Jelen.
