Learning Path
Business Intelligence
Level
Intermediate
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)
Do you want to change your company’s reporting game forever? This course is for you.
In this hands-on guided training, You will learn how to use Excel BI tools including Power Query, Pivot Tables, Pivot Charts, Power Pivot, DAX, and Power 3D Map together to analyze huge amounts of business data fast and transform data into bottom-line results – no programming required!
Together, we’ll walk through the Excel BI workflow, and build robust Self Service BI solutions:
We first look at how to bring data together, clean, reshape, and combine data with ease using Power Query.
Next, we will move on to creating relationships between data tables and building out a data model. We then look at performing simple-to-sophisticated calculations using DAX. You will also explore the powerful features of Excel Power Map, KPI’s and adding key metrics.
Finally, we will create an interactive dashboard, combining all the excel BI tools.
Throughout the course, we will use many data sets as examples, as well as Milestone projects for practical simulations, to ensure that you have maximized your learning and can use the tools immediately when returning to the office.
What will you learn?
This course sheds light on advanced concepts and topics of data analytics and visualization in Microsoft Excel. You will learn
- How to import data from different sources
- Create mashups between data sources, and prepare data for analysis.
- How business calculations can be expressed using the DAX calculation engine.
- How the data can be visualized and used in dashboards
What do you get in the training?
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.
This course is also ideal for
- Data Analysts, MIS professionals, Business Analysts, Managers, Dashboard Makers, Business Intelligence Professionals, and students.
- Excel users who are looking for a transition into a business intelligence role.
- Analysts who are using Excel to analyze large datasets (>1mm rows), connect to external sources, or build relational data models.
- Candidates striving to learn Excel Data Analytics
- Professionals regularly working with a bulk of Business Data
- Anyone seeking to explore Excel’s business intelligence
- Professionals planning to enroll in the Excel BI Essentials Training must comply with the following prerequisites:
- Conceptual Understanding of Excel Analytic Tools
- Familiarity with Excel Tables, Pivot Charts, and Pivot Tables
- Excel Formulas & Basic Features including Excel IF Funcion, VLOOKUP, Excel Filter Basics, Excel Sort Data etc.
- Practical Experience of working in a Data Environment
- 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.
Recommendations:
- Computer with internet access, a webcam, microphone, and Zoom App.
- Microsoft Excel 2016+ or Office 365 (ideally for PC/Windows)
Intro to Excel BI
- Why do you need BI?
- Excel BI Component
- Excel BI Workflow
- Course Structure and outline
- Setting Expectations
POWER QUERY: Overview and Importing Basic Data
- What is Power Query?
- Installing Power Query in Excel 2010 / 2013
- Exploring the Power Query Editor interface
- The ‘Get’ Data User Experience
- Loading Data with Power Query (Get & Transform)
- TXT
- CSV
- Excel Spreadsheet
- Excel Table
- Access Database
- Web
POWER QUERY: Loading Data to Destination
- Changing Source Location
- Organizing (Grouping) Queries
- Loading Power Query Data to
- Table
- PivotTable Report
- PivotChart
- Only Create Connection
- Add to Data Model
- Changing load Destination for Existing Query
- Excel Power Query Load Settings
- Power Query Refresh Settings
POWER QUERY: Getting and Combining Multiple Files
- Appending queries together
- Connecting to a Folder as a Data Source
- CSV
- TXT
- Same workbook
- Multiple Excel Workbook
- Merge Queries and Join types
- Difference between Append and Merge Queries
- Viewing Query Dependencies
POWER QUERY: Simple Transformation Techniques
- M Language Building Blocks
- Common Data Transformations with Power Query
- Essential Transformations for (Most) Tables
- Different Data Types in Power Query
- Sorting & Intelligent Filtering
- Add or Modify Columns
- Formula Bar, Applied Steps & M Code
- Power Query Workflow
- Formula Bar
- Applied Steps
- Advanced Editor
- Apply user-friendly naming conventions to Applied steps, columns, and queries
- Basic Transformation Techniques
- Row Transformation
- Column Transformation
- Text Transformation
- Number Transformation
- Date & Time Transformation
- Understanding Data Structure
- The Proper Dataset
POWER QUERY: Pivot and Unpivot Column Data
- Transpose Query Data
- Pivot Column Command in Action
- Unpivot Columns Command in Action
POWER QUERY: Grouping Data
- Grouping and Aggregating Data
- Advanced-Data Grouping
POWER QUERY: Creating Custom Columns with Calculations
- Creating Conditional Columns
- Creating Custom Column Calculation
POWER QUERY: Modifying and Auditing Queries
- Reference vs Duplicate Queries
- Query Error Auditing
- Deleting Power Query Queries
- Copy Queries
- Backup Queries Result
POWER QUERY: Milestone Project 1
Your FIRST major milestone project. In this section, We will apply all the techniques you have learned in Power Query Section
PIVOT TABLE & PIVOT CHART: Excel Pivot Table and Power Query Data
- The Value of Pivot Tables
- What is a Pivot Table?
- The Pivot Table Design Life Cycle
- Where do Pivot Tables fit in?
- Creating your First Pivot Table
- Inserting a Pivot Table
- Connecting to Source Data
- Creating a PivotTable based on Power Query Data
- Understanding Course Case Study Requirements
- Pivot Table Interface
- Analyze and Design Tab
- The Field List & Areas
- Field List – Move, Resize, Close
- Field List – Layout
- Field List – Hide & Unhide
- Field List – Sort Order
- Modifying PivotTable Look & Feel
- Column Names & Number Formats
- Predetermined number formatting
- Design Tools
- Report Layouts
- Sub totals / Grand Totals
- Blank Rows
- Grouping Data
- Grouping Data by Text
- Grouping Data by Number
- Grouping Data by Date
- Enabling & Disabling Automatic Date Grouping
- Expand & Collapse buttons
- Pivot Table Styles
- Sorting & Filtering Pivot Tables
- Column Names & Number Formats
- Changing PivotTable Aggregations
- Summarize Values By
- Show Value as
- Calculated Fields
- Calculated Items
- GETPIVOTDATA
- Slicing and Filtering
- Slicers & Timelines
- Linking Slicers to Multiple Pivots Tables
- Report Filter Pages
- The “Show Details” Feature – Drill down to audit
- Slicers & Timelines
- Key PivotTable Options
- Autofit Column Width
- Preserving vs resetting formats
- Show / Hide filter buttons
- Conditional Formatting on Pivots
- Create Custom Conditional Formatting Rules
- Advanced Conditional Format
- Manage Conditional Formatting Rules
- Working with Pivot Charts
- Creating Pivot Charts
- Pivot Chart Elements
- Field Buttons
- Expanding & Collapsing
- Pivot Chart Formatting
- Making Pivot Charts Look Good
- Data Refresh & Security
- Refreshing & Updating Pivots
- Removing & Reviving Data from the Pivot Table Cache
PIVOT TABLE & PIVOT CHART: Milestone Project 2
Your SECOND major milestone project. In this section, We will apply all the techniques you have learned in Power Query and Pivot Table to analyze data from different data sources.
POWER PIVOT & DATA MODEL: Connecting to Data Sources
- Introduction to Data Model
- Importing Data into the Power Pivot Data Model window
- Linking Excel Tables
- Importing an Excel File
- Importing an Access file
- Removing and Filtering Fields
- WHEN Importing Data
- AFTER Importing Data
- Why Removing and Filtering Fields?
- Updating Data Source Location
- The Data Model Window
- Data View
- Diagram View
- Calculation Area
- Combination for Data Modelling: Power Query & Power Pivot Together
POWER PIVOT & DATA MODEL: Data Modeling and Relationships
- Defining the tables
- Data Modeling Mindset
- Multiple Tables in Data Models
- Normalized vs Denormalize Data
- Creating Table Relationships
- Relationship Criteria
- Creating Relationships
- Monitoring and Editing Relationships
- Understanding Relationship
- Relationship Cardinality
- Filter Direction
- Active Properties
- Schemas
- Flat or Denormalized Schema
- Star Schema
- Top-Down Schema
- Snowflake Schema
- Changing snowflake to star schema designs
- Managing and Preparing Data for Analysis
- Configure Table and Column
- Formatting and Sorting options
- Creating Hierarchies
- Hide Irrelevant Metrics
- Perspectives
POWER PIVOT & DATA MODEL: Getting Started with Power Pivot
- Creating a Power PivotTable
- Power Pivots vs. Normal Pivots
- Adjust Default PivotTable Options
- Best Practices For Data Models
- When to use Pivot Tables Vs. Power Pivots Vs. Power Query
POWER PIVOT & DAX: Calculated Columns
- Introduction to DAX Formula Language
- DAX Syntax and Data Types
- Calculated Column Properties
- DAX Column Calculations
- Text Functions
- IF() & ISBLANK() functions: Simple Logic
- Complex Logic (IF, AND, OR functions)
- SWITCH & SWITCH(TRUE)
- RELATED and RELATED TABLE Function
- Iterative Function (SUMX)
POWER PIVOT & DAX: Measures
- Introduction to DAX Measures
- Implicit vs Explicit Measures
- Creating Common Aggregates in DAX
- Understanding Filter Context
- CALCULATE() Function
- Cross-Table Measures
- Filtering Data in Measures
- Using DAX ALL() Function
- Understanding DAX Errors
- Evaluate Measures with KPIs
POWER PIVOT & DAX: Analyzing Data Over Time
- Simple Date Calculations
- Creating a Date Table in Power Pivot
- Adding Sort By Columns to the Date Table
- Adding the Date Table to the Data Model
- Applying Time Intelligence (YearToDate, QuarterToDate, MonthToDate)
POWER PIVOT & DATA MODEL: Milestone Project 3
Your THIRD major milestone project. In this section, we will use all of the techniques you learned in Power Query to clean and flatten the data table, as well as analyze the same data with Power Pivot, Data Model, and DAX.
POWER MAP: Getting Started with Power Map (3D)
- Power Map Fundamental
- Power Map Formatting
- Categories in Power Map
- Scene in Power Map
- Annotation & Text Box in Power Map
- Creating a 2D chart in Power Map
- Time Feature in Power Map
- Capture Screen and Video in Power Map
EXCEL BI: Milestone Project 4
Your FOURTH major milestone project. In this section, We will apply all the techniques you have learned in EXCEL BI to create an interactive dashboard.
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.