Services

Data Analysis
and Visualization

In the Microsoft technology suite, tools like Excel, Power Query, Power Pivot, and Power BI are tossed around as possible solutions to data problems you’re facing. Learn which tools to use in various scenarios, how to use them effectively, and how to sell others on your insights.

What we can teach

Topics as simple as changing the layout of your data can make a huge difference in the flexibility, powerful, usefulness, and accuracy of your files. Learn core tools like Pivot Tables and functions, and more advanced topics like Macros and Power Query.

Fundamental

  • The Excel Interface
  • New Workbooks
  • Entering, Editing, and Formatting
  • Navigating and Using Shortcuts
  • Saving and File Types
  • Calculating with Formulas and Functions
  • AutoFill
  • Absolute and Relative References
  • AutoCorrect and Spellcheck
  • Templates
  • Printing and PDFing
Intermediate

  • List Design
  • Format as Table
  • Sorting
  • Filtering
  • Subtotals
  • Charts
  • Pivot Tables and Charts
  • Conditional Formatting
  • Data Validation
  • Protecting a Worksheet
Advanced

  • Named Ranges
  • Logical, Text, Date, Database, and Lookup Functions
  • Nested Functions
  • Consolidate Data
  • Data and Scenario Analysis Tools
  • Macros
  • Power Query
  • Power Pivot

The first step to successfully working with data is ETL – extracting the data from whatever source is holding it, transforming it so that it’s useful, and loading it into Excel or Power BI to be calculated and visualized. Power Query is the incredible new tool that makes it all easy.

Fundamental

  • Understanding the Microsoft BI Stack
  • What is ETL?
  • Extracting from Spreadsheets
  • Extracting from CSVs
  • Extracting from Databases
  • Extracting from Websites and Other Sources
  • Working with Columns
  • Working with Rows
  • Data Types
Intermediate

  • Text-Part Transformations
  • Pivoting and Unpivoting
  • Transposing
  • Date Math
  • Numeric Functions
  • Conditional Functions
  • Appending Data
Advanced

  • Outer Joins
  • Inner Joins
  • Anti Joins
  • Complex M Functions
  • Working with the Data Model

Once your data is available to Excel or Power BI, modeling that data – creating relationships and calculations – is the next vital step. Power Pivot is significantly more powerful and dynamic than standard Excel functions and Pivot Tables.

Fundamental

  • Understanding the Microsoft BI Stack
  • What is Power Pivot?
  • Fact Tables and Dimension Tables
  • Creating Relationships
  • Working with Composite Keys
  • Working with Bridge Tables
  • Building Dynamic Calendars
  • Understanding DAX
Intermediate

  • Creating a Calculated Column
  • Creating a Measure
  • Aggregate Functions
  • Iterator Functions
  • CALCULATE()
  • ALL()
  • Time Intelligence
Advanced

  • DAX Syntax
  • Power Pivot Best Practices
  • Text Functions
  • Complex Filtering Relationships

Data visualization is much more than knowing how to make a bar chart in Excel. At its core, data visualization is a communication skill – the ability to translate analyzed information into easily understood graphics. This combines understanding people with understanding software.

Core Charting

  • Handling Source Data
  • Reference Best Practices
  • Understanding Data Abstraction
  • MVC Design Patterns
  • Inserting Charts
  • Formatting Charts
  • Building Layouts
Design Theory

  • Understanding Design Theory
  • Repetition and Contrast
  • Signal and Noise
  • Audience Profiling
  • Choosing a Medium
  • Storytelling with Data
Advanced Visuals

  • Conditional Formatting
  • Dynamic Interaction
  • Layered Visuals
  • Small Multiples
  • Building Dashboards
  • Publishing Visuals

Excel’s core visual tools have been extended significantly with the introduction of Power BI. In this course, we build visual reports that are perfectly suited to the audience and the purpose, and expand your world of charting tools.

Power BI Reports

  • Planning and Best Practices
  • Choosing Effective Visuals
  • Visual Interactions and Filters
  • Essential Chart Types
  • Map Visuals
Design Theory

  • Understanding Design Theory
  • Repetition and Contrast
  • Signal and Noise
  • Audience Profiling
  • Choosing a Medium
  • Storytelling with Data
Custom Visuals

  • Drillthrough reports
  • Bookmarking and Links
  • Adding KPIs
  • Animation
  • Navigation and Selection

The team at Knack Training customizes every hands-on training to the world and requirements of the attendees.

  • Live Classroom Training
  • Seminar and Conference Sessions
  • Web-Based Training
  • Video Courses
  • Consulting
The team at Knack Training customizes every hands-on training to the world and requirements of the attendees.

Have any questions?
We’ll be happy to answer

Have any questions?
We’ll be happy to answer