Learning Path
Business Intelligence
Level
Advanced
Application Deadline
Closed
Start Date
To be Announced
Instructor-Led Live Session
Live Session Recording
Expert Support
Case Studies & Projects
Dedicated Resource Portal
Limited slots available
06 weeks (Sat & Sun)
08:30 PM to 10:30 PM
(12 Sessions)
Microsoft Excel has a lot of features, but it’s not always the most useful tool for data analytics. That’s where Power Pivot and DAX come in. Power Pivot and DAX are add-ons to Excel that provide advanced calculation tools to help you make sense of big data sets.
This course is designed to comprehend the fundamental and complex functions of Power Pivot and DAX.
The goal of this course is to give students the ability to understand the WHY behind the process.
Prerequisite
Attendees must have Excel proficiency equivalent to our Excel for Everyone course
Course Content
You will get
- Exercise and Project files
- Pre and Post Assessment
- Quizzes and Homework assignments
- 1-month access to the resource portal
- HD Recording of all live sessions
Certificate of Completion
You will be awarded a course completion E-Certificate by TechTAR Solutions after fulfilling the course requirements.
- Excel users who are looking for an introduction to advanced analytics and data modeling tools, or preparing to transition into a business intelligence role
- Excel users analyze large datasets (>1mm rows), connect to external sources or build relational data models
- Anyone seeking to explore Excel’s business intelligence capabilities
- Classes will be interactive throughout & LIVE on Zoom. You would be able to ask questions & share views with the Technical Assistant, Trainer & class other participants.
Experience with Excel PivotTables and formulas is strongly recommended
Recommendations:
- Computer with internet access, a webcam, microphone, and Zoom App.
- Microsoft Excel 2016+ or Office 365 (ideally for PC/Windows)
What is Power Pivot?
- The Need for Power Pivot
- Enabling Power Pivot in Excel
- Pivot Table Vs Power Pivot
- When to use Standard Pivot Tables Vs. Power Pivots
- Creating First Report
Explore the Power Pivot Workspace
- Opening the Power Pivot Window
- Relationship
- Creating a “Power” Pivot Table
- Pivot Table Vs Power Pivot – Field Pane
- Manage Data Model Button
- Power Pivot Ribbon Tab
- Relationship
- Viewing the Data Model
- Normal vs Advanced Mode
- Calculation Area
- Calculation Options
- Tables Properties
- Show Implicit Measures
- Detect Relationships
- Settings
Loading the Data in Power Pivot
- Import Data
- from Access
- using a Text file
- using an Excel File (Workbook)
- from Excel Table (Worksheet)
- from other sources
- Removing and Filtering Fields WHEN Importing Data
- Removing and Filtering Fields AFTER Importing Data
- Copying and Pasting Data
- Existing Connections
Creating and Optimizing Data Models
- What is Data Model
- Data Modeling Mindset
- Data Tables vs. Lookup Tables
- Database Normalization and Denormalization
- Proper Data Structure
- Creating Table Relationships Manually
- Handling Multiple Fact Tables
- Creating Table Relationships Automatically
- Modifying Table Relationships
- Flat vs Star vs Top-Down vs Snowflake Schema
- Relationship Cardinality
- Filter Direction
- Hiding Tables / Fields from Client Tools
- Defining Hierarchies
- Active vs. Inactive Relationships
- Optimizing Data Structures
- Best Practice for Data Models
Calendar Tables
- Need for Calendar Table
- Creating a Calendar Table in Data Model
- Sorting Calendar Table
- Creating a Calendar Table in Excel
- Creating a Dynamic Calendar in Power Query
Create a Power Pivot Table
- Create a PivotTable
- All Vs Active View
- Pivot Table Field Setting
- Analyze
- PivotTable
- Active Field
- Group
- Filter
- Data
- Actions
- Calculations
- Relationships
- Tools
- Show
- Design
- Layout
- PivotTable Style Options
- PivotTable Styles
- Adjust Default PivotTable Options
- Sort
- Expand / Collapse
- Drill Down / Drill Up
- Drill through extract 1000+ rows
- Autofil Column widths on update
- Filter and Slicers
- Show Items with no data on rows/columns
- Include filtered items in totals
- Convert to Formula
MILESTONE PROJECT 1: Handling a Big Flat Table
In this section, we will apply everything we learned in the previous module to analyze a big flat table data file. To complete the task, we will combine Power Pivot and Power Query.
DAX Language
- Understanding DAX
- DAX Syntax & Data Type
- DAX Operators
- Calculated Column VS Measures
Calculated Columns in the Data Model
- Calculated Columns vs Custom Columns in Power Query
- Common DAX Functions for Calculated Columns
- TEXT Functions: LEFT, RIGHT, CONCATENATE, FORMAT, LEN, LOWER, UPPER, REPT, TRIM
- DATE and TIME Functions: YEAR, MONTH, DATEDIFF, EOMONTH, EDATE, WEEKDAY, WEEKNUM, TODAY, NOW
- LOGICAL Function: IF, SWITCH
- NAVIGATION Function: RELATED and RELATEDTABLE
- Handling Errors
Measures in the Data Model
- Implicit vs Explicit Measures
- Measure vs Calculated Fields
- Creating Measures, Syntax
- Adding Measures in Data Model
- Adding Measures in Excel (Power Pivot Tab)
- Validating Measures
- Interpreting Errors
- Finding, Editing and Deleting Measures
- Displaying Measure in Pivot Tables
- Value Field Settings – Names
- Creating dedicated Measures Tables
How Measures Work?
- Understanding Evaluation Context
- Row Context
- Filter Context
Common DAX Functions
- Basic MATH Functions
- DIVIDE, ROUND, ROUNDUP, SUM
- Basic STATISTICS Functions
- COUNT, COUNTA, DISTINCTCOUNT, COUNTROWS, COUNTBLANK, MAX, MIN
- TOTALYTD, TOTALMTD
- VALUES Function
- Creating Measure KPI Slicer
Iterator Functions
- Understanding Iterative Function
- SUMX
- COUNTX
- AVERAGEX
- MAXX
- MINX
- RANKX
- CONCATENATEX
Understanding CALCULATE Function
- Calculate Function Syntax
- Adding filter Context with FILTER Function
- Adding filter Context with ALL Function
- Variables in DAX Functions
Creating Reports and KPI
- Using KPI in Power Pivot
- Conditional Formatting
MILESTONE PROJECT 2: Sales Dashboard
In this section, we will apply everything we learned in the previous module to create a dynamic interactive dashboard using Power Query, Power Pivot, Pivot Charts, and Excel Tools.
Table Functions
- FILTER Function
- ALL Function
- VALUES Function
- HASONEVALUE Function
Time Intelligence Functions
- DATESYTD
- PREVIOUS YEAR
- DATEADD
- SAMEPERIODLASTYEAR
- FIRSTDATE
- LASTDATE
- ENDOFMONTH
- STARTOFYEAR
- DATESBETWEEN
Advanced DAX Functions
- Advanced DAX Functions and Patterns
- KEEPFILTERS
- Semi Additive Measures
Individual Price
PKR 12,000
Per Person
Total charges for complete training
Group of Two
PKR 10,800
Per Person
Avail 10% off on a Group of two
Group of Three
PKR 10,200
Per Person
Avail 15% off on a Group of three
Group of Four or more
PKR 9,600
Per Person
Avail 20% off on a Group of four or more
Course Reviews
We have engaged TechTAR Solutions for a number of session in Excel and Power BI for our employees. Sohail Merchant is wonderful to deal with when making the bookings and the high level of experience the trainers hold is evident. We will definitely use them again in the future.!
I was tasked with seeking out a company to assist us in recreating our department’s appraisal Matrix and after meeting with TechTAR Solutions’ consultant, I knew they were the right fit to help get the job done! Their expertise and understanding of our needs made the whole process so simple.
Sohail Merchant from TechTAR Solutions’ [ explained the theory very well, also gave us sufficient room to ask questions and clarify our queries. Enough resources were provided to read and increase our knowledge on individual capacity..
Lots of useful information, Ideas & strategies with many practical examples. I learned a lot throughout the course & implementing it in my business. Sohail Merchant from TechTAR Solutions’ expertise gave me an amazing learning experience. Thank you.
The professionalism and Personalized service that we got from TechTAR Solutions was unlike any other company we worked with and they made us feel so comfortable by their natural communication and let’s do it attitude. Incredibly trustworthy and high value! Thankyou!
Please keep developing the products and solutions to make our lives more easier and work to be more focused efficient targeted and productive. Appreciate the contributions of the entire community in this.