Table Of Content

Power Pivot and Power BI: The Excel User's Guide to the Data Revolution by Rob Collie & Avi Singh Holy Macro! Books PO Box 541731 Merritt Island, FL 32954 Power Pivot and Power BI © 2016 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 & Avi Singh Layout: Jill Bee Technical Editor: Scott Senkeresty Cover Design: Shannon Travise & Jocelyn Collie Indexing: Nellie J. Liwam Published by: Holy Macro! Books, PO Box 541731 Merritt Island FL 32954 USA Distributed by: Independent Publishers Group, Chicago, IL ISBN: 978-1-61547-039-6 Print, 978-1-61547-226-0 PDF, 978-1-61547-349-6 ePub, 978- 1-61547-126-3 Mobi LCCN: 2015940638 Contents at a Glance Dedications Supporting Workbooks and Data Sets Errata and Book Support A Note on Hyperlinks Foreword and Forward Introduction - Our Two Goals for this Book 1 - A Revolution Built On YOU 2 - Power Pivot and the Power BI Family: Making Sense of the Various Versions 3 - Learning Power Pivot “The Excel Way” 4 - Loading Data Into Power Pivot 5 - Intro to Calculated Columns 6 - Introduction to DAX Measures 7 - The “Golden Rules” of DAX Measures 8 - CALCULATE() – Your New Favorite Function 9 - ALL() – The “Remove a Filter” Function 10 - Thinking in Multiple Tables 11 - “Intermission” – Taking Stock of Your New Powers 12 - Disconnected Tables 13 - Introducing the FILTER() Function, and Disconnected Tables Continued 14 - Introduction to Time Intelligence 15 - IF(), SWITCH(), BLANK(), and Other Conditional Fun 16 - SUMX() and Other X (“Iterator”) Functions 17 - Multiple Data Tables 18 - Multiple Data Tables – Differing Granularity 19 - Performance: Keep Things Running Fast 20 - Power Query to the Rescue 21 - Power BI Desktop 22 - “Complicated” Relationships 23 - Row and Filter Context Demystified 24 - CALCULATE and FILTER – More Nuances 25 - Time Intelligence with Custom Calendars: Greatest Formula in the World 26 - Advanced Calculated Columns 27 - New DAX Functions… and Variables! 28 - “YouTube for Data” – The Importance of a Server PS: Can We Ask You for a Special Favor? A1 - Power Pivot and SSAS Tabular: Two Tools for the Price of One (again!) A2 - Cube Formulas – the End of GetPivotData() A3 - Some Common Error Messages A4 - People: The Most Powerful Feature of Power Pivot Index Detailed Table of Contents Power Pivot and Power BI Dedications Supporting Workbooks and Data Sets Errata and Book Support A Note on Hyperlinks Foreword and Forward “State of the Union” November 2015 – What’s Changed? What Has Changed at Microsoft? Virtually Everything. What’s Changed in My Corner of the World? Also Everything. Introduction - Our Two Goals for this Book 1 - A Revolution Built On YOU Does This Sound Familiar? Excel Pros: The World Is Changing in Your Favor Our Importance Today Excel at the Core Three Ingredients of Revolution Ingredient One: Explosion of Data Ingredient Two: Economic Pressure Ingredient Three: Dramatically Better Tools 2 - Power Pivot and the Power BI Family: Making Sense of the Various Versions It’s a Family of Products Built on Shared Engines Power Query is a Close Second in Importance Visuals: The Crucial “Last Mile” Power BI Desktop: Two Tools for the (Learning) Price of One! Same Engines, Just Different Visuals What do we mean by the “tough” or “valuable” stuff? Power Pivot (in Excel) Versions Power Pivot for Excel 2010 Only Available in “Pro Plus” Excel Power Pivot for Excel 2013 - Differences in User Interface: 2010, 2013, 2016 When We Said “Cosmetic” We Meant “Awkward” 32-bit or 64-bit? Office 2010 or Newer is Required 3 - Learning Power Pivot “The Excel Way” Power Pivot is Like Getting Fifteen Years of Excel Improvements All at Once Learn Power Pivot As You Learned Excel: Start Simple & Grow When to Use Power Pivot, and How it Relates to Normal Pivot Usage What This Book Will Cover in Depth 4 - Loading Data Into Power Pivot No Wizards Were Harmed in the Creation of this Chapter Everything Must “Land” in the Power Pivot Window Launching the Power Pivot Window One Sheet Tab = One Table You Cannot Edit Cells in the Power Pivot Window Everything in the Power Pivot Window Gets Saved into the Same XLSX File Many Different Sources Linked Tables (Data Source Type) Advantages Limitations Tips and Other Notes Pasting Data Into Power Pivot (Data Source Type) Advantages Limitations Importing From Text Files (Data Source Type) Advantages Limitations Databases (Data Source Type) Advantages Limitations Less Common Data Source Types SharePoint Lists Reporting Services (SSRS) Reports Cloud Sources Like Azure DataMarket and SQL Azure “Data Feeds” Other Important Features and Tips Renaming up Front – VERY Important! Don’t Import More Columns than You Need