PowerPivot Alchemy : Patterns and Techniques for Excel.
Jelen, Bill.
PowerPivot Alchemy : Patterns and Techniques for Excel. - 1st ed. - 1 online resource (257 pages)
Cover -- Title page -- Copyright -- Table of Contents -- Dedications -- A Note on Hyperlinks -- Acknowledgements -- Foreword -- Introduction -- Chapter 1: Dashboard Tricks and Visualization Techniques -- Adding a "Last Refreshed Date" Readout -- Normalizing Your Measures to First/Average/Max Values for Charts -- Bubbling Up Exceptions with "Sarah Problem" -- Ranks and Exceptions That Bubble Up to Subtotals -- Chapter 2: Slicers: The Gateway to Interactivity -- "Initializing" Slicers That Contain Too Many Values to Scroll -- Wingdings and Other Symbolic Fonts in Slicers -- Using Macros to Change Slicer Fonts -- A Simple Trick for Combatting "Stale" Slicers -- User-Friendly Report Sorting with Slicers -- Adding State Alpha Sorting to the Sort-by-Slicer Trick -- Conditional Formatting Controlled via Slicers -- Catching Slicer Selections in a Formula -- Chapter 3: Conquering Common Calculated Column Conundrums -- An Excel Pro's Primer on Calculated Columns -- Subtotaling Calc Columns and the EARLIER Function -- Referencing the Previous Row and Similar Calcs -- Referencing Rows "Within Range" of the Current Row -- Totaling Data Table Values in Lookup Tables -- CONTAINSX: Finding Matching Values in Two Tables -- CONTAINSX Revisited: What Is the Match? -- Common Calendar Conundrums -- Custom Calendar Conundrums -- Chapter 4: Modeling and Portable Formulas -- Integrating Data of Different "Grains" -- A Mystifying and Awesome Solution for the Many-to-Many Problem -- Calculating Same-Store Sales in Power Pivot -- Same-Store Sales Continued: Using Store Open/Close Dates -- Campaign Analysis: Seasonally Adjusted Measures -- A/B Campaign Analysis with Start and End Date Slicers -- Customers and Website Visitors: Percentage Who Return -- Calculating Average Customer Age -- Moving Averages, Sums, etc -- Moving Averages Controlled by a Slicer. Chapter 5: Power Query -- Combining Multiple Worksheets or Workbooks into a Single Power Pivot Table -- Using Power Query to Merge CSV Files -- Using Power Query to "Unpivot" a Table -- Using Power Query to Create a Lookup Table from a Data Table -- Creating a Calendar Table: Advanced Usage of Power Query -- Chapter 6: Power View -- How to Use Power View -- Chapter 7: Power Map -- Thank you -- Index -- More from Holy Macro! Books.
Rob Collie (PowerPivotPro) and Bill Jelen (MrExcel) join forces in this combined sequel to their best-selling Power Pivot books. Alchemy sets aside the fundamentals of past books and provides a series of practical, easy-to-follow "patterns" for dozens of common analysis, reporting, and dashboarding needs. Three "bonus" chapters introduce you to the newer members of the "Power BI" family - Power Query, Power View, and Power Map. Whether you are starting your transformation from "old school" Excel User to Modern Data Professional or a veteran of the Power BI arts, this book will dramatically expand your data superpowers.
9781615472147
Microsoft PowerPivot (Computer file).
Microsoft Excel (Computer file).
Electronic spreadsheets -- Computer programs.
Electronic books.
HF5548.4.M5258 -- .C655 2014eb
005.54
PowerPivot Alchemy : Patterns and Techniques for Excel. - 1st ed. - 1 online resource (257 pages)
Cover -- Title page -- Copyright -- Table of Contents -- Dedications -- A Note on Hyperlinks -- Acknowledgements -- Foreword -- Introduction -- Chapter 1: Dashboard Tricks and Visualization Techniques -- Adding a "Last Refreshed Date" Readout -- Normalizing Your Measures to First/Average/Max Values for Charts -- Bubbling Up Exceptions with "Sarah Problem" -- Ranks and Exceptions That Bubble Up to Subtotals -- Chapter 2: Slicers: The Gateway to Interactivity -- "Initializing" Slicers That Contain Too Many Values to Scroll -- Wingdings and Other Symbolic Fonts in Slicers -- Using Macros to Change Slicer Fonts -- A Simple Trick for Combatting "Stale" Slicers -- User-Friendly Report Sorting with Slicers -- Adding State Alpha Sorting to the Sort-by-Slicer Trick -- Conditional Formatting Controlled via Slicers -- Catching Slicer Selections in a Formula -- Chapter 3: Conquering Common Calculated Column Conundrums -- An Excel Pro's Primer on Calculated Columns -- Subtotaling Calc Columns and the EARLIER Function -- Referencing the Previous Row and Similar Calcs -- Referencing Rows "Within Range" of the Current Row -- Totaling Data Table Values in Lookup Tables -- CONTAINSX: Finding Matching Values in Two Tables -- CONTAINSX Revisited: What Is the Match? -- Common Calendar Conundrums -- Custom Calendar Conundrums -- Chapter 4: Modeling and Portable Formulas -- Integrating Data of Different "Grains" -- A Mystifying and Awesome Solution for the Many-to-Many Problem -- Calculating Same-Store Sales in Power Pivot -- Same-Store Sales Continued: Using Store Open/Close Dates -- Campaign Analysis: Seasonally Adjusted Measures -- A/B Campaign Analysis with Start and End Date Slicers -- Customers and Website Visitors: Percentage Who Return -- Calculating Average Customer Age -- Moving Averages, Sums, etc -- Moving Averages Controlled by a Slicer. Chapter 5: Power Query -- Combining Multiple Worksheets or Workbooks into a Single Power Pivot Table -- Using Power Query to Merge CSV Files -- Using Power Query to "Unpivot" a Table -- Using Power Query to Create a Lookup Table from a Data Table -- Creating a Calendar Table: Advanced Usage of Power Query -- Chapter 6: Power View -- How to Use Power View -- Chapter 7: Power Map -- Thank you -- Index -- More from Holy Macro! Books.
Rob Collie (PowerPivotPro) and Bill Jelen (MrExcel) join forces in this combined sequel to their best-selling Power Pivot books. Alchemy sets aside the fundamentals of past books and provides a series of practical, easy-to-follow "patterns" for dozens of common analysis, reporting, and dashboarding needs. Three "bonus" chapters introduce you to the newer members of the "Power BI" family - Power Query, Power View, and Power Map. Whether you are starting your transformation from "old school" Excel User to Modern Data Professional or a veteran of the Power BI arts, this book will dramatically expand your data superpowers.
9781615472147
Microsoft PowerPivot (Computer file).
Microsoft Excel (Computer file).
Electronic spreadsheets -- Computer programs.
Electronic books.
HF5548.4.M5258 -- .C655 2014eb
005.54