• What is data analysis
• Why Is Data Analysis important?
• Data Analysis Use Cases
• Data Analysis Tools
• Types of Data Analysis: Techniques and Methods
• Data Analysis Process

• What is Data visualization
• Scope of data visualization
• The different types of visualization
• Types of data visualization charts

Introduction to Excel

• Introduction to Excel interface
• Understanding rows and columns, Naming Cells
• Working with excel workbook and sheets

Formatting excel work book

• New, Open, Close, Save, Save As
• Formatting Text: Font Size, Font Style
• Font Color, Use the Bold, Italic, and Underline
• Wrap text, Merge and Centre
• Currency, Accounting and other formats
• Modifying Columns, Rows, & Cells

Perform Calculations with Functions

• Creating Simple Formulas
• Setting up your own formula
• Date and Time Functions, Financial Functions
• Logical Functions
• Lookup(V-Lookup and H-Lookup) and Reference Functions
• Mathematical Functions
• Statistical Functions, Text Functions
• Working with Templates

Managing Tables

• Creating Tables
• Naming the Table
• Changing the Table Style
• Creating a Total Row
• Creating a calculated column
• Using Filtering in Table
• Removing the Duplicate Record

Sort and Filter Data with Excel

• Sort and filtering data
• Using number filter, Text filter
• Custom filtering
• Removing filters from columns
• Conditional formatting

Create Effective Charts to Present Data Visually

• Inserting Column, Pie chart etc.
• Create an effective chart with Chart Tool
• Design, Format, and Layout options
• Adding chart title
• Changing layouts
• Chart styles
• Editing chart data range
• Editing data series
• Changing chart
• Saving chart as template
• Summarizing data using spark line

Analyze Data Using Pivot Tables and Pivot Charts

• Understand PivotTables, Create a PivotTable
• Framework Using the PivotTable and PivotChart
• Create Pivot Chart from pivot Table
• Inserting slicer
• Creating Calculated fields

Protecting and sharing the work book

• Protecting a workbook with a password
• Allow user to edit ranges
• Track changes
• Working with Comments
• Insert Excel Objects and Charts in Word Document and Power Point Presentation

Use Macros to Automate Tasks

• Creating and Recording Macros
• Assigning Macros to the work sheets
• Saving Macro enabled workbook

Calculations with Functions

• Setting up your own formula
• Calculations between worksheets
• Mathematical Functions
• Statistical Functions
• Text Functions
• Logical IF Function
• Logical functions with multiple conditions
• Lookup Functions (V-Lookup and H-Lookup)
• Using range names in formulas
• Conditional summary function
• Countif, Sumif, Averageif functions

Create Effective Charts to Present Data Visually

• Create a Chart with the Chart Wizard
• Choose the Right Type of Chart for Your Data
• Use the Chart Toolbar, Select Objects in a Chart
• Configure Chart Options, Change the Chart Type
• Change a Chart’s Source Data
• Change the Format, the Chart Area, Plot area
• Configure and Change the Scale of an Axis
• Liven Up Charts with Patterns, Fills, and Pictures
• Format Individual Chart Elements, Print Charts
• Editing Chart data series, Advanced Chart options

Analyze Data Using PivotTables and Pivot Charts

• Understand PivotTables
• Create a PivotTable Framework
• Using the PivotTable and PivotChart Wizard
• Create the PivotTable on the Framework
• Grouping and Ungrouping Data
• Choose PivotTable Options to Configure a PivotTable
• Create Pivot Charts from PivotTables
• Create a Conventional Chart from PivotTable Data
• Creating Calculated fields, Inserting Slicer

More Excel Functions

• Automatic Rounding of Long Numbers
• Round Function
• Round Down Function
• Round Up Function
• Excel Text Functions
• Using Excel TEXT with other functions
• Separate Strings, Logical functions
• AND function, OR Function
• Combining logical functions with other functions

Index & Match functions

• Index Function, Match Function
• Combination of Index and Match Function
• VLOOKUP and match formulas in excel
• Indirect & Offset functions
• Using dynamic lookup tables
• Using range names.
• Applying range names in formulas.

Financial Functions

• Financial Function
• PMT, IPMT, PPMT, RATE, NPER
• Calculating Present Value(PV)
• Calculating Future Value(FV)
• Loan Amortization Schedule.

Excel Data Tools

• Data Validation,Circle Invalid Data
• Clear Validation Circles
• Excel Goal seek function
• Data Table,Group, Outline
• Ungroup, Clear outlineSorting
• Using Subtotal, Removing subtotals
• Advanced filtering

Informative functions

• Informative functions,Iferror, Iserror
• Isn, Isnumber, Istext, Isblank
• Combinations of informative functions with other Functions

Excel Dashboard

• Dashboard Design Tips
• Visual Dashboarding Techniques
• Data Sources
• Interactivity with Slicers
• Build a Sales Dashboard

Recording & Editing Macros

• Writing a New Macro, Recording Macro
• Running a Macro, Using a Shortcut Key
• Using the Visual Basic Toolbar
• Using the Visual Basic Editor Window
• Assigning a Shortcut Key
• Using Relative References
• Assigning a Macro to a Menu
• Deleting a Macro from a Menu
• Deleting a Macro from work book
• Editing Macro Commands
• Creating a Custom Button
• Adding a Button to Quick Access Toolbar
• Assigning a Macro to a Button
• Changing a Button Image

INTRODUCTION TO DATA ANALYSIS

• Introduction to Excel interface
• Basic Data Importing
• Importing Data from other Sources
• CSV, Word, Enter Data Directly
• Importing from Websites, SQL Servers
• Entering Data Directly
• Importing from SQL Servers

THE POWER BI DESKTOP

• Installing Power BI Desktop
• Exploring the Power BI Desktop Interface
• Utilizing File Types; PBIX vs PBIT
• Examining Data Sources
• Discussing Data Refresh
• Understanding Data Types

Basic Report Design

• Power BI Desktop Installation
• Data Sources & Visual Types
• Canvas, Visualizations and Fields
• Get Data and Memory Tables
• In-Memory xvelocity Database
• Table and Tree Map Visuals
• Format Button and Data Labels
• Legend, Category and Grid
• PBIX and PBIT File Formats
• Visual Interaction, Data Points
• Disabling Visual Interactions
• Edit Interactions – Format Options
• SPOTLIGHT & FOCUSMODE
• CSV and PDF Exports. Tooltips
• Power BI EcoSystem, Architecture

Visual Sync, Grouping

• Slicer Visual: Real-time Usage
• Orientation, Selection Properties
• Single & Multi Select, CTRL Options
• Slicer: Number, Text and Date Data
• Slicer List and Slicer Dropdowns
• Visual Sync Limitations with Slicer
• Disabling Slicers,Clear Selections
• Grouping: Real-time Use, Examples
• List Grouping and Binning Options
• Grouping Static / Fixed Data Values
• Grouping Dynamic / Changing Data
• Bin Size and Bin Limits (Max, Min)
• Bin Count and Grouping Options
• Grouping Binned Data, Classification

Hierarchies, Filters

• Creating Hierarchies in Power BI
• Independent Drill-Down Options
• Dependant Drill-Down Options
• Conditional Drilldowns, Data Points
• Drill Up Buttons and Operations
• Expand & Show Next Level Options
• Dynamic Data Drills Limitations
• Show Data and See Records
• Filters: Types and Usage in Real-time
• Visual Filter, Page Filter, Report Filter
• Basic, Advanced and TOP N Filters
• Category and Summary Level Filters
• DrillThru Filters, Drill Thru Reports
• Keep All Filters” Options in DrillThru
• CrossReport Filters, Include, Exclude

Visualization Properties

• Stacked Charts and Clustered Charts
• Line Charts, Area Charts, Bar Charts
• 100% Stacked Bar & Column Charts
• Map Visuals: Tree, Filled, Bubble
• Cards, Funnel, Table, Matrix
• Scatter Chart: Play Axis, Labels
• Series Clusters & Selections
• Waterfall Chart and ArcGIS Maps
• Infographics, Icons and Labels
• Color Saturation, Sentiment Colors
• Column Series, Column Axis in Lines
• Join Types: Round, Bevel, Miter
• Shapes, Markers, Axis, Plot Area
• Display Units,DataColors,Shapes
• Series, Custom Series and Legends

INTERACTIVE DASHBOARDS

• Stacked Charts and Clustered Charts
• Line Charts, Area Charts, Bar Charts
• 100% Stacked Bar & Column Charts
• Map Visuals: Tree, Filled, Bubble
• Cards, Funnel, Table, Matrix
• Scatter Chart: Play Axis, Labels
• Series Clusters & Selections
• Waterfall Chart and ArcGIS Maps
• Infographics, Icons and Labels
• Color Saturation, Sentiment Colors
• Column Series, Column Axis in Lines
• Join Types: Round, Bevel, Miter
• Shapes, Markers, Axis, Plot Area
• Display Units,DataColors,Shapes
• Series, Custom Series and Legends

DAX Functions – Level 1

• DAX: Importance in Real-time
• Real-world usage of Excel, DAX
• DAX Architecture, Entity Sets
• DAX Data Types, Syntax Rules
• DAX Measures and Calculations
• ROW Context and Filter Context
• DAX Operators, Special Characters
• DAX Functions, Types in Real-time
• Vertipaq Engine, DAX Cheat Sheet
• Creating, Using Measures with DAX
• Creating, Using Columns with DAX
• Quick Measures and Summaries
• Validation Errors, Runtime Errors
• SUM, AVERAGEX, KEEPFILTERS
• Dynamic Expressions, IF in DAX

DAX Functions – Level 2

• Data Modeling Options in DAX
• Detecting Relations for DAX
• Using Calculated Columns in DAX
• Using Aggregated Measures in DAX
• Working with Facts & Measures
• Modeling: Missing Relations
• Modeling: Relation Management
• CALCULATE Function Conditions
• CALCULATE & ALL Member Scope
• RELATED & COUNTROWS in DAX
• Entity Sets and Slicing in DAX
• Dynamic Expressions, RETURN
• Date, Time and Text Functions
• Logical, Mathematical Functions
• Running Total & EARLIER Function

DAX FUNCTIONS Level 3

• 1:1, 1:M and M:1 Relations
• Connection with CSV, MS Access
• AVERAGEX and AVERAGE in DAX
• KEEPFILTERS and CALCUALTE
• COUNTROWS, RELATED, DIVIDE
• PARALLELPERIOD, DATEDADD
• CALCULATE & PREVIOUSMONTH
• USERELATIONSHIP, DAX Variables
• TOTALYTD, TOTALQTD
• DIVIDE, CALCULATE, Conditions
• .ELSE..THEN Statement
• SELECTEDVALUE, FORMAT
• SUM, DATEDIFF Examples in DAX
• TODAY, DATE, DAY with DAX
• Time Intelligence Functions – DAX

Power Query Level 1

• Power Query M Language Purpose
• Power Query Architecture and ETL
• Data Types, Literals and Values
• Power Query Transformation Types
• Table & Column Transformations
• Text & Number Transformations
• Date, Time and Structured Data
• List, Record and Table Structures
• let, source, in statements @ M Lang
• Power Query Functions, Parameters
• Invoke Functions, Execution Results
• Get Data, Table Creations and Edit
• Merge and Append Transformations
• Join Kinds, Advanced Editor, Apply
• ETL Operations with Power Query

Power Query Level 2

• Query Duplicate, Query Reference
• Group By and Advanced Options
• Aggregations with Power Query
• Transpose, Header Row Promotion
• Reverse Rows and Row Count
• Data Type Changes & Detection
• Replace Columns: Text, NonText
• Replace Nulls: Fill Up, Fill Down
• PIVOT, UNPIVOT Transformations
• Move Column and Split Column
• Extract, Format and Numbers
• Date & Time Transformations
• Deriving Year, Quarter, Month, Day
• Add Column: Query Expressions
• Query Step Inserts and Step Edits

Power Query Level 3

• Creating Parameters in Power Query
• Parameter Data Types, Default Lists
• Static/Dynamic Lists for Parameters
• Removing Columns and Duplicates
• Convert Tables to List Queries
• Linking Parameters to Queries
• Testing Parameters and PBI Canvas
• Multi-Valued Parameter Lists
• Creating Lists in Power Query
• Converting Lists to Table Data
• Advanced Edits and Parameters
• Data Type Conversions, Expressions
• Columns from Examples, Indexes
• Conditional Columns, Expressions

OTHER DATA SOURCES

• Import and Upload Options in Excel
• Excel Workbooks and Dashboards
• Datasets in Excel and Dashboards
• Using Excel Analyzer in Power BI
• Using Excel Publisher in PBI Cloud
• Excel Workbooks, PINS in Power BI
• Excel ODC Connections, Power Pivot
• Row Level Security (RLS) with DAX
• Need for RLS in Power BI Cloud
• Data Modeling in Power BI Desktop
• DAX Roles Creation and Testing
• Adding Power BI Users to Roles
• Custom Visualizations in Cloud
• Histogram, Gantt Chart, nfographics

POWER BI CLOUD, SERVICE & MOBILE

Power Bi Cloud – 1

• Power BI Service Architecture
• Power BI Cloud Components, Use
• App Workspaces, Report Publish
• Reports & Related Datasets Cloud
• Creating New Reports in Cloud
• Report Publish and Report Uploads
• Dashboards Creation and Usage
• Adding Tiles to Dashboards
• Pining Visuals and Report Pages
• Visual Pin Actions in Dashboards
• LIVE Page Interaction in Dashboard
• Adding Media: Images, Custom Links
• Adding Chs and Embed Links
• API Data Sources, Streaming Data
• Streaming Dataset Tiles (REST API)

Power Bi Cloud – 2

• Dashboards Actions,Report Actions
• DataSet Actions: Create Report
• Share, Metrics and Exports
• Mobile View & Dashboard Themes
• Export, Subscribe, Subscribe
• Favorite, Insights, Embed Code
• Featured Dashboards and Refresh
• Gateways Configuration, PBI Service
• Gateway Types, Cloud Connections
• Gateway Clusters, Add Data Sources
• Data Refresh: Manual, Automatic
• PBIEngw Service, ODG Logs, Audits
• DataFlows, Power Query Expressions
• Adding Entities and JSON Files

Dashboards Development

• Creating Dashboards
• Pin Visuals and Pin LIVE Report Pages to Dashboard
• Advantages of Dashboards
• Interacting with Dashboards
• Adding Tiles to Dashboards
• Web Content, Image, Text Box,Video
• Formatting Dashboard
• Sharing Dashboard

Data Gateways

 Introduction to Data Gateways

• How Data Gateways work
• Connect to an on-premise Data Source by using a Data Gateway
• Download Data Gateway
• Installing a Data Gateway
• Types of Gateways
• On-premises Data Gateway, On-premises Data Gateway (personal mode)
• Manage Data Gateway
• Add and Remove Administrators
• Add Data Source, Add or Remove Users to a Data Source
• Refresh On Premise Data
• Configuring Automatic Refresh using Schedules

Collaboration in Power BI using App Workspace

• Introduction to App Workspaces
• Create an App Workspace
• Add Members to App Workspace to Collaborate
• App Workspace Access
• Admin, Member, Contributor

Sharing Power BI Content using Apps and Content Packs

• Introduction to App
• Publish an App
• Update a Published App
• Manage Content in App
• Include in App, Exclude in App
• Sharing App
• Entire Organization, Specific individuals or group
• Un publishing an App
• Content Pack Introduction
• Create Content Pack
• Sharing Content Packs
• Specific Groups, My Entire Organization
• Selecting the Content / Items to Publish or Share

DASHBOARD PREPARATION

• Creating dashboards using Excel
• Creating dashboards using Power BI

MIS REPORTS

• Creating MIS Reports with Excel
• Sales Report
• Financial Report
• Profit Report
• Income Report
• Purchase Report
• The Summary Reports
• The Trend Reports
• The Exception Reports
• On-Demand Reports