Duration: 1day

Objectives

  • 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.

Audience

Excel users who want to see different analytics options in Excel. Familiarity with Excel and basic functions is a must.

Outline for Advanced Data Analytics with Excel Training

Working with Tables and PivotTables in Excel

  • Configuring filters
  • Adding slicers
  • Configuring time-intelligence slicer
  • Adding conditional formatting to tables
  • Adding sparklines
  • Adding databars
  • 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/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 PowerPivot 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
03/04/2024 - 03/04/2024
10:00 AM - 06:00 PM
Eastern Standard Time
Online Virtual Class
USD $770.00
Enroll
04/08/2024 - 04/08/2024
10:00 AM - 06:00 PM
Eastern Standard Time
Online Virtual Class
USD $770.00
Enroll