Course

📊 Advance Excel with Power Query more details...

Module 1: Basic Excel Features and Functionality part 1

• Understanding the number format codes
• Changing the font color with number format codes
• Hide Number and Text based on criteria
• GOTO SPEICAL Feature
• Creating Name range and Name Manager
• Hyperlink from a different sheet, workbook, cell, defined names and websites
• Remove Duplicates
• Data Validation: Data List, rules, modify error messages
• Grouping, ungrouping and Subtotal in Excel

Module 2: Basic and Intermediate Excel Features and Functionality part 2

• COUNT, COUNTA, COUNTBLANK
• COUNTIF
• COUNTIF with wildcard
• Uses of COUNTIF with real-time example
• Unique Entry using the COUNTIF function
• Find duplicates using COUNTIF Function
• Running Count
• Example of COUNTIFS
• Example of SUMIFS
• Example of AVERAGEIFS
• Example of DCOUNT
• Example of COUNTA
• Example of DSUM
• Example of DAVERAGE

Module 3: Number Function & Formulas

• COUNT, COUNTA, COUNTBLANK
• COUNTIF
• COUNTIF with wildcard
• Uses of COUNTIF with real-time example
• Unique Entry using the COUNTIF function
• Find duplicates using COUNTIF Function
• Running Count
• Example of COUNTIFS
• Example of SUMIFS
• Example of AVERAGEIFS
• Example of DCOUNT
• Example of COUNTA
• Example of DSUM
• Example of DAVERAGE

Module 4: Logical and SUMPRODUCT Functions

• Logical Functions: IF, Nested IF, AND, OR, NOT, XOR, IFERROR
• SMALL and LARGE function
• SUMPRODUCT Function
• Use SUMPRODUCT for COUNT
• Use SUMPRODUCT for SUM
• Use SUMPRODUCT for COUNTIF and COUNTIFS
• Use SUMPRODUCT for SUMIF and SUMIFS
• Use SUMPRODUCT for AVERAGE and AVERAGEIFS
• Use SUMPRODUCT for MAXIF and MAXIFS

Module 5: Text functions in Excel to solve complex text problems

• Introduction of TEXT Functions (LEFT, RIGHT, MID, REPLACE, SUBSTITUTE, etc) available in Excel
• Use text functions to Split the words
• Use text functions to split characters and numbers

Module 6: Date & Time Functions in Excel, creating a dynamic calendar for whole months and years

• Introduction of Date Functions available in excel.
• Create and Design Calendar using Different Date Functions (WEEKDAY, WEEKNUM, EOMONTH, EDATE) in excel.
• Calculate Age using DATEDIF Function
• Calculate the date and time difference
• count working days using NETWORKDAYS Function

Module 7: LOOKUP, VLOOKUP, HLOOKUP,XLOOKUP, Dynamic LOOKUP functions

• Introduction of LOOKUP VLOOKUP and HLOOKUP
• VLOOKUP with Exact Match
• VLOOKUP with Approximate Match
• Usage of VLOOKUP Functions using ROW and COLUMN functions
• Example of VLOOKUP with Array
• Example of HLOOPKUP with multiple Examples
• Example of LOOKUP with multiple examples

Module 8: INDEX and OFFSET function, Uses of Array in Excel

• Introduction of the INDEX Function
• Introduction of OFFSET Function
• Use of INDEX Function
• Advance uses of Array Function using INDEX, OFFSET, MATCH, SMALL, LARGE, ROW, and COLUMN function
• Use of ROW and ROWS function using INDEX and OFFSET function

Module 9: INDIRECT and ADDRESS Function

• Introduction of INDIRECT Function
• Introduction of ADDRESS Function
• Uses of INDIRECT Function
• Uses of ADDRESS Function

Module 10: Data Analysis using Power Query and Power Pivot in Microsoft Excel

• Creating a Pivot table and Pivot chart in Excel
• Adding Slicer and Timeline to analyze in Excel
• Using the power pivot to do the Data Analysis
• Using Power Query to Source the data from Different Sources

Module 11: MIS Reports and Dashboards

• Introduction of Dashboards and MIS Reports
• Adding and Using User Form control in Excel
• Recording Macro and run it through a command button
• Creating Dynamic Formula based on User Form control
• Creating Dynamic Charts using Formula
• Creating 2D charts in Excel
• Importance of charts in Dashboards and MIS Reports

Module 12: Creating Dynamic Dashboard on Live data Project

• Project work on MIS Dashboard

Module 13: Excel 365 Function and usage

• Data Import from multiple sources
• XLOOKUP
• FILTER
• ColumnIndex

📈 Power BI

Module 1: Power BI Fundamentals

• Introduction to Data Visualization
• Understanding Business Intelligence Concepts
• Getting Started with Power BI
• Overview of Power BI Architecture
• Core Components of the Power BI Ecosystem
• End-to-End Power BI Workflow
• Introduction to Power BI Desktop
• Power BI Licensing Models (Free, Pro & Premium)
• Installing and Configuring Power BI Desktop
• Exploring the Power BI Desktop Interface

Module 2: Data Acquisition in Power BI

• Connecting to Different Data Sources
• Data Storage & Loading Modes in Power BI
• Import vs DirectQuery vs Live Connection
• Choosing the Right Data Connectivity Option

Module 3: Data Transformation Essentials

• Promoting Rows to Headers
• Creating Duplicate Columns
• Splitting Columns into Multiple Fields
• Removing Duplicate Records and Unnecessary Columns
• Replacing and Standardizing Values
• Managing Data Types
• Grouping and Aggregating Data

Module 4: Advanced Data Preparation

• Pivoting and Unpivoting Data
• Data Formatting Techniques
• Creating Custom Columns
• Using and Invoking Functions
• Appending Multiple Queries
• Merging Queries with Joins
• Combining Files from Folders
• Working with Parameters
• Additional Power Query Features

Module 5: Data Modeling Fundamentals

• Introduction to Data Modeling
• Key Components of a Data Model
• Understanding the Power BI Model Engine
• Flat, Star and Snowflake Schema Designs
• Data Granularity Concepts
• Working with Multiple Fact Tables
• Creating Relationships Between Tables
• One-to-One Relationships
• One-to-Many Relationships
• Many-to-Many Relationships

Module 6: Advanced Data Modeling

• Role-Playing Dimensions
• Managing Table Visibility and Naming
• Building Hierarchies
• Data Categories and Formatting
• Grouping and Binning Techniques
• Creating Drill-Through Reports
• Using What-If Parameters

Module 7: Data Visualization & Reporting

• Report Design Options
• Overview of Visualization Types
• Using Custom Visuals
• Visualization Best Practices
• Effective Color Selection
• Chart Types and Business Use Cases
• Working with Slicers and Filters
• Building an End-to-End Interactive Dashboard

Module 8: Advanced Reporting Features

• Enhancing the Interactive Dashboard
• Data Storytelling with Reports
• Bookmarks and Selection Pane
• Spotlight and Focus Mode
• Drill-Through Navigation
• Advanced Report Interactions
• Additional Reporting Features

Module 9: Power BI Service Fundamentals

• Introduction to Power BI Service
• Publishing Reports and Datasets
• Creating and Managing Workspaces
• Dashboards vs Reports
• Sharing and Collaboration Features
• Managing Content in the Power BI Service
• Workspace Roles and Permissions

Module 10: Power BI Service Administration

• Export and Embedding Options
• Integrating Power BI with Excel
• Implementing Row-Level Security (RLS)
• Understanding Data Refresh Strategies
• Gateway Setup and Configuration
• Using Power BI Mobile Applications
• Power BI Administrative Roles
• Monitoring Usage and Audit Logs

Module 11: DAX Fundamentals & Calculations

• Introduction to DAX
• DAX Data Types
• Creating Calculated Columns
• Creating Calculated Tables
• Building Measures
• Calculated Columns vs Measures
• Aggregation Functions (SUM, MIN, MAX, AVERAGE, COUNT)
• Relationship Functions (RELATED, RELATEDTABLE)
• Iterator Functions (SUMX, AVERAGEX, COUNTX)
• Using Variables (VAR)
• Logical Functions (IF, SWITCH, AND, OR)
• Table Functions (VALUES, FILTER, CALCULATETABLE)
• Text and Utility Functions
• Handling Blank Values
• Ranking Calculations with RANKX
• Mastering CALCULATE and Filter Modifiers
• Understanding Row Context and Filter Context
• Semi-Additive Measures and Balance Calculations

Module 12: Date & Time Intelligence in DAX

• Working with Date Functions
• Date Difference and Relative Date Calculations
• Creating Calendar and Date Tables
• Auto Calendar Generation
• Month-to-Date (MTD) Calculations
• Quarter-to-Date (QTD) Calculations
• Year-to-Date (YTD) Calculations
• Comparing Periods with PARALLELPERIOD
• Year-over-Year Analysis
• Running Total Calculations
• Advanced Time Intelligence Scenarios

Module 13: Capstone Projects & Q&A

• End-to-End Business Intelligence Project 1
• End-to-End Business Intelligence Project 2
• Project Review and Optimization
• Interview-Oriented Discussion
• Frequently Asked Questions
• Course Wrap-Up and Next Steps

Module 14: Projects

• End-to-End Business Intelligence Project 1
• End-to-End Business Intelligence Project 2
• Misc.

🗄 SQL Server

SQL Basics

• SELECT, WHERE
• JOINS

Advanced SQL

• Stored Procedures
• Indexes

🐍 Python

ADF Basics

• Linked Services
• Datasets

Pipelines & Triggers

• Pipeline Creation
• Scheduling

☁ Azure Data Factory

MODULE 1: Introduction

• Overview
• Course Structure
• What is Cloud Computing?
• What is Azure Data Factory ? (with example)

MODULE 2: Environment Setup

• Module Overview
• Create Azure Account
• Suggest for Upcoming Courses
• Azure Portal Walkthrough
• Create Storage Account
• Bonus - Storage Account Redundancy Options
• Bonus - Storage Account Access Tier
• Create Azure DataLake Gen2 Account
• Create Azure Data Factory
• Create Azure SQL DB
• Create SQL Server VM
• Quiz: Storage Account Redundancy Options and Access Tiers

MODULE 3: Building Blocks of Azure Data Factory

• ADF Building Blocks Overview
• Pipelines
• Activities
• Linked Services
• DataSets
• Supported File Formats
• Triggers
• Interaction between Different Components

MODULE 4: Copy Data Activity

• Copy Data from Blob to ADLS
• Copy Data - Logging Settings
• Copy Data - Multiple Files from a Folder & Other Settings
• Copy Data - Last Modified Filter
• RWS #3: Append Filename Column During Copy
• Copy Data from Azure SQL Tables
• Copy Data - Fault Tolerance
• Setting Up Self Hosted IR
• Copy Data from On-Prem DB
• Delete SQL Server VM
• Copy Data from REST API

MODULE 5: Parameters & Variables

• Parameterized Components
• Variables in Azure Data Factory
• System Variables
• Set Variable Activity
• Parameterized Dataset Connection
• Parameterized Linked Service Connection

MODULE 6: Control Flow Activities

• Filter
• For Each
• Lookup
• RWS #2: Copy Multiple Files in Bulk Using Lookup Table - Use Case & Hints
• Get Metadata
• RWS #1: Copy Files Modified in the Last One Day - Use Case & Hints
• If Condition
• Wait
• Switch
• Validation

MODULE 7: Other Important Pipeline Activities

• Stored Procedure
• RWS #5: Save Pipeline Run Details in SQL Table for Audit - Use Case & Hints
• Delete Activity
• Fail Activity
• Execute Pipeline
• Dataflow Activity

MODULE 8: Mapping Data Flows

• Mapping Data Flows Introduction
• Source + Validate Schema + Schema Drift
• Sink + Error Row Handling
• Select
• Filter
• Conditional Split
• Union
• Exist
• Sort
• Rank Transformation
• Aggregate
• Join
• Pivot
• Lookup
• Derived Column
• New Branch
• Surrogate Key
• Flatten
• Alter Row
• Assert

MODULE 9: Advanced Concepts

• Expression Language
• Dynamic Queries

MODULE 10: Triggers

• What Is Trigger
• Types of ADF Triggers
• Schedule Triggers
• Event-Based Triggers
• Tumbling Window Triggers

MODULE 11: Monitoring

• Monitoring Capabilities

MODULE 12: Real World Use Cases

• Fact & Dimension Tables
• Slowly Changing Dimensions (SCD) Types
• SCD Type 1 Implementation
• SCD Type 2 Implementation
• Incremental Data Load - Use Case & Hints

MODULE 13: Real World Scenarios – Interview Prep

• RWS #1 - Copy Files Modified Within the Last One Day
• RWS #2 - Bulk Copy
• RWS #3 - Appending Source Filename Dynamically During Copy
• RWS #4 - Incremental Loading
• RWS #5 - Loading Pipeline Run Details in SQL Table for Audit
• RWS #6 - Loading Pipeline Run Copy Only Latest File
• RWS #7 - Loading Pipeline Run Copy if Files Are Not Empty
• RWS #8 - Loading Pipeline Run Copy All SQL Tables Using Lookup

MODULE 14: Projects

• Project 1
• Project 2