Data Analytics classes Fees

In this lesson, we will explore a real-world data analytics use case where data is scattered across Excel files, CSVs, and a SQL database. We’ll demonstrate how to use Advanced Excel, Power Query, SQL, Tableau, and Power BI to create a seamless data solution—from extraction and transformation to visualization and actionable insights.

retail company wants to analyze its sales performance. The data is stored across multiple formats:

1. Excel Files: Monthly sales data by region.
2. CSV Files: Product master data.
3. SQL Database: Customer purchase history.

The goal is to create an interactive dashboard that provides insights into:

• Regional sales performance.
• Top-performing products.
• Customer purchasing behavior.

Step 1: Data Extraction

• Advanced Excel: Quickly review and clean the Excel and CSV files using formulas, filters, and conditional formatting.
• SQL: Query the database to extract relevant customer purchase data using SELECT statements and JOINS.

Step 2: Data Transformation

• Power Query: Combine and transform the Excel and CSV files into a unified format. Merge this data with the SQL output, removing duplicates and handling missing values.

Step 3: Data Modeling

• SQL: Use database tools to optimize data structure, applying relationships and indexing for efficient querying.
• Power Query/Power BI: Create relationships between datasets and build calculated columns where necessary.

Step 4: Data Visualization

• Tableau: Design an interactive dashboard showing sales trends, regional comparisons, and top products. Use advanced visualization techniques like heat maps and bullet charts.
• Power BI: Create dynamic dashboards with slicers and drill-through capabilities for deep insights into customer behavior and sales metrics.

.)

This table is in the range A1:D4. You want to find the salary of employee with ID 102.

Excel

=VLOOKUP(102, A1:D4, 4, FALSE)

• 102: The Employee ID (your lookup_value).
• A1:D4: The table containing the employee data (your table_array).
• 4: The fourth column of the table contains the Salary (your col_index_num).
• FALSE: You want an exact match for the Employee ID.

How it Works:

1. VLOOKUP looks in the first column of A1:D4 (the Employee IDs) for the value 102.
2. If it finds an exact match, it goes across to the fourth column (because col_index_num is 4) in the same row where it found the match.
3. It returns the value in that cell (the Salary, $60,000).

Key Rules (Recap):

• Lookup value in the first column: The value you're searching for must be in the first column of the table_array.
• Sorted for approximate match: If you use TRUE or omit range_lookup, the first column must be sorted in ascending order.
• Exact or approximate match: Use FALSE for exact matches (recommended) and TRUE for approximate matches.
• Column index number: Specify the correct column number to retrieve the desired value.

VLOOKUP vs. HLOOKUP:

• VLOOKUP: Searches vertically in the first column.
• HLOOKUP: Searches horizontally in the first row.

Use VLOOKUP when your lookup values are in the first column, and HLOOKUP when they are in the first row. In most cases, VLOOKUP is more commonly used because data is often structured in columns.