Diploma in Business Analytics – Master Data-Driven Decision Making

Power BI Desktop & Power Query

• Getting Started
• Class Introduction (BI Basics)
• Class Introduction (Power BI Desktop Overview)
• Data Discovery with Power BI Desktop
• Transforming Data (Basic Transforms)
• Transforming Data (Add Column From Example)
• Transforming Data (Appending Queries)
• Transforming Data (Merging Queries)
• Transforming Data (Combine Files)
• Transforming Data (M Query Basics)
• Transforming Data (Parameters and Templates)
• Transforming Data (Other Query Features)
• Introduction to Modeling Data
• Creating the Data Model (Modeling Basics)
• Creating the Data Model (Model Enhancements)
• Creating the Data Model (What If Parameters)
• Creating Calculated Columns and Tables (DAX Basics)
• Creating Calculated Columns and Tables (Navigation Function)
• Creating Calculated Columns and Tables (Calculated Tables)
• Creating Calculated Measures (Measure Basics)
• Creating Calculated Measures (Time Intelligence Functions)
   

Power BI Service & Visualizations & Power BI Mobile

• Introduction to Visualizing Data
• Creating Basic Reports with the Power BI Desktop
• Creating Interactive Reports (Adding Slicers for Filters)
• Creating Interactive Reports (Visualizing Tabular Data)
• Creating Interactive Reports (Visualizing Categorical Data)
• Creating Interactive Reports (Visualizing Data Trends)
• Creating Interactive Reports (Visualizing Categorical and Trend Data Together)
• Creating Interactive Reports (Visualizing Geographical Data with Maps)
• Creating Interactive Reports (Visualizing Goal Tracking)
• Creating Interactive Reports (Using Custom Visuals)
• Creating Interactive Reports (Digital Storytelling)
• Creating Interactive Reports (Other Features)
• Using the Power BI Service (Deploying to the Power BI Service)
• Using the Power BI Service (Creating and Sharing Dashboards)
• Using the Power BI Service (Using Power BI Q&A)
• Using the Power BI Service (Setting up App Workspaces)
• Using the Power BI Service (Subscriptions and Alerts)
• Using the Power BI Service (Excel Integration)
• Using the Power BI Service (Export and Embed Options)
• Refreshing the Data (Refreshing Data Overview)
• Refreshing the Data (Installing the Data Gateway)
• Refreshing the Data (Scheduling a Data Refresh)
• Mobile BI (Power BI Mobile Overview)
• Mobile BI (Designing Reports and Dashboards for Mobile)
• Mobile BI (Interacting with the Power BI Mobile App)
• Creating the Bookmarks
• Use the Selection

Get Data/Basic Dax

• Excel
• CSV
• Flat File
• Web
• SQL Server
• Getting Started
• Data Modeling Basics (Overview)
• Data Modeling Basics (Tabular)
• What is DAX (Overview)
• What is DAX (Tabular)
• Creating Calculated Columns (Basics)
• Creating Calculated Columns (Basics Continued)
• Navigation Functions
• Conditional and Logical Functions
• Creating Calculated Measures
• Time Intelligence (Overview)
• Time Intelligence (Time Intelligence Functions)
• Using X-Functions and A-Functions
• Table Functions
• DAX as a Query Language

Advance Dax/Projects

• Class Introduction (Class Files)
• Class Introduction (Why Advanced DAX)
• Working with Variables
• Working with Filter Context (What is Filter Context?)
• Working with Filter Context (CALCULATE)
• Working with Filter Context (Overriding Filter Context)
• Working with Filter Context (FILTER)
• Working with Filter Context (FILTER and ALL)
• Working with Filter Context (ALLEXCEPT)
• Working with Filter Context (Totals)
• CALCULATE and Time Intelligence (YTD and MTD)
• CALCULATE and Time Intelligence (Rolling 12 Months Total)
• CALCULATE and Time Intelligence (Rolling 3 Month Average Total)
• Semi-Additive Measures (Overview)
• Semi-Additive Measures (Opening and Closing Balance)
• Row Context (Understanding Row Context and Context Transition)
• Row Context (Working with Iterator Functions)
• Nested Row Context
• Error Handling
• Security (Implementing Basic Security)
• Security (Dynamic Security)
• Security (Implementing Basic Security – Tabular)
• Security (Dynamic Security – Tabular)
• Role Playing Dimensions (Multiple Table Import)
• Role Playing Dimensions (USERELATIONSHIP)
• Advanced DAX Function (CONTAINS)
• Advanced DAX Function (FIND)
• Advanced DAX Function (SEARCH and SUBSTITUTE)
• Advanced DAX Function (ALLSELECTED)
• Advanced DAX Function (RANKX)
• Q&A
• Key Influencers
• Decomposition Tree
• Smart Narrative
• Create the Workspace
• Publish the Dashboard
• Access Controls
• Power BI Projects

VBA Basics & Macro Foundations

• Introduction to Programming
• Logical Thinking, Flowcharts & Algorithms
• Defining Objectives, Start & End Points
• Identifying Solutions & Breaking into Steps
• Writing Step-by-Step Instructions & Flowcharts
• Process Flow Diagrams
• Excel Macros – An Introduction
• Process Documentation
• Creating & Recording a Macro
• Macro Naming Conventions
• Limitations of Macros
• Debugging Macros
• IDE Environment Overview

Excel VBA Programming Core

• Sheet, Range, Multirange & Offset Methods
• Insert/Delete Rows & Columns
• Text Wrapping, Merging, Fonts, Autofit
• Hide/Unhide Sheet, Strongly Hide
• With Block, Tab Color, Cell Color
• Autofilter (Single, Multiple, Array, XLOR)
• Advanced Filter
• Variables, Constants, Data Types, Arrays
• Operators, Expressions, Loops (For, Do, While)
• Conditional Statements (If, Else, Select Case)
• Workbook Functions (Save, Save As, Close)
• Objects, Properties, Methods
• Option Explicit, ThisWorkbook vs ActiveWorkbook

Advanced VBA, Events & User Forms

• Introduction to Events
• Event Triggers and Use Cases
• VBE Overview: Developer Tab, Security, Project Explorer
• Password Protection of Code
• Debugging Tools: Breakpoints, Watches, Immediate Window
• MessageBox and InputBox
• User Defined Functions (UDFs)
• Single & Multi-Dimensional Arrays
• Using Dictionary and Collections
• Working with Dynamic Ranges
• Protecting Worksheets, Cells, and Code
• Accessing File System, Opening & Saving Files
• Building Dashboards Using VBA
• Basic Report Automation with Word & PowerPoint
• Working with UserForms: ListBox, ComboBox, Buttons, etc.
• Creating Dynamic Dashboards with UserForms
• Linking Multiple UserForms

Integration, Automation & Real Projects

• Connecting VBA with Internet Explorer
• Exporting Excel Data to Notepad
• Automating PowerPoint Presentations with VBA
• Working with Access Database via VBA
• Sending Emails through Outlook with VBA
• Integrating Excel with Microsoft Word
• Automation Development for Business Use Cases
• Live Projects and Real-World Scenarios
• Case Studies on Automation and Reporting

Part 1

• Overview of the Basics of Excel
  • An Overview of the Screen, navigation and Basic Spreadsheet concepts
  • Excel Hierarchy
  • Mathematical and Statistical
  • Various data selection techniques of Data
  • Excel shortcut keys
  • Grouping and Ungrouping of Sheets
  • Range and Cell
  • Cell Referencing/Freezing Concept
  • Protecting and un-protecting worksheets and cells
• Mathematical Functions in Advanced Excel
  • Sum, Sumif, Sumifs
  • Count, Counta, Countblank, Countif, Countifs
  • Average, Averagea, Averageif, Averageifs
  • Subtotal, Aggregate
  • Rand, Randbetween
  • Round, Roundup, Rounddown
  • SumProduct
  • DCount, DCountA, DSum, DAverage
• Date and Time Functions in Advanced Excel
  • Date, Day, Month, Year
  • EDate, EOMonth
  • Networkdays, Networkdays.INTL
  • Workday, Workday.INTL
  • Weeknum, Weekday
  • Hour, Minute, Second
  • Now, Today, Time
• Text Functions and Data Validation in Advanced Excel
  • Char, Clean, Code
  • Concatenate, Find, Search, Substitute, Replace
  • Len, Right, Left, Mid
  • Lower, Upper, Proper, Text, Trim, Value
  • Large, Small
  • Filters (Basic, Advanced, Conditional)
  • Data Sort (Ascending, Descending, Cell/Font Color)
  • Conditional Formatting
  • Data Validation, Dependent Validation
  • Group and Ungroup
  • Data split
• Statistical and Other Functions in Advanced Excel
  • Max, Min, Median
  • Len, Right, Left, Mid
  • Maxa, Maxifs
  • Minifs, Mina
  • Vara, Correl, Geomen, Slope, Forecast
• Logical Functions in Advanced Excel
  • IF, Nested IF
  • AND, OR, NOT
  • ISNA, ISBLANK, ISERR, ISERROR
  • ISEVEN, ISODD
  • ISLOGICAL, ISTEXT, ISNUMBER
  • Other logical operators
  • Truth Table
• Lookup and Reference Functions in Advanced Excel
  • Lookup, Vlookup, Hlookup
  • Index, Match
  • Offset, Indirect, Address
  • Column, Columns, Row, Rows
  • Choose
  • Arrays Concept in Lookup Formula’s
  • Paste Special, Paste link
  • Hyperlink
  • Insert object in Excel (Excel, Notepad, Access, Email, Image, etc.)

Part 2

• Working with Reports in Advanced Excel
  • Pivot Tables
  • Golden Rule of Pivot Table
  • Grouping and Ungrouping in Pivot
  • Calculated fields in Pivot
  • Slicer in Pivot
  • Slicer Formatting
  • Dynamic Pivot
  • Pivot Data Model
  • Formatting and customizing
  • Advanced options of Pivot tables
  • Consolidated pivot tables
  • Show values As in pivot
• Data Analysis and Visualization
  • Power Pivot
  • What-if Analysis
  • Data Table – One Variable and Two Variables
  • Data Analysis Using Statistics
  • Descriptive Statistics
  • ANOVA
  • Moving Average
  • Testing Hypothesis
  • Measuring Covariance and Correlation
  • Distribution
  • Regression
  • Graphs and Charts
  • Analysis Tool Pack
  • Solver
  • Histogram
  • Pareto
  • Water Fall
  • Import and Export data
  • Worksheet formatting and Print Display
• Data Collection Method in Advanced Excel
  • Import data from web browser
  • Import data from MS-Access
  • Import data from Text file
  • Import data from SQL Server
  • Import data from other data sources as well
• Analysis Single/Multidimensional Analysis in Advanced Excel
  • Three Dimensional (3D) Tables
  • Sensitivity Analysis like Data Table
  • Manual What-If Analysis
  • Threshold Values
  • Goal Seek
  • One-Variable Data Table
  • Two-Variable Data Table
  • Solver
  • Histogram
• Data Analysis and Visualization (Charts)
  • Charts Overview
  • Charts Description
  • Create charts like Column, Line, Pie, Bar, Combo, Area, Bubble, Scatter etc.
  • Formatting Charts
  • Using 3D Graphs
  • Using Secondary Axis in Graphs
  • Sharing Charts with PowerPoint / MS Word
  • Dynamic Chart updates (Data Modified in Excel, Chart would automatically get updated)
• Advanced Dashboard in Excel
  • Dashboard using Active X Form Controls
  • Create Interactive Dashboard using Option Button, Scroll Bar, Check Box, Combo Box
  • Form Controls for reports automation
• Two Live Report Development in Advanced Excel (Real World Data)

Introduction to Data Preparation using Tableau

• Data Visualization
• Business Intelligence tools
• Introduction to Tableau
• Tableau Architecture
• Tableau Server Architecture
• VizQL Fundamentals
• Introduction to Tableau Prep
• Tableau Prep Builder User Interface
• Data Preparation techniques using Tableau Prep Builder tool

Data Connection with Tableau Desktop

• Features of Tableau Desktop
• Connect to data from File and Database
• Types of Connections
• Joins and Unions
• Data Blending
• Tableau Desktop User Interface

Basic Visual Analytics

• Visual Analytics
• Basic Charts Bar Chart, Line Chart, and Pie Chart
• Hierarchies
• Data Granularity
• Highlighting
• Sorting
• Filtering
• Grouping
• Sets

Calculations in Tableau

• Types of Calculations
• Built-in Functions (Number, String, Date, Logical and Aggregate)
• Operators and Syntax Conventions
• Table Calculations
• Level of Detail (LOD) Calculations
• Using R within Tableau for Calculations

Advanced Visual Analytics

• Parameters
• Tool tips
• Trend lines
• Reference lines
• Forecasting
• Clustering

AN INTRODUCTION TO PYTHON

Python Fundamentals Course Outline

• Brief History
• Why Python
• Where to use

Beginning Python Basics

• The print statement
• Comments
• Basic data types introduction
• String Operations in Python
• Simple Input & Output
• Simple Output Formatting

Data Types and Structures in Python

• Numbers
• Strings
• List
• List Methods
• Dictionary
• Tuple
• Sets
• Frozen sets
• Mutability and immutability
• Type Casting
• Assignment

PYTHON PROGRAM FLOW

Control Structures and Loops

• Indentation
• The If statement and its related statements
• An example with if and its related statements
• The while loop
• The for loop
• The range statement
• Break & Continue statements
• Enumerate, zip & tuple unpacking
• Examples for looping
• Assignment

Functions & Modules

• Create your own functions
• Function Parameters
• Variable Arguments
• Scope of a Function
• Function Documentation/Docstrings
• Lambda Functions
• Map and Filter
• Exercise with functions
• Assignment

EXCEPTION HANDLING

Errors and Exception Handling

• Errors
• Exception Handling with try
• Handling Multiple Exceptions
• Writing your own Exceptions

File Handling

• File Handling Modes
• Reading Files
• Writing & Appending to Files
• Handling File Exceptions
• The with statement

Classes in Python

• Classes introduction
• Variable Type
• Creating Classes
• Defining objects
• Instance Methods
• Inheritance
• Polymorphism
• Assignment

Power Automate Desktop – 16.5-Hour Training Plan

• Day 1: Introduction to Power Automate Desktop (1.5 hrs)
  • What is Robotic Process Automation (RPA)?
  • Overview of Power Automate Desktop
  • Power Automate vs. Power Automate Desktop
  • Installing Power Automate Desktop
  • Navigating the PAD Interface
  • Creating Your First Flow
• Day 2: Understanding Flow Structure (1.5 hrs)
  • Flow Types and Components
  • Recorder vs. Manual Actions
  • Variables and Data Types
  • Error Handling Basics
  • Comments and Documentation in Flows
• Day 3: Core Actions – Part 1 (1.5 hrs)
  • Launching and Closing Applications
  • File System Operations (Create, Read, Write, Delete Files/Folders)
  • Clipboard Operations
• Day 4: Core Actions – Part 2 (1.5 hrs)
  • Message Boxes and Input Dialogs
  • Working with Loops (For, While, Loop Condition)
• Day 5: Web and Desktop Automation (1.5 hrs)
  • UI Elements and Selectors
  • Web Automation using Web Recorder
  • Desktop Automation using UI Elements
  • Handling Popups and Dialog Boxes
  • Automating Data Entry in Forms
• Day 6: Data Manipulation (1.5 hrs)
  • Working with Variables and Lists
  • String Operations (Trim, Split, Join)
  • Text Parsing and Regular Expressions
  • Reading and Writing CSV Files
  • Excel Automation (Launch Excel, Read/Write Cells)
• Day 7: Logic and Error Handling (1.5 hrs)
  • IF, SWITCH, and CASE statements
  • Try-Catch-Finally Blocks
  • Exception Logging
  • Debugging and Troubleshooting
• Day 8: Email and Outlook Automation (1.5 hrs)
  • Sending and Receiving Emails
  • Reading Email Attachments
  • Automating Outlook Tasks and Calendar Events
  • Conditional Logic with Email Triggers
• Day 9: Excel and Database Integration (1.5 hrs)
  • Reading and Writing Excel Files without Opening
  • Data Filtering and Sorting
  • Connecting to SQL Server/Access
  • Executing Queries and Using Data Tables
• Day 10: Advanced Features & Use Cases (1.5 hrs)
  • Using Subflows for Reusability
  • Working with JSON and XML
  • Use Cases:
    • Invoice Processing
    • Data Scraping and Reporting
    • File Organization
    • Web Data Extraction + Email Notification
• Day 11: Final Project & Certification (1.5 hrs)
  • Hands-on End-to-End Automation Project
  • Review and Feedback
  • Assessment Quiz
  • Course Completion Certificate