Course Information
Advanced Data Analytics with Excel Training
Parking for SAO, Professional Development courses is in Garage B (1511 San Jacinto Blvd.). The Garage signage may read 1511 San Jacinto or Garage B. The elevator in Garage B is not reliable. If you are unable to walk the stairs, please contact the professionaldevelopment@sao.texas.gov for alternate parking arrangements. Handicapped parking is free at the meters around the downtown area.
A course coordinator will email you a parking permit prior to the course start date. A permit must be displayed or you will be ticketed.
Course Description
Are you an Excel user who wants to advance your data analytics skills by implementing the end-to-end data analytics process? This training covers importing data from various data sources, transforming/cleaning/shaping data, implementing efficient data models with relationships, hierarchies, and data categorization, data visualization using pivot tables, sparklines, indicators, data bars, maps, and embedding reports in dashboards.
Course Objectives
Upon completion of this course, participants will be able to:
Import data from different data sources, such as Excel, CSV, Web, and SQL Server.
Transform & clean data using Power Query Editor.
Perform data modeling including creating relationships and hierarchies.
Add calculations, measures, and KPIs using DAX.
Data visualization using sparklines, databars, table, and pivot table.
Use filters and slicers to limit the data displayed on the report.
Forecast data and involving what-if scenarios.
Use Natural Language Processing (NLP) to ask questions based on your data.
Detailed Course Outline
Introduction
Working with Tables and PivotTables in Excel
Configuring filters
Adding slicers
Configuring time-intelligence slicer
Adding conditional formatting to tables
Adding sparklines
Adding data bars
Adding indicators/KPIs
Using the Map Visual
Categorizing geo-spatial data
Add the basic map visual
Add the 3-D map visual
Working with Q&A/NLP (Natural Language Processing) in Excel 365
Querying data using Q&A / NLP
Import Data using Power Query in Excel
Importing Excel using Power Query
Importing CSV using Power Query
Importing Web data using Power Query
Importing SQL Server data using Power Query
Transforming and Cleaning Data using Power Query
Remove rows
Remove columns
Replace values
Merge columns
Append tables
Merge tables
Data Profiling using Power Query in Excel
Column quality
Column distribution
Column profile
Working with Power Pivot in Excel
Connecting to data sources
Using DAX to add calculated columns
Using DAX to create measures
Using DAX to create KPIs (Key Performance Indicators)
Using DAX to implement time-intelligence
Configure relationship between tables
Create hierarchies
What-if Analysis using Excel
Scenarios and variables
Using Goal Seek to get a desired result
Working with Data Tables
Preparing forecasts
Conclusion
Prerequisites
Familiarity with Excel and its basic functions.
Instructors
Holly is an exceptional lead instructor with an extensive background delivering trainings tailored to client requirements on Microsoft Power Platform, with a special emphasis in Power BI. She is an expert in creating custom curriculum and manuals and is experienced in development and presentation of customized training solutions. Holly stands out as a top choice of key customers as she consistently provides a high caliber learning engagement as a dynamic, and trusted educator.
Additional Information
Participants need to bring their own device and have access to Excel.