Professional Data Analyst Certification Program (Batch – 03) – Zero to Hero in Excel, SQL, Power BI & Python
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, 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
Course Content
Assignments/ Quizzes
Assignment 01 Solution
15:43Assignment 02 Solution
20:43- 09:47
Class 01 – Introduction of Data Analytics
Class 02 – Introduction of Power Query | Replace Value | Extract Values | Remove Columns
Class 03 – Text Tools | Numerical Tools | Date Tools | Conditional Columns | Dealing with Errors
Class 04 – Column from Example | Append Queries | Merge Queries | Group By | Manage Parameter | Duration | Filtering and Sorting
Bonus Lessons (Part 01)
Class 05 – Data Modelling and Formatting
Class 06 – Calculated Columns | Text Functions – UPPER, LOWER, LEFT, RIGHT, SEARCH, CONCETENATE | Date Functions – YEAR, MONTH, DAY, WEEKDAY, WEEKNUM, DATEDIFF
Class 07 – Conditional Functions | IF | SWITCH | AND | OR Conditions
Class 08 – Measures | Implicit Vs Explicit Measure | Filter Context | Numerical Functions | SUM | AVERAGE | MIN | MAX | DIVIDE | COUNT | DISTINCTCOUNT | CALCULATE Function
Class 09 – CALCULATE Function Case Study | ALL | FILTER | Iterator Functions | SUMX | AVERAGEX | MINX | MAXX
Class 10 – ISBLANK | IFERROR | Time Intelligence Functions | DATESYTD | DATESQTD | DATESMTD | DATEADD | SAMEPERIODLASTYEAR | PREVIOUSMONTH | DATESINPERIOD | Variables and Commenting
Bonus Lesson (Part 02)
Class 11 – Data Visualization | Card Visual | Line Chart | Bar Chart
Class 12 – KPI Charts | Donut Chart | Table and Matrix Chart | TOP N Filter | Card Chart Filter
Class 13 – Donut Chart | Drill Through | Area Chart | Slicers
Class 14 – Drill Up and Down | Field Parameter | Numeric Parameter | Custom Tool Tips | Page Navigation | Hierarchies
Project Discussion (Meeting 01)
Project Discussion (Meeting 02)
Bonus Lesson (Part 03)
Introduction to Microsoft Excel
Class 15 – MS Excel – Text Functions | UPPER | LOWER | PROPER | CONCAT | RIGHT | LEFT | MID | FIND | Named Range | SUM | AVERAGE | MIN | MAX Functions
Class 16 – Excel Tables | COUNT | COUNTA | COUNTBLANK | COUNTIFS | SUMIFS | AVERAGEIFS Functions
Class 17 – Logical Functions | IF | SWITCH | LOOKUP | LET Function
Class 18 – LOOKUP Functions | VLOOKUP | INDEX MATCH | IFERROR | Data Validation in Excel
Oodles of Noodles (Power BI Project) Solution
Class 19 – INDEX & MATCH | Excel Tables | Amortization Table | Data Cleaning
Class 20 – Data Visualization with Excel Charts
Market Mindz (Power BI Project)
Class 21 – Pivot Tables | Data Analysis with Pivot Tables
Class 22 – Advance Pivot Tables | Label Filters | Grouping | Summarization | Show Value as | Calculated Fields | Slicers and Timelines
Class 23 – Pivot Table Case Studies
Class 24 – Excel Dashboards with Pivot Table
Class 25 – Dynamic Interactive Excel Dashboard (Part 01)
Class 26 – Dynamic Interactive Excel Dashboard (Part 02)
Class 27 – Introduction of Statistics | Descriptive Statistics (Frequency Distribution, Relative Frequency, Mean, Median, Mode)
Class 28 – Descriptive Statistics (Variability) – Range, MAX, MIN, Interquartile Range, Variance, Standard Deviation
Class 29 – Inferential Statistics (Correlation, Regression, Least Square Errors)
Class 30 – Introduction of SQL | Clauses (SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY)
Class 31 – SQL Queries – TOP N | TOP N PERCENT | OFFSET AND FETCH | DISTINCT | Aggregate Statements (SUM, AVG, COUNT, MAX, MIN) | Numeric Statements (ROUND, CEILING, FLOOR) | Date Statements (DATEDIFF, DATEADD, DATENAME, MONTH)
Class 32 – SQL Queries – Text Function (LEFT, RIGHT, LEN, CHARINDEX, UPPER, LOWER, CONCAT) | Logical Functions (AND, OR, IS NULL, NOT, IIF, Nested IIF, CASE Function) | CAST Function
Class 33 – SQL Queries (JOINS – INNER, LEFT, RIGHT, FULL) | UNION for Appending Queries
Class 34 – SQL Queries – Creating Views | LIKE Function | Sub Queries
Class 35 – SQL Project (PostgreSQL) – Setting Up Database | Question 01 – 08 Solved
Class 36 – SQL Project (PostgreSQL) – Questions 09 – 13 Solved
Class 37 – Python – Introduction of Python | Jupyter Notebook | Basic Arithmetic Operations | Data Types | Variables | Functions (Print, Type, Int)
Class 38 – Python – Magic Commands | Numeric Functions (Arithmetic Operator. Order of Operations, Max, Min, Sum, Round, Abs) | String (Arithmetic Operations, Concatenate, STR, Indexes, Slicing) | String Methods (Replace, Find, Upper, Lower, Strip, LStrip, RStrip, Join, Split)
Class 39 – Python – String Functions (F-String) | Logical Functions (Boolean Logic, Boolean Operators, Conditional Functions, IF, ELIF, ELSE, IN, AND, OR) | Sequence Data Types (Lists, List Indexing, List Slicing, APPEND, INSERT, DEL, COMBINE)
Class 40 – Python – Sequence Data Types (List Functions, Tuples, Range) | Loops (Loop over Items, Loop over Indices, Enumerate)
Class 41 – Python – Dictionaries (Creating, Adding, Modifying Dictionaries, Dictionaries Methods – KEYS, VALUES, GET, UPDATE, ITEMS, Nested Dictionaries, Looping through Dictionaries)
Class 42 – Python (Pandas for Data Analysis) – Dataframe Basics | Aggregation Functions | Logical Operations
Class 43 – Python (Pandas for Data Analysis) – Data Frame Basic | Creating Data Frames | Analyzing Data Frames | Data Wrangling/ Data Cleaning | Dealing with Blanks/ Missing Values
Class 44 – Python (Pandas for Data Analysis) – Replacing Values | Grouping Datasets | Concat and Appending Data Frames | Merging and Joining – Left, Right, Inner, Outer)
Student Ratings & Reviews
No Review Yet