Advanced Excel Course
Text functions
IsNumber
IsText
Trim
Left
Right
Find
Search
Mid
Len
Upper
Lower
Char
Code
Concatinate
Dollar
Fixed
Proper
Substitute
Replace
Rept
Clean
Join Strings
Counting Words
Number of Instances
Text To Column
Compare Text
Exact
Reference Functions
Row
Row in Array Formula
Column
Column in Array Formula
Address
Offset
Transpose
Indirect
Hyperlink
GetPivot Data
Lookup Functions
Choose
Vlookup
Hlookup
Index
Index (Two Dimentional)
Index (Three Dimentional)
Match
Lookups with Appropriate
Match
Error Functions
IsErr
IsError
IsNA
Logical Functions
If
Nested Ifs
True
False
And
Or
Not
Date Functions
Date
Today
Now
Day
Month
Year
Networkdays
Weekday
Workday
DATEDIF
Array and Summarizing Function
Entering Array Formulas
Benefits of using Array
Formulas
Sumproduct for Multiple
condition summarization
Sumproduct with And and
Or Condition
SumIf
Countif
Sumifs
Countifs
Resolving version conflicts
Subtotal
Using Wildcards
Sumifs
Countifs
Resolving version conflicts
Subtotal
Using Wildcards
Financial functions
PMT
FV
PV
RATE
Random Functions and Concepts
Rand Between
Rand
Random Numbers
Random Dates
Random Time
Random Characters
Random Text
Generating Dependent Random Texts
Tools
Text to Columns Using Form Controls
Option Button
Drop Down
Scroll Bars
Conditional Formatting
Based on Cell Value
Creating Heat Chart
Using Data Bars
Top or Bottom Ranked Values
Above or below average
Unique or duplicate
Using Formula to Format Cells
Finding Alternate solutions for 2003 and prior versions
Using Macro Recorder
Recording Macro
Assigning Macro to Buttons
Assigning Shortcut Keys to Macro
Points to be noted while using Macro
Recorder
AutoFilter and Sorting
Filtering Data
Shortcuts
Sorting Data
Sorting by Custom List
Sorting by rows
Advanced Filter
Getting Unique List
Getting Unique Combinations
Getting only desired Columns
Points to be noted
Filtering Data in Place
Copy Filtered Data to Another Location
And Criteria
Or Criteria
And and Or Criteria
Using Formula to specify Criteria
Creating a Adv Filter Program using
Macro
Subtotal and Outlines
Prerequisite for Subtotal
Creating Subtotal
Creating Multiple Subtotals
Removing Subtotals
Creating Outlines Manually
Removing Outlines
Cell Linking
Linking (Manual Method)
Linking (by Paste Special)
Linking within Sheet/Workbook or
Other Workbooks
Words of Caution
Linking and Updation Concepts
Checking and Maintaining Links
Health
Links while sending across Locations
Moving To and from Link to
destination
Formula Auditing
Reasons when links are not updated
Database functions
Dsum
Dcount
Dget
Daverage
Charts and Dashboards
Creating Column Chart
Changing Orientation of Chart
Understanding Legends, Chart Area, Plot
Area, Series etc.
Creating Bar Chart
Gantt Chart
Mixing Various type of charts
Doughnut Chart
Pie Chart
Speedometer Chart
Scrolling Charts
Block Charts
Dashboard Concept
Creating Multiple Dashboards
Pivot Tables and Charts
Creating Pivot Table
Adding Row/Column/Data/Page Fields
Summarizing Date into Years/Qtrs/Months etc.
Summarizing Dates by Financial Year Qtrs
Analysing Pivot data from various angles
Creating Pivot Charts
Showing Report of Page Items on separate
pages
Creating Multiple Pivots from the same
cache
Situations when to use different Pivot Caches
Formatting Pivot Tables
Using Calculated Fields
Various Pivot Table Options
Using GetPivotData function
Creating Pivot Table from External Data
Consolidating Data
Formula Auditing
Trace Precedent
Trace Dependent
Clear Arrows
Consolidation
By Using Three Dimentional Sum
By Using Consolidation Tool
By Using Pivot Table
Using Access data
Importing data from Access
Pulling Excel Data to Access
Using Queries to Import Data
Understanding use of Table
What if Analysis
Data Tables
Scenerios
Goal Seek
Concepts
Date and Time Concepts
Shortcuts for Date & Time
Entering Dates and System Setting
Entering Time Manually
Time bases calculations
Getting Difference between two Dates
Getting Difference between two Time
Getting Time Difference between long Dates
Getting Workday Differences
Adding Day/Workdays to Dates
Getting Absolute Time Difference
Hyperlink
Hyperlink to webpage
Hyperlink to other Document or File
Hyperlink to a range in the same workbook
Hyperlink to an email ID
Dynamic Hyperlinks using Formula
SQL Trainings
- Introduction to Mysql
Introduction to Databases
Introduction to RDBMS
Explain RDBMS through normalization
Different types of RDBMS
Software Installation(MySQL Workbench)
- SQL Commands and Data Types
Types of SQL Commands (DDL,DML,DQL,DCL,TCL) and their applications
Data Types in SQL (Numeric, Char, Datetime)
- DQL & Operators
SELECT
LIMIT
DISTINCT
WHERE AND
OR
IN
NOT IN
BETWEEN
EXIST
ISNULL
IS NOT NULL
Wild Cards
ORDER BY
- Case When Then and Handling NULL Values
Usage of Case When then to solve logical problems and handling NULL Values (IFNULL, COALESCE)
- Group Operations & Aggregate Functions
Group By
Having Clause
COUNT
SUM
AVG
MIN
MAX
COUNT String Functions
Date & Time Function
- Constraints
NOT NULL
UNIQUE
CHECK
DEFAULT
Primary key
Foreign Key (Both at column level and table level)
- Joins
Inner
Left
Right
Cross
Self Joins
Full outer join
- DDL
Create
Drop
Alter
Rename
Truncate
Modify
Comment
- DML & TCL Commands
DML
Insert
Update & Delete
TCL
Commit
Rollback
Savepoint
Data Partitioning
- Indexes and Views
Indexes (Different Type of Indexes)
Views in SQL
- Stored Procedures
Procedure with IN Parameter
Procedure with OUT parameter
Procedure with INOUT parameter
- Function, Constructs
User Define Function
Window Functions
Rank
Dense Rank
Lead
Lag
Row_number
- Union, Intersect, Sub-query
Union, Union all
Intersect
Sub Queries, Multiple Query
- Exception Handling
Handling Exceptions in a query
CONTINUE Handler
EXIT handler
- Triggers
Triggers - Before | After DML Statement
MicroSoft POWER BI
BusinessIntelligence Power BI Course Content
Tech Bodhi IT Training’s Power BI certification course is curated by subject matter experts to help you clear the official Microsoft Power BI Data Analyst Exam: PL-300 . This course will help you gain expertise in Business Analytics and throughout the training, you will master the concepts such as Power BI Desktop, Architecture, DAX, Service, Mobile Apps, Reports, QGA, and many more with industry-relevant use cases. This Power BI training ensuresthat you get hands-on experience in real-time projects. Learn Power BI from experienced industry experts.
Module 1: Introduction to Power BI
• Power BI Job Roles in Real-time
• Power BI Data Analyst Job Roles
• Business Analyst - Job Roles
• Power BI Developer - Job Roles
• Comparing Tableau and Power BI
• Types of Reports in Real-World
• Interactive & Paginated Reports
• Analytical & Mobile Reports
• Data Sources Types in Power BI
• Power BI Licensing Plans- Types
• Power BI Dev & Prod Environments
• Understanding the Power BI Tools
• Installing Power BI & Connecting to Data
• Working with the query Editor
• Working with the data model and creating a visualization
Module 2: Basic Report Design
• 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
• Tooltips
• Power BI EcoSystem, Architecture
Module 3: Learn Basic & Advanced Charts
• 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
• Info graphics, 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, Data Colors, Shapes
• Series, Custom Series and Legends
Module 4: 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
• Bin Size and Bin Limits (Max, Min)
• Bin Count and Grouping Options
Module 5: Hierarchies, Filters
• Creating Hierarchies in Power BI
• Independent Drill-Down Options
• Dependent 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
• Drill Thru Filters, Drill Thru Reports
• Keep All Filters" Options in Drill Thru
• Cross Report Filters, Include, Exclude
Module 6: Bookmarks & its Options
• Drill-thru Filters, Page Navigations
• Bookmarks: Real-time Usage
• Bookmarks for Visual Filters
• Bookmarks for Page Navigations
• Selection Pane with Bookmarks
• Buttons, Images with Actions
• Buttons, Actions and Text URLs
• Bookmarks View & Selection Pane
• Import & Direct Query with Power BI
• SQL Queries and Enter Data
• Data Modeling, Relations
Module 7: 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
• 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
Module 8: 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, Nontax
• 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
• Creating Parameters in Power Query
• Parameter Data Types, Default Lists
• Removing Columns and Duplicates
• Convert Tables to List Queries
• Testing Parameters and PBI Canvas
Module 9: 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
• Creating, Using Measures with DAX
• Creating, Using Columns with DAX
• Quick Measures and Summaries
• SUM, AVERAGEX, KEEPFILTERS
• Dynamic Expressions, IF in DAX
Module 10: 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
• Dynamic Expressions, RETURN
• Date, Time and Text Functions
• Logical, Mathematical Functions
• Running Total & EARLIER Function
Module 11: DAX Functions Level 3
• AVERAGEX and AVERAGE in DAX
• KEEPFILTERS and CALCUALTE
• COUNTROWS, RELATED, DIVIDE
• PARALLELPERIOD, DATEDADD
• CALCULATE & PREVIOUSMONTH
• USERELATIONSHIP, DAX Variables
• TOTALYTD, TOTALQTD
• DIVIDE, CALCULATE, Conditions
• IF ELSE THEN Statement
• SELECTEDVALUE, FORMAT
• SUM, DATEDIFF Examples in DAX
• TODAY, DATE, DAY with DAX
• Time Intelligence Functions – DAX
Module 12: 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
Module 13: Power BI Cloud - 2
• Dashboards Actions, Report Actions
• Dataset Actions: Create Report
• Share, Metrics and Exports
• Mobile View & Dashboard Themes
• Q & A [Cortana] and Pin Visuals
• Export, Subscribe, Subscribe
• Featured Dashboards and Refresh
• Gateways Configuration, PBI Service
• Gateway Types, Cloud Connections
• Gateway Clusters, Add Data Sources
• Data Refresh: Manual, Automatic
• Dataflows, Power Query Expressions
Module 14: Real-Time Project
• Project Requirement Analysis
• Implementing SDLC Phases
• Requirement Gathering, FSA
Phase 1:
• PBIX Report Design
• Visualizations, Properties
• Analytics and Formatting
Phase 2:
• Data Modeling, Power Query
• Dynamic Connections, Azure DB
• Parameters and M Lang Scripts
Phase 3:
• DAX Requirements, Analysis
• Cloud and Report Server
• Project FAQs and Solutions
Module 15: Projects Coverage
• Bank Customers Analysis Project
• Udemy Data Analysis
• Retail Sales Analysis
• Insurance Project
• IPL Analysis
Bonus Session-
• Will Cover Real Time Scenarios or questions which generally occurs in Project Development.