Data Analyst (Batch-08) Certification Program – Zero to Hero in Excel, SQL, Power BI, Tableau and Python

Categories: MS Excel, Power BI, Python, SQL, Tableau
Wishlist Share
Share Course
Page Link
Share On Social Media

About Course

Data analysis is a process of inspecting, cleansing, transforming, and modelling data with the goal of discovering useful information, informing conclusions, and supporting decision-making.

 

Data analysts are in high demand across all sectors, such as finance, consulting, manufacturing, pharmaceuticals, government and education.

 

The ability to pay attention to detail, communicate well and be highly organised are essential skills for data analysts. They not only need to understand the data, but be able to provide insight and analysis through clear visual, written and verbal communication.

 

Some responsibilities of a data analyst includes:

  • Developing records management processes and policies
  • identify areas to increase efficiency and automation of processes
  • set up and maintain automated data processes
  • identify, evaluate and implement external services and tools to support data validation and cleansing
  • produce and track key performance indicators
  • develop and support reporting processes
  • monitor and audit data quality
  • liaise with internal and external clients to fully understand data content
  • gather, understand and document detailed business requirements using appropriate tools and techniques
  • design and carry out surveys and analyse survey data
  • manipulate, analyse and interpret complex data sets relating to the employer’s business
  • prepare reports for internal and external audiences using business analytics reporting tools
  • create data dashboards, graphs and visualisations
  • provide sector and competitor benchmarking
  • mine and analyse large datasets, draw valid inferences and present them successfully to management using a reporting tool

 

In this course we will perform some task of a Data Analyst  using Python ,Excel, Tableau, SQL, and  Power BI.  We will connect to a variety of data sources, perform  data transformation ,cleaning and  exploration . We will create dashboards to visual data

Show More

Course Content

Class 01 – Introduction to Power BI | Data Sourcing and Warehousimg | Difference b/w Data Mart, Data Lake, Data Warehouse | Installing Microsoft Power BI | Excel vs Power BI | Exploring Data Sources | Data Types | Introduction of Power Query

  • Data Analyst (Batch-08) – Session 01
    02:36:12

Class 02 – Exploring Data Source (Web, SQL Server) | Text Tools (Extract, Split Columns, Merge Columns Format Data) | Duplicate Column | Replace Values | Parse Data (XML, JSON) | Views (Column Distribution, Profile, Quality) | Numeric Tools (Statistics, Standard, Trigonometry, Rounding, Information) | Handling Errors

Class 03 – Change Type with Locale | Date Tools (Month Name, Year, Start of Year, Start of Week, Name of Day, Age) | Conditional Column | Index Column | Merge Queries | Append Queries | Grouping Data | Line Chart (Zoom Chart, Gridlines, Shade Color, Forecasting, Trend Line)

Class 04 – Understanding of Database Management Systems (DMS).| Tables | Primary & Foreign Keys | Relationship Cardinality | Relationships (One-to-Many) | Star and Snowflake Schemas | Real Life E-Commerce Sample Database (Datatypes)

Class 05 – Data Modeling and it’s Practical Implementation | Identifying Primary Keys | Relationships between Tables | Custom Layout Examples of Star and Snowflake Schema | Bi-Directional Flows | Active and Inactive Relationship | Connecting Multiple Fact Tables (Through Dimensions) | Hide Columns in Model View | Creating Hierarchies

Class 06 – DAX Measures and Conditional Columns | What is Data Analysis Expression (DAX) | Difference between DAX and M-Code | Conditional Columns | DAX Measures (SUM, AVERAGE, COUNT, DISTINCTCOUNT, DIVIDE) | Logical Functions (IF, SWITCH)

Class 07 – Power BI – DAX – Measures and Conditional Columns (ALL, FILTER) | Iterator Functions (SUMX, MAXX, MINX) | Time Intelligence Functions (DATESYTD, DATEADD, DATEINPERIOD) | Creating Dynamic Measures and Conditional Logic | Using ALL and FILTER for Context Modification | Applying Iterator Functions for Row-Wise Calculations | Leveraging Time Intelligence for Periodic Analysis and Comparisons

Class 08 – Power BI – Creating Dashboard: Map Visuals | Table Visuals | Donut Chart | Top N Filtering | Real-Life Scenarios Discussion | Designing Interactive Dashboards with Map and Table Visuals | Using Donut Charts for Proportional Data | Applying Top N Filters for Focused Insights | Discussing Practical Applications and Use Cases

Class 09 – Power BI – Gauge Chart | Slicers | Visual Filters | Styling of Filters | Line Charts | Bookmarks and Buttons | Edit Interactions | Creating and Customizing Gauge Charts for KPIs | Using Slicers for Interactive Filtering | Applying and Styling Visual-Level Filters | Designing Line Charts for Trend Analysis | Implementing Bookmarks and Buttons for Navigation | Editing Visual Interactions Between Report Elements

Class 10 – Power BI – Navigation Buttons | Slicer Panel Creation and Management | View Roles and Role-Based Security | Discussed Importance of Roles | Field Parameters | Designing Interactive Navigation with Buttons | Creating and Managing Slicer Panels for Filtering | Setting Up and Testing Roles for Data Security | Understanding Role Significance in Reports | Using Field Parameters for Dynamic Visualizations

Class 11 – Power BI – AI Visuals | Key Influencers | Decomposition Tree | Q&A | Narrative | Anomalies | Using AI-Powered Visuals to Gain Deeper Insights | Identifying Key Factors Affecting Metrics with Key Influencers | Breaking Down Data Hierarchically Using Decomposition Tree | Interactive Q&A for Natural Language Queries | Generating Automated Narratives for Reports | Detecting Anomalies and Outliers in Data

Class 12 – Career Guidance | Question and Answer Session with Students | Job Description Analysis | Suggestions about Freelancing | Self Working Motivation

Class 13 – MarketMindz – Power BI Project: Creating New Tables from Existing Data | Data Pre-Processing Techniques (Cleaning, Transforming) | Visualizations for Campaign Analysis (Key Metrics, Trends, Insights)

Class 14 – MarketMindz – Power BI Project: Visualizations for Buyer Analysis (Charts, Graphs, Insights) | Power BI Themes (Default and Custom) | Creating Custom Themes Using Images | Enhancing Report Aesthetics and User Experience

Class 15 – Power BI Revision | SQL – Introduction to Databases | SQL Basics | Schema Understading | Basic Query Syntax | SELECT Statement

Class 16 – SQL – Command Types (Data Definition Language (DDL), Data Manipulation Language (DML), Data Query Language (DQL)) | SQL Main Clauses (SELECT, FROM, WHERE, GROUP BY & HAVING) | SQL Numeric Functions () | DATE Functions

Class 17 – SQL – Comparison Operators | Logical Operators | String Functions (UPPER, LOWER, CONCAT, LEN, LEFT, RIGHT, REPLACE) | Logical Functions (IFF, CASE – WHEN – END Statement)

Class 18 – SQL – DATE Functions | GETDATE(), DATEPART, DATEDIFF, DATEADD, DATENAME, YEAR, MONTH | CASE Statements | Performing Date Calculations and Formatting | Extracting Specific Date Parts for Reporting | Handling Date Differences and Future/Past Calculations | Using CASE for Conditional Logic in Queries

Class 19 – SQL – Numeric Functions | CAST Function | Unicode ‘N’ Explanation | SQL Query Execution Plan | Subqueries Intro | Normalization 1NF, 2NF, 3NF | Performing Mathematical Operations with Numeric Functions | Type Conversion Using CAST | Understanding Unicode and Prefix ‘N’ for International Characters | Analyzing Query Performance Using Execution Plan | Introduction to Writing Subqueries | Database Design Best Practices with Normal Forms

Class 20 – SQL – Entity Relationship Diagram (ERD) | SQL Joins (INNER, LEFT, RIGHT, OUTER) | Visualizing Database Structure and Table Relationships Using ERD | Understanding One-to-One, One-to-Many, and Many-to-Many Relationships | Joining Tables to Retrieve Related Data | Real-World Use Cases for Each Type of Join

Class 21 – SQL – Data Control Language (DCL) | Transaction Control Language (TCL) – COMMIT and ROLLBACK | BEGIN TRANSACTION | SQL Discussion | All Topics Revision in Detail | Managing Permissions and Access with DCL | Ensuring Data Integrity with TCL Commands | Using Transactions to Control Multiple SQL Operations | Comprehensive Recap of All Key SQL Concepts

Class 22 – SQL – Views | UNION ALL | UNION | INTERSECTION | LIKE Command | GROUP BY Clause (Revision) | Creating and Querying Virtual Tables with Views | Combining Query Results Using UNION Variants | Filtering Data with Pattern Matching Using LIKE | Aggregating Data by Categories Using GROUP BY

Class 23 – SQL – Self Join | Common Table Expression (CTE) – Building Virtual Tables | Using Self Join to Compare Rows Within the Same Table | Creating Readable and Reusable Queries with CTE | Simplifying Complex Joins and Subqueries | Real-World Scenarios for Hierarchical and Recursive Data Queries

Class 24 – SQL – Window Functions (RANK, DENSE_RANK, ROW_NUMBER) | Aggregate Window Functions (SUM, MAX, MIN, COUNT) Using PARTITION BY | Ranking Rows Within Groups | Performing Group-Wise Aggregations Without GROUP BY | Real-World Use Cases in Analytics and Reporting

Class 25 – SQL – Analytical Window Functions (LEAD, LAG, FIRST_VALUE, LAST_VALUE) | Real World Practical Examples | Understanding Row-Wise Calculations Without Aggregation | Applying Functions for Trend Analysis and Comparative Reporting | Use Cases in Business Intelligence and Reporting Scenarios

Class 26 – SQL Server – Triggers (Part 1) | AFTER INSERT | BEFORE UPDATE | AFTER DELETE | General Discussion on AI and Cloud Computing Concepts

Class 27 – SQL Server – Advanced Concepts | Triggers – Part 2 | Stored Procedures with IN, OUT, and INOUT Parameters | Understanding SQL Functions – System-Defined and User-Defined Functions

Class 28 – Getting Started with Python | Installed Anaconda Navigator | Wrote First Python Program | Understood Python Fundamentals | Explored Core Data Types | Performed Variable Assignments | Applied String Data Type Functions

Class 29 – Core Data Structures in Python | Lists | Sets | Tuples | Dictionaries | Boolean Data Types | Indexing and Slicing in Lists | Built-in Python Functions | Comparison Operators and Their Use Cases

Class 30 – Python: Control Flow and Logic in Python | Chained Comparison Operators | Conditional Statements: if | elif | else | Loops in Python: for Loop | while Loop | Built a “Guess the Number” Game | Compared Lambda Functions vs User-Defined Functions

Class 31 – Exploring Essential Python Libraries | How to Install and Use Python Libraries | NumPy – Working with 1D, 2D, and 3D Arrays | Array Indexing and Slicing Techniques | Pandas – Core Functions for Data Handling | Importing CSV, Excel, and Other File Formats in Python | Practical Use Cases and Applications

Class 32 – Exploratory Data Analysis (EDA) Using Python | Analyzed Roller Coaster Dataset (Project 1) | Applied Pandas for Data Handling | Created Visualizations Using Matplotlib and Seaborn (Scatter | Bar | KDE | Histogram | Heatmap & More) | Explained How to Connect to a Database in Python

Class 33 – Exploratory Data Analysis (EDA) Using Python | Performed EDA on Laptop Price Dataset (Project 2) | Utilized Pandas for Data Manipulation | Visualized Data Using Matplotlib and Seaborn | Demonstrated Advanced Python Coding Techniques with Assistance from GPT

Class 34 – Introduction to Excel (Interface Overview, Basic Navigation) | Cells and Ranges (Selecting, Naming, Using in Formulas) | Basic Functions (SUM, AVERAGE, MIN, MAX) | Cell Referencing (Relative, Absolute, Mixed) | 3D Referencing Across Worksheets | Text Functions (TRIM, CONCAT, PROPER) | Practical Examples and Best Practices

Oodles of Noodles (Power BI Project)

Class 35 – Text Functions (LEFT, RIGHT, MID) | Logical Functions (IF, AND, OR) | Nested IF Statements | SWITCH Function | IFS Function | Practical Examples and Use Cases for Conditional Logic and Text Extraction

Class 36 – Conditional Functions (COUNTIFS, SUMIFS, AVERAGEIFS, MINIFS, MAXIFS) | Syntax and Usage of COUNTIFS and SUMIFS | Applying AVERAGEIFS for Conditional Averaging | Using MINIFS and MAXIFS for Conditional Minimum and Maximum Values | Practical Examples and Common Use Cases

Class 37 – Lookup Functions (LOOKUP, VLOOKUP) | Understanding Syntax and Usage of LOOKUP | VLOOKUP for Vertical Data Lookup | Common Errors and How to Fix Them | Practical Examples and Tips for Efficient Lookup

Class 38 – INDEX MATCH (Understanding INDEX and MATCH Functions, Combining INDEX with MATCH for Dynamic Lookups, Advantages over VLOOKUP) | XLOOKUP (Introduction to XLOOKUP, Syntax and Usage, Handling Exact and Approximate Matches, Replacing VLOOKUP and HLOOKUP) | Power Query in Excel (Data Import and Transformation, Cleaning and Shaping Data, M Language Basics, Automating Data Preparation)

Class 39 – Pivot Tables (Creating and Customizing Pivot Tables, Grouping Data, Calculated Fields, Filtering and Sorting) | Power Pivot (Introduction to Power Pivot, Data Model Creation, Relationships Between Tables, Using DAX for Calculations, Building Advanced Data Models)

Class 40 –

Class 41 –