1. Activating Power Query & Understanding Power Query Editor

2. Importing Data from Multiple Data Source
    From Websites, Text Files, CSV Files, Excel, Folder, etc.

3. Handling Data Types and Cleaning Unnecessary Data
    Transforming Text, Number and Date
    Handling Rows ( Filtering Row, Remove Top or Bottom rows, Setting as Header Rows, etc )
    Handling Columns ( Splitting Columns to Extract the required text, Merging Columns as a single  Column )
   Remove Duplicate and Errors
   Replace & Fill
   Sorting
   Editing Query in Formula Bar

4. Appending and Merging Queries
    Appending Table from Same Excel File
    Appending, Duplicating and Referencing Multiple Queries
    Merging by Multiple/Cross JOINS

5. Data Transformation
    Pivoting & Unpivoting

    Group by Function
    Transposing

6. Adding Calculated Columns
    Add Columns from Examples
    Adding Conditional Columns
    Adding Custom Calculated Columns
    Adding Index Columns

7. Loading Query Data and Refreshing
    Understanding the Multiple Loading Options
    Customizing Refreshing Setting

8. M Language
    Understanding M language Pattern
    Editing and Writing M in Advanced Editor
    If and Nested If function
    Using If & And / If & Or
    Text Functions
    Date Functions
    Error Trapping
    Writing M in Notepad++

9. Power Query Objects
    List Object and List Functions
    Record Object and Record Function
    Table Object & Table Function
    Other Objects & Useful Function

10. Parameter Tables and Custom Function
    Parameter Tables for Filtering
    Scrape Data from Multiple Web Pages
    Dynamic Folder Paths and File Paths
    Formula Firewall Errors
    Managing Parameters

11. Case Studies



1. Essential Background Concepts
    Pivot Tables in Excel 2013
    Understanding PowerPivot Window
    Developing Relationships between Data Tables
    Loading Data into PowerPivot from Multiple Sources

2. Data Modelling
    Understanding Data Modelling
    Hierarchy and Perspective
    Normalization and Denormalization
    Star Schemas
    Over Denormalization

3. Overview of DAX Functions
    DAX Data Types & Operators
    Calculated Column and Calculated Field ( Measures )
    Variables
    Handling Errors in DAX

4. Common DAX functions
    Mathematical functions ( SUM, AVERAGE, COUNT, COUTNROWS, DISTINCOUNT etc. )
    Logical functions ( IF, AND, OR, NOT, SWITCH )
    Information functions ( ISBLANK, ISERROR, ISLOGICAL, ISNONTEXT, ISNUMBER, and ISTEXT )
    Relational functions ( RELATED , RELATEDTABLE )
    Iterator functions ( SUMX, AVERAGEX, RANKX , etc. )

5. Understanding Evaluation Contexts
    Row Context
    Filter Context
    Context Transition
    Evaluation Contexts and Relationship
    EARLIER, ISFILTERED, ISCROSSFILTERED

6. Table Functions
    ALL, ALLEXCEPT, ALLSELECTED, FILTER, VALUES, DISTINCT, EVALUATE, TOPN

7. CALCULATE & CALCULATETABLE
    A variety of Case studies

8. Time Intelligence Functions
    DATESMTD, DATESQTD, DATESYTD
    DATESBETWEEN, DATESINPERIOD
    PREVIOUSDAY, PREVIOUSMONTH, PREVIOUSQUARTER, PREVIOUSYEAR
    PARALLELPERIOD, SAMEPERIODLASTYEAR, DATEADD
    CLOSINGBALANCEMONTH, CLOSINGBALANCEQUARTER, CLOSINGBALANCEYEAR
    FIRSTDATE, LASTDATE

    TOTALMTD, TOTALQTD, TOTALYTD

9. Case Studies

​

​
 

1. Applying the previous learnt techniques in BI Desktop
2. Data Modelling and Developing Interactive Dashboards
 

 

1. Creating Report and Dashboard in Power BI Service
2. Generating Data Insights
3. Sharing Report & Dashboard