Power BI Training

Part – A Advanced Excel with VBA

Advanced Excel with VBA Course Content (Foundation for Power BI)

Module 1: Advanced Excel Foundations

• Data Cleaning & Preparation in Excel
  • Text-to-Columns, Flash Fill, Remove Duplicates
  • Find & Replace with wildcards
  • Data validation rules (drop-down lists, custom validation)
• Working with Advanced Functions
  • Lookup Functions: VLOOKUP, HLOOKUP, INDEX-MATCH, XLOOKUP
  • Logical Functions: IF, IFS, AND, OR
  • Text Functions: LEFT, RIGHT, MID, TRIM, TEXT, CONCAT, TEXTJOIN
  • Date & Time Functions: EOMONTH, DATEDIF, NETWORKDAYS
  • Dynamic Array Functions: FILTER, SORT, UNIQUE (Excel 365)
• Conditional Formatting (using formulas for advanced rules)
• Named Ranges and Structured References

Module 2: Advanced Data Analysis Tools

• PivotTables
  • Creating, grouping, and custom calculations
  • Calculated fields/items
  • Slicers and Timelines
• PivotCharts & Dashboards
  • Combining multiple PivotTables
  • Interactive dashboards with slicers
• What-If Analysis
  • Goal Seek, Data Tables, Scenario Manager
• Advanced Charting Techniques
  • Combo Charts, Waterfall, Gantt-style charts
  • KPI indicators with sparklines & conditional formatting

Module 3: Excel Power Tools (Mini Power BI inside Excel)

• Power Query (Get & Transform)
  • Importing data from multiple sources
  • Data cleaning & shaping (remove errors, split columns, merge queries)
  • Append vs Merge queries
• Power Pivot
  • Data Model basics
  • Creating Relationships between tables
  • Intro to DAX formulas in Excel (SUMX, CALCULATE, FILTER)

Module 4: VBA Automation

• Introduction to VBA & Macro Recording
• VBA Editor Overview (Modules, Procedures, Functions)
• Writing & Debugging VBA Code
  • Variables, Data Types, Loops, Conditions
  • Functions vs Subs
• Automating Repetitive Tasks
  • Automating report generation
  • Automating PivotTable refresh & formatting
• UserForms & Controls (Buttons, Combo Boxes, Checkboxes)
• Error Handling & Best Practices

Module 5: Integration & Reporting

• Importing/Exporting Data (CSV, Text, SQL, Web)
• Automating Data Refresh (Excel + VBA)
• Building Interactive Dashboards in Excel (with VBA buttons & forms)
• Preparing Excel Data Models for Power BI Import
• Best Practices for Transitioning to Power BI

⬆ Back to Top

Part – B Power BI

Power BI Course Content

1. 1. Introduction to Power BI
   • What is Power BI and its components (Power BI Desktop, Service, Mobile)
   • Benefits and use cases of Power BI
   • Difference between Power BI, Excel, and other BI tools
   • Overview of the Power BI ecosystem
   2. Getting Started
   • Installing Power BI Desktop
   • Understanding the interface: ribbons, panes, and views
   • Connecting to different data sources:
     • Excel, CSV, SQL Server, Azure, Web data, APIs
   • Understanding data types and tables in Power BI
   3. Data Preparation and Modeling
   • Data import vs DirectQuery
   • Cleaning and transforming data using Power Query (M)
     • Remove duplicates, filter, split columns
     • Merging and appending queries
   • Creating relationships between tables
   • Star schema vs Snowflake schema
   • Understanding cardinality and cross-filter directions
   4. Introduction to Data Transformation & DAX (Data Analysis Expressions)
   • Basics of DAX: calculated columns, measures, and tables
   • Common DAX functions:
     • Aggregation: SUM, AVERAGE
     • Logical: IF, SWITCH
     • Date and Time: DATEADD, DATESYTD, YEAR, MONTH
     • Filter and context functions: CALCULATE, FILTER, ALL
   5. Data Visualization
   • Types of visualizations:
     • Bar/column charts, line charts, pie charts, matrices
     • Maps: shape maps, ArcGIS maps
     • KPI visuals, cards, and gauges
   • Custom visuals from marketplace
   • Conditional formatting
   • Drillthrough and drilldown techniques
   • Bookmarking and storytelling with visuals
   6. Reports and Dashboards
   • Difference between a report and a dashboard
   • Creating interactive reports
   • Using slicers and filters
   • Tooltips and report page tooltips
   • Publishing reports to Power BI Service
   • Pinning visuals to dashboards
   7. Power BI Service
   • Workspaces, Apps, and content packs
   • Sharing dashboards and reports
   8. Hands-on Projects
   • TBD
   • Dashboard
   • Real-world datasets from open sources

   ⬆ Back to Top

Part – C Tableau

Tableau Course Content

Module 1: Introduction to Tableau

• What is Tableau? Why use it for BI?
• Tableau Products (Desktop, Prep, Server, Public, Online)
• Tableau Architecture & Workflow
• Connecting to Data Sources (Excel, CSV, SQL, Cloud Sources)
• Tableau Interface: Workspace, Menus, Data Pane, Shelves

Module 2: Working with Data in Tableau

• Data Types & Field Properties
• Dimensions vs Measures
• Discrete vs Continuous fields
• Data Joins, Blends, and Relationships
• Data Extracts vs Live Connections
• Data Preparation (filters, sorting, grouping, bins, hierarchies)

Module 3: Basic Visualizations

• Bar Charts, Line Charts, Area Charts
• Pie Charts, Treemaps, Heatmaps
• Cross Tabs & Highlight Tables
• Dual-axis Charts & Combo Charts
• Show Me Panel & Recommended Visuals

Module 4: Intermediate Visual Analytics

• Filters (Extract, Context, Relative, Top N)
• Parameters (dynamic inputs for users)
• Sets & Groups (static vs dynamic sets)
• Advanced Sorting & Custom Hierarchies
• Calculated Fields (row-level, aggregate)
• Table Calculations (running totals, percent of total, moving average)

Module 5: Advanced Visualizations

• Waterfall Charts, Bullet Graphs, Gantt Charts
• Funnel Charts, Donut Charts, Word Clouds
• Geographic Mapping (filled maps, symbol maps, custom geo data)
• Custom Shapes & Images in Visuals
• Animation & Storytelling with Visuals

Module 6: Dashboards & Stories

• Designing Interactive Dashboards
• Actions in Dashboards (Filter Action, Highlight Action, URL Action)
• Dashboard Layouts (fixed vs responsive)
• Best Practices for Dashboard Design
• Creating Stories in Tableau (narrative storytelling with visuals)

Module 7: Advanced Calculations & Data Modeling

• Level of Detail (LOD) Expressions
  • FIXED, INCLUDE, EXCLUDE
• Conditional Calculations
• Advanced Date Calculations
• Aggregation & Granularity in Data
• Data Modeling with Relationships

Module 8: Tableau Server & Collaboration

• Publishing Workbooks to Tableau Server/Online
• Managing Permissions & Access
• Scheduling Data Extract Refreshes
• Version Control & Collaboration
• Tableau Public vs Enterprise Deployment

Module 9: Tableau Prep (Optional – for Data Preparation)

• Introduction to Tableau Prep Builder
• Cleaning, Splitting, Pivoting, and Joining Data
• Creating Data Flows
• Output Options and Integration with Tableau Desktop

Module 10: Real-World Projects & Case Studies

• Sales & Revenue Analysis Dashboard
• HR Analytics (Employee Attrition, Headcount Trends)
• Financial KPI Dashboard
• Supply Chain & Inventory Management Dashboard
• Marketing Campaign Effectiveness Dashboard
• Customer Segmentation & Retention Analysis

⬆ Back to Top