Become an AI-Powered Certified Excel & Power Bi Data Analyst

You will learn about data analysis tools and methods as data analysis students.

Data Cleaning

What you’ll discover:
1. Handling missing values and eliminating duplicates.
2. Standardizing data formats (text, integers, and dates).
3. Fixing inconsistent and incorrect data entry.
4. Divide and combine columns as necessary.

Functions of Text
What you’ll discover:
1. Overview of text functions: Substitute, Left, Right, Trim, etc.
2. Use sample datasets to test the text functions.

Fundamental Features
What you’ll discover:
1. Overview of the SUM, AVERAGE, MIN, and MAX basic arithmetic functions.
2. Use sample datasets to practice applying the fundamental arithmetic operations.

Background

Netflix wants to improve its content recommendation system and enhance customer satisfaction by making sure its content library data is accurate, organized, and analysis-ready. The company discovered several data quality issues, including missing values, duplicate entries, and incorrect classifications, which could negatively impact the effectiveness of its recommendation engine.

Task Overview

As a data analyst working for Netflix, your role is to clean and prepare the dataset for deeper analysis. The dataset contains details about movies and TV shows, including titles, genres, release dates, ratings, and user engagement statistics.

Responsibilities
Identify columns that contain missing values.
Apply suitable methods for handling missing data, such as replacing values, using defaults, or removing unnecessary rows or columns.
Detect and eliminate duplicate records to ensure data uniqueness and accuracy.
Review and fix inconsistencies in data formats, including dates and rating structures.
Standardize categorical information, such as genre labels and country names, for uniformity across the dataset.
Validate numeric fields, such as duration and user ratings, to confirm they fall within acceptable ranges.
Examine dates and other time-related information to ensure logical consistency throughout the dataset.

What you’ll discover

• IF function: Basic syntax and usage.
• IF functions can be nested to create more intricate conditional logic.
• An overview of the IFS function as a nested IF substitute.

Advanced Logical Operations

ACTIVITIES:

• Recognizing and using the SUMIF, COUNTIF, and AVERAGEIF functions.
• Utilize SUMIF with several criteria (by merging with SUMIFS).
• Utilize COUNTIF with several criteria (by merging with COUNTIFS).
• Combine AVERAGEIF with AVERAGEIFS to use AVERAGEIF with various criteria.

Background

A global retail company aims to improve its marketing campaigns in order to boost sales and enhance customer engagement. Over the past year, the marketing team has gathered sales data from multiple campaigns, including customer information, transaction records, and campaign-related details. Your task is to analyze this data using logical functions to uncover trends, segment customers, and provide recommendations for improving future marketing efforts.

Responsibilities

1. Apply logical functions to categorize customers based on factors such as age group, purchase value, and loyalty program participation.
2. Identify high-value customers who regularly make large purchases or actively respond to marketing campaigns.
3. Evaluate the performance of each campaign by calculating metrics such as total sales, average purchase value, and transaction count.
4. Use logical functions to filter and compare campaign performance under different conditions, including region, discount usage, and sales channels.
5. Analyze sales patterns to identify trends such as peak buying periods, most popular product categories, and regional buying preferences.
6. Compare the purchasing behavior of loyalty program members with non-members using logical analysis techniques.
7. Provide recommendations for improving future campaigns, such as targeting profitable customer segments, refining discount strategies, and investing more in high-performing regions or sales channels.

What you’ll discover

• Overview of VLOOKUP
• using minimal datasets to practice basic VLOOKUP.
• For more sophisticated capabilities, nest VLOOKUP inside other formulas.
• Investigate different lookup functions, such as MATCH and INDEX.
• discovering how to develop effective lookup formulas by combining INDEX and MATCH.
• recognizing the benefits of MATCH and INDEX over VLOOKUP.

Background

A retail company wants to improve its sales reporting process by integrating data from multiple sources to gain better insights into overall sales performance. The company stores sales transactions, customer records, and product information in separate datasets. Your task is to use lookup functions to combine these datasets and carry out a detailed sales analysis.

Responsibilities

1. Use lookup functions to connect and merge data from the Sales Transactions dataset with the Customer and Product datasets.
2. Build a detailed sales report that includes customer names, product names, product categories, customer regions, and loyalty program status for every transaction.
3. Apply lookup functions to organize sales data according to product categories.
4. Use lookup functions to segment sales information by customer region and loyalty membership status.
5. Analyze sales performance across different regions and compare the buying habits of loyalty program members with non-members.
6. Utilize lookup functions to associate sales records with their corresponding product suppliers.
7. Calculate key sales metrics such as average order value, total quantity sold, and revenue generated per customer using the combined dataset.
8. Recommend strategies for attracting high-value customers, improving inventory management, and strengthening supplier partnerships.

What you’ll discover

• Create the structure and layout of the dashboard.
• For interactivity, use slicers, pivot tables, and pivot charts.
• Add metrics and key performance indicators (KPIs).

PRESENTATION

• Delivering presentations in groups
• Get comfortable giving the presentation.
• Get input and make changes.

Background

A leading bike retail company wants to strengthen its business strategy by analyzing its bike sales data. The company aims to uncover sales trends, identify important customer segments, and improve inventory management. Your task is to examine the provided sales data, develop an interactive dashboard, and present valuable insights and recommendations to the management team.

Responsibilities

1. Analyze overall sales trends over time, including peak sales periods and seasonal patterns.
2. Assess the performance of different bike categories based on sales quantity and revenue generated.
3. Segment customers according to demographics such as age, gender, and region to better understand purchasing behavior.
4. Design an interactive dashboard that clearly showcases the major findings from the analysis.
5. Include important key performance indicators (KPIs) such as total sales, average order value, best-selling bike categories, customer demographic insights, and regional sales performance.
6. Add dashboard filters that enable users to explore specific time ranges, regions, or customer groups in greater detail.
7. Utilize different visualization techniques, including bar charts, line charts, pie charts, and heat maps, to display the data in a clear and engaging manner.
8. Provide recommendations on which bike categories should receive greater focus in marketing and inventory planning based on customer demand and sales performance.

ACTIVITIES

• Recognizing different sources of info.
• Importing data from a variety of sources (such as databases, Excel, CSV, etc.) into Power BI.

Data Transformation and Cleaning

ACTIVITIES

• Cleaning data with Power Query
• Utilizing Data Types and

Data Modeling

ACTIVITIES

• Establishing connections between tables.
• Defining foreign and primary keys.

Background

Shoprite, a retail chain with several store locations, has collected a significant amount of sales data over the past year. However, the dataset is unorganized and contains inconsistencies, duplicate entries, and missing values. The management team wants to use this data to better understand sales performance, discover trends, and make data-driven business decisions. Your task is to clean, transform, and analyze the sales data in order to generate meaningful insights and recommendations.

Responsibilities

• Identify missing values within the dataset and determine the most suitable methods for handling them, such as using mean or median replacement, forward/backward filling, or removing records with excessive missing data.
• Detect and eliminate duplicate records to ensure all sales transactions are unique and accurate.
• Verify that the Total Sales Amount is correctly calculated by multiplying Quantity Sold by Unit Price, and fix any inconsistencies discovered.
• Organize products into broader product categories where necessary and generate summarized sales reports by category.
• Analyze overall sales performance over time, including peak sales periods, seasonal trends, and store performance based on revenue and sales volume.
• Examine customer purchasing behavior to identify high-value customers and evaluate the effect of discounts on sales performance.
• Recommend products or categories that should be prioritized in marketing and inventory management based on customer demand and sales trends.

ACTIVITIES

• Making simple visualizations, such as pie charts, bar charts, and line charts.
• Using visualizations to understand data distributions and trends.
• DAX Formulars

ACTIVITIES

• Use DAX measurements to provide summary statistics.
• DAX is used for calculated columns and sophisticated measures.
• Using DAX for creating Calculate Functions
• Dashboard Creation

ACTIVITIES

• Building Interactive Dashboards
• Making important visualizations and incorporating them into the dashboard.
• Adding interactivity with slicers, filters etc.

This unit will teach you how to incorporate generative AI into data analytics workflows, which will greatly improve organizations’ capacity to obtain actionable insights, enhance data quality, and make better decisions. Artificial Intelligence (AI) is currently influencing processes in all businesses and industries.

You will complete three capstone projects and fifteen to twenty mini-projects in this course to get ready for data-related careers. We will provide you all the abilities and self-assurance you need to land any data analysis job opportunity since we know exactly what you need to acquire a job.