Data Analytics using BI Tools

Week 1: Excel for Data Analysts

• Data Cleaning & Processing: Mastering Text-to-Columns, Flash Fill, and removing duplicates; handling inconsistent data formats and nested logical functions (IF, IFS, AND, OR).

• Advanced Lookup Functions: Transitioning from VLOOKUP/HLOOKUP to XLOOKUP and INDEX-MATCH for robust, non-volatile data retrieval.

• PivotTables & PivotCharts: Summarizing large datasets, grouping data, and using Slicers/Timelines for interactive filtering.

• Power Query (M Language): Introduction to “Get & Transform”—automating repetitive data cleaning tasks and merging multiple workbooks or CSVs.

• Statistical Analysis: Using the Data Analysis Toolpak for descriptive statistics, correlation matrices, and basic forecasting.

Week 2: SQL for Data Querying and Management

• Relational Database Basics: Understanding schemas, primary/foreign keys, and normalization; working with PostgreSQL or MySQL.

• Core Querying: Mastering SELECT, FROM, JOIN (Inner, Left, Right, Full), and UNION to combine data from disparate tables.

• Aggregation & Filtering: Grouping data with GROUP BY and filtering aggregated results with HAVING; using CASE WHEN for conditional logic.

• Common Table Expressions (CTEs): Writing readable, modular code using WITH clauses instead of complex subqueries.

• Window Functions: Using RANK(), DENSE_RANK(), and LEAD/LAG for time-series analysis and row-level comparisons within partitions.

Week 3: Business Intelligence with Power BI (Foundations)

• Data Connectivity: Connecting to SQL databases, Excel, and Web sources; managing the “Data vs. Report” view.

• Data Modeling (Star Schema): Designing efficient models; understanding one-to-many relationships and the importance of Fact vs. Dimension tables.

• DAX (Data Analysis Expressions) Basics: Creating calculated columns vs. measures; understanding Implicit vs. Explicit measures.

• Foundational Visuals: Building bar charts, line graphs, and treemaps; setting up drill-throughs and report-level filters.

Week 4: Advanced Power BI & Tableau Essentials

• Advanced DAX & Time Intelligence: Using CALCULATE, ALL, and FILTER; calculating Year-over-Year (YoY) growth and Year-to-Date (YTD) totals.

• Power BI Service: Publishing reports, setting up Row-Level Security (RLS), and scheduling automatic data refreshes.

• Introduction to Tableau: The Tableau ecosystem (Desktop vs. Server); understanding Blue vs. Green pills (Dimensions vs. Measures).

• Tableau Specialized Charts: Creating Dual-Axis charts, Heat Maps, and Bullet Graphs; using Sets, Groups, and Parameters for user interactivity.

• Capstone Dashboard: Building a dual-tool portfolio project—taking a raw dataset and creating a strategic dashboard in Power BI and a tactical visualization in Tableau.