Modules

1) Python
2) Python Libraries
3) Statistics & Mathematics
4) Domain Knowledge
5) Advanced Excel
6) SQL
7) Power BI

Python Programming

Introduction | History | What can Python do? | Variables | Comments | Print Statement |Operators
| Data Types| Conditions | Loops | Functions | Break, Continue and Pass Statements | List | Tuple |
Set | Dictionaries | Keywords | Identifiers

Python Libraries

Numpy |Pandas | matplotlib | Scipy | math | random | re
Statistics & Mathematics
• Descriptive Statistics
• Inferential Statistics
• Probability

Power BI

Module 1:

Power BI for Data Analysts (Visualization) 1.1:
Introduction to Power BI

• Introduction to Power BI: What is Power BI?
• Power BI Desktop; The First Experience
• Power BI Website: You’ll Need Just a Web Browser
• Introduction to Power BI Components: Power Query, Power Pivot, Power View, and
Power Q&A.
: Getting Data and Transformation
• What is Power Query: Introduction to Data Mash-Up Engine of PowerBI
• Different versions of Power Query
• Power Query Introduction
• Query Editor
• Get Started with Power Query
• Power BI Get Data from Excel: Everything You Need to Know
• What is the Role of Power Query in a Power BISolution

1.3: Data Modelling and DAX

• Power BI xVelocity engine basics and concepts
• Relationships in Power BI
• Hierarchies and Formatting
• Sorting by other columns
• Date Table
• Introduction to DAX
• Calculated Columns, Measures, and CalculatedTables

1.4: Data Visualization
• Power BI Desktop Visualization
• Formatting Visuals in Power BI Desktop
• Basic Charts and Visuals in Power BI
• Sorting, Filtering, and categorization
• Custom Visuals in Power BIDesktop
• Maps and Geo-Spatial Visualization

1.5: Power BI Service
• Basic Sharing and using workspaces in Power BI web site
• Dashboard vs. Report
• Power Q&A
• Mobile Dashboard Design
• Row Level Security
• Schedule Refresh vs. Other types of connections
• Gateway’s Role in the Service
: Visualization Basics
• Importance of Visualization
• Basic Charts; Bar and Column Charts
• Clustered vs. Stacked Charts
• Q&A for Reports
• Quick Measures in Power BI
• Drill Down and Up using visuals and hierarchies
• Pages

1.7: Slicing and Dicing
• Filters
• Scopes of Filters: Visual Level, Page Level, Report Level
• Modes of Filtering: Basic, Advanced, Relative, Top N
• Drill through Page Filter: Creating master-details page scenario in Power BI
• Slicers
• Types of Slicers; Date, Text, Number
• Slicer Formatting
• Syncing Slicer between Pages

1.8: Advanced Visualization in Power BI
• Report Page Tooltip
• Controlling the interaction of Visuals in a page
• Designing Power BI reports for Mobile
• Grouping and Binning
• Bookmark; saving the state of a page
• Buttons; action items
• Selection pane; visibility control in Power BI
• Golden triangle; Bookmark, button, and selection pane
• Color theme or pallets in PowerBI

1.9: Best Practice Scenarios of Using Visuals
• Line Chart; Trend Analysis
• Combo Charts; Different Scales in one graph
• Ribbon Chart; Ranking
• Waterfall Chart; Cashflow
• Scatter Chart; Storytelling with the data
• Grouping charts: Pie, Donut chart, and Tree map charts
• KPI visual
• Gauge
• Card Visual
• Table and Matrix; showing the numbers with conditional formatting

1.10: Geo Location Visualization: Map Visuals
• Map Visual; Using Bing to find spots
• Fine Tuning Address Details
• Map Visual; Using Latitude and Longitude
• Filled Map, Regions and Polygons
• Data Category importance for the filled map
• Shape Map; Having custom map visual
• ArcGIS Map: Map visual which can do everything

1.11: Custom Visuals
• Adding and using a custom visual
• Infographic Designer
• Tachometer Gauge
• Map custom visuals
• Certified custom visuals
• Hierarchy Slicer
• Play Axis
• R Custom Visuals

Module 2: Power BI for Data Wrangler/Transformer (Data Transformation)
: Get Data
• Introduction to Power Query
• Query Editor
• Get Data from Web
• Basic Transformations
• Get Data from Excel
• Use First Row as Headers / Use Headers as First Row
• Get Data from SQL Server

2.2: Data Types and Data Structures
• Base data structures in Power Query
• Data Types in Power Query
• Query Operations
• Enable Load, Performance Boost
• Query Operations; Duplicate, andReference

2.3: Combine Queries
• Dimensional Modelling; Designing the data model
• Append, creating a single big query of the same structure
• Merge: Joining queries when the structure is different
• Join types in Merge
• Tips to consider after Merge orAppend

2.4: Better Power Query Editor Experience
• Groups; Folders in Query Editor
• Steps Operations
• Splitting query steps
• Moving steps up or down
• Add as new query / Drill Down
• Be Careful of Actions;Undo!

2.5: Reducing Number of Rows; Filtering
• Row Operations; Removing rows
• Row Operations; Keeping rows
• Remove/Keep Errors
• Remove/Keep Duplicates
• Using Remove/Keep combination for troubleshooting report
• Filtering based on Individual values
• The dilemma of the basic filtering
• Advanced Filtering
• Sorting

2.6: Column Operations
• Column Operations
• Choosing Columns
• Removing Columns
• Data Type Change
• Locale consideration for the data type
• Replace Values
• Fill Down/Up, Very Useful for Excel

2.7: Table Transformations
• Group By; Changing the granularity of the data table
• Group by Advanced
• Scripting and Group by; First and Last item in each group
• Transpose; rows to columns and reverse
• Pivot; changing the name-value structure to columns
• Unpivot; changing the budget column structure torows

2.8: Text Transformations
• Split Column by Delimiter
• Split Column by number of Characters
• Split into rows instead of columns
• Merge (Concatenate)
• Format
• The difference between Clean and Trim

2.9: Numeric Transformations
• Standard transformations: Divide, Integer-divide, Multiply, Add etc.
• Scientific transformations; logarithm, power square, etc.
• Statistics transformations.
• Rounding
• Information functions; Is Even, Is Odd, and Sign.
• Dealing with faults in Numeric calculations

2.10: Date and Time Transformations
• Date Transformations (Year, Month, Quarter, Week, etc.)
• Extending Fiscal Date Column
• Time Transformations (Hour, Minute, Second, etc.)
• Adding Time/Date banding
• Duration Transformation and Data Type
• Age Calculation
• Local Date or Time
• Time zone consideration for PowerBI

2.11: Structured Column Transformations
• What is a Structured Column?
• Expand
• Aggregate
• Expand and Aggregate: PerformanceConsideration

2.12: Add Column Transformations
• Add Column vs. Transform?
• Add Column with a Transformation
• Index Column: Row Number
• Conditional Column
• Add Column by Example; When you don’t know which transformation to use
• Add Custom Column: Generic

2.13: Functions and Parameters; Dynamic Power Query
• Defining Parameters
• Using Parameters in an existing query
• Advanced GUI for parameters
• Creating Function from a query
• Invoking the sample function
• Add Column Transform: Invoke Custom Function
• When the advanced GUI does not exist

Module 3: Power BI for Data Modeler (Data Modelling and DAX)
: Power BI Modelling 101
• Basics of Modelling in Power BI
• The step before this: Data Preparation
• Relationships in Power BI; Filter propagation
• Direction of Relationship
• Be Careful of both directional relationship
• Active or In-active relationships
• Relationship based on multiple Columns
• Role Playing Dimension
• Formatting
• Hide/Unhide Columns
• Hierarchy Definition in Power BI
• Sort by Column

3.2: Introduction to DAX
• Syntax of DAX
• Naming in DAX
• Operators and Operands
• Logical Operations
• Data Types in Power BIModel
• Overview of Functions inDAX
• Variables
• DAX or M? When to use Which?

3.3: Calculations in Power BI
• Calculated Column; Row by Row
• Measure; Single Output
• Calculated Table; A derived table
• Calculated Column? Maybe a good candidate for Power Query transformation
• Measures are Dynamic

3.4: Aggregation and Iterator Functions
• Aggregation Functions
• Implicit Measures vs. Explicit Measures
• Sum of an Expression: SumX
• Iterator Functions
• Difference between SUM and SUMX

3.5: Filter Functions• ALL
• Filter Functions to be used inside other functions
• Examples of using ALL function
• ALL and SUMX; Percentage Calculation
• AllExcept
• Filter Function: Custom Filter
• Values/Distinct; getting a unique list of values

3.6: Evaluation Contexts and Conditional Sum
• Row Context
• Filter Context
• Exception for Row Context
• Exception for Filter Context
• Calculate Function
• Conditional Sum; Three ways of Implementing; pros and Cons
• Variables in DAX and using them for debugging

3.7: Relationship Functions
• Related: Many to one
• RelatedTable: Sub table that can be used as a filter
• CrossFilter: Changing Direction of relationship
• UseRelationship: using an inactive relationship
• TreatAS function

3.8: Time Intelligence Functions
• Choosing the Date Table: Built-in or Customized Date Table
• Mark as Date Table
• Year to Date, Quarter to Date
• Fiscal Year to Date
• Same Period Last Year
• Year over Year Comparison
• ParallelPeriod vs SamePeriodLastYear vs DateAdd
• Running Total
• Rolling 12 Month Sales
• Average 12 Month Sales
• Rolling 6 Months
• Flexible time banding
• Time zone consideration in Power BI

Module 4: Power BI for Data Architects (Architecture and Administration)
: Power BI Service
• Power BI Components
• Power BI Service Content; Dashboard, Report, and Dataset
• Dashboard Features in the service
• What is Gateway?
• Two types of Gateway; On-premises, Personal
• Configuration and Installation of Gateway
• Import Data and Schedule Refresh
• DirectQuery Connection
• Live Connection
• Composite Model
• Basic Dashboard or Report Sharing
• Workspaces in Power BI; Dev Environment
• Power BI Apps; User Environment
• Publish to Web
• Embed in SharePoint Online; Office 365 Integrated Sharing
• Power BI Embedded; Embedding content into another application
• Comparison of all Sharing methods
: Security
• Row Level Security
• Dynamic Row Level Security
• Patterns for Dynamic Row Level Security
• Manager Level Access
• Organization Chart Access as Dynamic Row Level Security
• Profiles and Users; Dynamic Row Level Security
• Dynamic Row Level Security using SQL Server Analysis ServiceLive Connection

4.4: Administration and Licensing
• Power BI Administrator Configuration
• Usage Metrics
• Customizing Usage Metrics
• Power BI Licensing Guide
• Power BI Premium
• Power BI Embedded Licensing
: Integration
• PowerPoint Integration with Power BI
• Power BI and Excel; More than just an Integration
• Power BI Report Server: Power BI on-premises
• SQL Server Reporting Services Integration with Power BI
• Real-time Streaming with Power BI, and Azure StreamAnalytics
Basic to Advance EXCEL
• MS-EXCEL BASICS
• Create And Close Files
• Tabs and Groups
• Editing Data
• Formatting
• Characteristics of a cell
• Cell Reference
• Speak cells
• Text to columns
• Comments
• Copying and Filling
• Advanced fill
• Fill Series
• Flash Fill
• Viewing Worksheets
• Custom Views
• Adjusting Worksheet
• Working with Worksheet
• Compare Two Sheets
• View Files Side by side
• Logical Test
• Logical Functions
• Text function
• Date Functions
• Name Range
• Name Range with Formulas, header and Footer
• Basic Formulas
• Count Function
• Statistical Functions
• Math & Trigonometric Functions
• Lookup
• Functions
• Protecting, Hyperlink, page setup for printing
• Data sorting
• Filters
• Charts
• Chart Elements
• Pivot table
• Consolidation
• Conditional Formatting
• Data validation