Data Analytics
Module 1
1. Intermidiate Excel
- Lookup and Reference function
- Text Function
- Data & Time function
- Nested formula in excel with conditional function – “If”
- Frequently used Excel Functions (Sum, count, left, right, find, If, Text etc..)
- Logical Functions
- Information functions
- Advance Conditional formatting
- What if Analysis
- Array Formula
- Basics concept of Excel charts
- Advance chart techniques
- All charts with an example
- Basic of Pivot Tables
- Understanding of Row, columns, page and value field
- Calculated field in pivot table
- Formatting of pivot table
Module 2
2 Advance Excel
- Getting Started with Power Pivot
- Enable and Navigate Power Pivot
- Manage Data Relationships
- Visualizing Power Pivot Data
- Create a Power Pivot Report
- Create Calculations in Power Pivot
- Working with Advanced Functionality in Power Pivot
- Create a Key Performance Indicator
- Getting Started with Power Query
- Data Tranformation
- Reporting
Module 3
3.1 SQL Server
- SQL Overview
- SQL Commands
- SQL Data Types
- Basic Commands
- SQL Operators
- Normalization
- SQL Constraints
- SQL Clauses
- Adding More to Our Queries
- The EXISTS Operator
- System Defined Functions
- Views
- Stored Procedures
- Transactions
- Using Sequences
- Triggers
Module 4
5. Introduction to Power BI - Need, Importance
- Power BI - Advantages and Scalable Options
- History - Power View, Power Query, Power Pivot
- Power BI Data Source Library and DW Files
- Cloud Collaboration and Usage Scope
- Business Analyst Tools, MS Cloud Tools
- Power BI Installation and Cloud Account
- Power BI Cloud and Power BI Service
- Power BI Architecture and Data Access
- On-premise Data Access and Microsoft On Drive
- Power BI Desktop - Installation, Usage
Module 5
6. Power Query & M Language - Part 1
- Power Query & M Language - Part 1
- Understanding Power Query Editor - Options
- Power BI Interface and Query / Dataset Edits
- Working with Empty Tables and Load / Edits
- Empty Table Names and Header Row Promotions
- Undo Headers Options. Blank Columns Detection
- Data Imports and Query Marking in Query Editor
- JSON Files & Binary Formats with Power Query
- JavaScript Object Notation - Usage with M Lang.
- Applied Steps and Usage Options. Revert Options
- creating Query Groups and Query References. Usage
- Query Rename, Load Enable and Data Refresh Options
- Combine Queries - Merge Join and Anti-Join Options
- Combine Queries - Union and Union All as New Dataset
- M Language: Nested Join and Join Kind Functions
- REPLACE, REMOVE ROWS, REMOVE COL, BLANK - M Lang
- Column Splits and Filled Up / Filled Down Options
- Query Hide and Change Type Options. Code Generation Class
Module 6
7. Power Query & M Language 2
- Invoke Function and Freezing Columns
- Creating Reference Tables and Queries
- Detection and Removal of Query Datasets
- Custom Columns with Power Query
- Power Query Expressions and Usage
- Blank Queries and Enumeration Value Generation
- M Language Semitics and Syntax. Transform Types
- IF..ELSE Conditions, Transform Column() Types
- Remove Columns (), Split Columns (), Replace Value ()
- Table.Distinct Options and GROUP BY Options
- Table.Group(), Table.Sort() with Type Conversions
- PIVOT Operation and Table.Pivot(). List Functions
- Using Parameters with M Language (Power Query Editor)
- Advanced Query Editor and Parameter Scripts
- List Generation and Table Conversion Options
- Aggregations using PowerQuery & Usage in Reports
- Report Generation using Web Pages & HTML Tables
- Reports from Page collection with Power Query
- Aggregate and Evaluate Options with M Language
- Creating high-density reports, ArcGIS Maps, ESRI Files
- Generating QR Codes for Reports
- Table Bars and Drill Thru Filters Class
Duration Course
- 3 Months
