Collie Rob - Power Pivot alchemy: patterns and techniques for Excel

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

Dedicationsv

Acknowledgementsvii

Forewordix

Introductionxi

Chapter 1: Dashboard Tricks and Visualization Techniques1

Adding a Last Refreshed Date Readout1

Normalizing Your Measures to First/Average/Max Values for Charts4

Bubbling Up Exceptions with Sarah Problem10

Ranks and Exceptions That Bubble Up to Subtotals12

Chapter 2: Slicers: The Gateway to Interactivity23

Initializing Slicers That Contain Too Many Values to Scroll26

Wingdings and Other Symbolic Fonts in Slicers28

Using Macros to Change Slicer Fonts40

A Simple Trick for Combatting Stale Slicers42

User-Friendly Report Sorting with Slicers46

Adding State Alpha Sorting to the Sort-by-Slicer Trick50

Conditional Formatting Controlled via Slicers60

Catching Slicer Selections in a Formula69

Chapter 3: Conquering Common Calculated Column Conundrums75

An Excel Pros Primer on Calculated Columns75

Subtotaling Calc Columns and the EARLIER() Function76

Referencing the Previous Row and Similar Calcs79

Referencing Rows Within Range of the Current Row 80

Totaling Data Table Values in Lookup Tables90

CONTAINSX: Finding Matching Values in Two Tables92

CONTAINSX Revisited: What Is the Match?94

Common Calendar Conundrums97

Custom Calendar Conundrums100

Chapter 4: Modeling and Portable Formulas103

Integrating Data of Different Grains109

A Mystifying and Awesome Solution for the Many-to-Many Problem117

Calculating Same-Store Sales in Power Pivot120

Same-Store Sales Continued: Using Store Open/Close Dates123

Campaign Analysis: Seasonally Adjusted Measures128

A/B Campaign Analysis with Start and End Date Slicers137

Customers and Website Visitors: Percentage Who Return145

Calculating Average Customer Age151

Moving Averages, Sums, etc.157

Moving Averages Controlled by a Slicer160

Chapter 5: Power Query171

Combining Multiple Worksheets or Workbooks into a Single Power Pivot Table171

Using Power Query to Merge CSV Files176

Using Power Query to Unpivot a Table186

Using Power Query to Create a Lookup Table from a Data Table190

Creating a Calendar Table: Advanced Usage of Power Query193

Chapter 6: Power View199

How to Use Power View199

Chapter 7: Power Map221

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.

Foreword

Walk through the halls of any office today, and youll be surrounded by people who have favorite Excel tips and tricks. This incredible software has a 30-year history and an installed base of 750 million computers; it is easy to find people all around who can offer bits and pieces of advice about Excel. Unfortunately, this is not the case with Power Pivot. Power Pivot is new, and there arent yet a lot of people using it. If you are trying to do something with Power Pivot, it is likely that you are the only person in your office who even knows what Power Pivot is.