Exam Ref 70-779 Analyzing and Visualizing Data with Microsoft Excel

Exam Ref 70-779 Analyzing and Visualizing Data with Microsoft Excel

by Chris Sorensen
Released June 2018
Publisher(s): Microsoft Press
ISBN: 9781509308088

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

Prepare for Microsoft Exam 70-779 and help demonstrate your real-world mastery of Microsoft Excel data analysis and visualization. Designed for BI professionals, data analysts, and others who analyze business data with Excel, this Exam Ref focuses on the critical thinking and decision-making acumen needed for success at the MCSA level.

 

Focus on the expertise measured by these objectives:

 

  • Consume and transform data by using Microsoft Excel
  • Model data, from building and optimizing data models through creating performance KPIs, actual and target calculations, and hierarchies
  • Visualize data, including creating and managing PivotTables and PivotCharts, and interacting with PowerBI

 

This Microsoft Exam Ref:

 

  • Organizes its coverage by exam objectives
  • Features strategic, what-if scenarios to challenge you
  • Assumes you have a strong understanding of how to use Microsoft Excel to perform data analysis

Table of contents

  1. Cover Page
  2. Title Page
  3. Copyright Page
  4. Dedication Page
  5. Contents at a Glance
  6. Contents
  7. Acknowledgements
  8. Introduction
    1. Organization of this book
    2. Microsoft certifications
    3. Microsoft Virtual Academy
    4. Quick access to online references
    5. Errata, updates, & book support
    6. Stay in touch
    7. Important: How to use this book to study for the exam
  9. Chapter 1 Consume and transform data by using Microsoft Excel
    1. Skill 1.1: Import from data sources
      1. Connect to and import from databases, files, and folders
      2. Connect to Microsoft SQL Azure and Big Data
      3. Import from Excel workbooks
      4. Link to data from other sources
      5. Privacy Levels
    2. Skill 1.2: Perform data transformations
      1. Design and implement basic and advanced transformations
      2. Apply business rules
      3. Change data format to support visualization
      4. Filter data
      5. Format data
    3. Skill 1.3: Cleanse data
      1. Manage incomplete data
      2. Handle data received as a report
    4. Thought experiment
    5. Thought experiment answers
    6. Chapter summary
  10. Chapter 2 Model data
    1. Skill 2.1: Create and optimize data models
      1. Understanding the Excel data model
      2. Get & Transform
      3. Manually enter data
      4. Manage data relationships
      5. Optimize models for reporting
    2. Skill 2.2: Create calculated columns, measures, and tables
      1. Create DAX formulas
      2. Create DAX queries
      3. Create Excel formulas
    3. Skill 2.3: Create Hierarchies
      1. Create date hierarchies
      2. Create business hierarchies
      3. Resolve hierarchy issues
    4. Skill 2.4: Create Performance KPIs
      1. Calculate the actual value
      2. Calculate the target value
      3. Calculate actual-to-target values
    5. Thought experiments
    6. Thought experiment answers
    7. Chapter summary
  11. Chapter 3 Visualize data
    1. Skill 3.1: Create and manage PivotTables
      1. Format PivotTables
      2. Format calculated measures
      3. Filter data
      4. Group and summarize data
    2. Skill 3.2: Create and manage PivotCharts
      1. Select a chart type
      2. Format PivotCharts
      3. Filter data
    3. Skill 3.3: Interact with Power BI
      1. Power BI overview
      2. Import Excel data from Power BI
      3. Manipulate Excel data in Power BI
    4. Thought experiment
    5. Thought experiment answers
    6. Chapter summary
  12. Index
  13. Code Snippets

Product information

  • Title: Exam Ref 70-779 Analyzing and Visualizing Data with Microsoft Excel
  • Author(s): Chris Sorensen
  • Release date: June 2018
  • Publisher(s): Microsoft Press
  • ISBN: 9781509308088