2015-10-30-10.30.02-1024x5762016-07-29 07.56.51

Advanced Power BI – In-Person Course


This course is module 2 of Power BI from Rookie to Rock Star Training.

In this training course you will learn advanced techniques for Power BI. This course is a deep dive into Power Query and M language for Data Transformation, Data Modeling and DAX expressions, Power BI Service, Gateways, and Architecture, and advanced Visualization. You will learn practical and real-world examples of complex challenges to be solved in Power BI. At the end of this training you will be a Power BI Jedi who can solve the most complex challenges in real-world scenarios with Power BI.


Product Description

This course is module 2 of Power BI from Rookie to Rock Star Training. In this training course you will learn advanced techniques for Power BI. This course is a deep dive into Power Query and M language for Data Transformation, Data Modeling and DAX expressions, Power BI Service, Gateways, and Architecture, and advanced Visualization. You will learn practical and real-world examples of complex challenges to be solved in Power BI. At the end of this training you will be a Power BI Jedi who can solve the most complex challenges in real-world scenarios with Power BI.

This course is deep dive of 4 main modules of Power BI as below:

Module 1: Advanced Power Query and Data Transformation

In this module, you will learn all things about Power Query, from zero to hero. You will learn Power Query from basic level with Getting data from different data sources. You will learn about different types of transformations available in Query Editor. You will also learn about M (Power Query Formula Language) in deep. Unique features such as error handling, generators, structured columns, custom functions and many other advanced level features of Power BI data transformations will be explored through handson labs and lecture. After this module you will be able to implement any types of data transformation through Power Query in Excel or Power BI.

Agenda in details;

  1. Get Data
    1. Different Data Sources
    2. What is Power Query?
    3. Power Query in Excel and Power BI
    4. Basic Transformations
    5. Get Data from Web
  2. Query Editor
    1. Base structures in Power Query
    2. Query Editor in Details
    3. Get Data from SQL Server
    4. Get Data from CSV Files
    5. Get Data from Excel
    6. Get Data from JSON
    7. Transformation Pane
  3. Power Query Syntax
    1. Data Types in Power Query
    2. Query Properties
    3. Transformation Steps
    4. Query Operations (Duplicate, Reference)
  4. Combine Queries
    1. Merge, joining queries
    2. Append, creating a big list
    3. Combine Binaries; Looping through files in a folder
  5. Transformations
    1. Column Operations
    2. Row Operations
    3. Filtering
    4. Sorting
    5. Add as New Query/Drill Down
  6. Table Transformations
    1. Group By
    2. Extending Group By
    3. Transpose
    4. Pivot, Unpivot
  7. Text Transformations
    1. Split
    2. Merge (Concatenate)
    3. Parse
    4. Extract
    5. Format
  8. Numeric Transformations
    1. Standard
    2. Scientific
    3. Statistics
  9. More transformations
    1. Date Transformations (year, Month, Quarter)
    2. Extending Fiscal Date Columns
    3. Time Transformations
    4. Adding Time/Date banding
    5. Structured Column
    6. Expand
    7. Aggregate
    8. Add Custom Column
  10. Power Query Formula Language: M
    1. What is M? and the importance of learning M
    2. M Systax
    3. Things to consider for M
    4. Basic examples of M
    5. Values in M
    6. Logical operations in M
  11. Advanced M Scripting
    1. #Shared key word; function library of Power Query
    2. Parameters
    3. Custom Functions
    4. Error Handling in Power Query
    5. Generators in Power Query; Loop structure
    6. EACH singleton function
  12. Use cases
    1. Date Dimension with Power Query
    2. Day Number of Year Custom Function
    3. Looping through files in a folder with Power Query
    4. Online Date Time Scheduler with Power Query

Module 2: Advanced Power BI Modeling and DAX

In this module, you will learn DAX from zero to hero. You will learn how to design the best model in Power BI with relationships, considering formatting and data types. You will learn about DAX which is the data modeling expression language in Power BI (and also SSAS Tabular, and Power Pivot). You will learn from Simple DAX calculations to complex expressions and calculations for solving real world challenges of a BI solution.

Agenda in details;

1-Power BI Modeling 101

  1. Relationship in Power BI
  2. Relationship based on multiple Columns
  3. Role Playing Dimension
  4. Formatting
  5. Hide/Unhide
  6. Hierarchy
  7. Basic Calculated Column and Measure
  8. Sort by Column

2-Introduction to DAX

  1. Syntax of DAX
  2. Naming in DAX
  3. Logical Operations
  4. Data Types in Power BI Model
  5. Overview of Functions
  6. Variables

3-Functions in DAX in Details

  1. Text Functions
  2. Information Functions
  3. Logical Functions
  4. Date and Time Functions
  5. Aggregation Functions

4-Filter, Value, and Relationship Functions

  1. ALL
  2. Examples of using ALL function
  3. Filter Function
  4. Related
  5. RelatedTable

5-Evaluation Contexts

  1. Row Context
  2. Filter Context
  3. Exception for Row Context
  4. Exception for Filter Context
  5. Calculate Function

6-Time Intelligence Functions

  1. Year to Date, Quarter to Date
  2. Fiscal Year to Date
  3. Same Period Last Year
  4. Running Total
  5. Rolling 12 Month Sales
  6. Average 12 Month Sales
  7. Rolling 6 Months
  8. Flexible banding

7-Calculated Tables

  1. Role Playing Dimension
  2. Top 10 Customers
  3. More Scenarios of Using Calculated Tables
  4. Performance Consideration

8-Best Practices Tips and Tricks

  1. Solving DAX Now and Today Time zone Issue
  2. Modeling Best Practices
  3. Advanced Time Intelligence
  4. Advanced Relationship in DAX
  5. Hierarchies in DAX

Module 3: Advanced Data Visualization

In this module, you will learn all things about data visualization in Power BI. Module starts with basics of visualizations of basic charts. You will learn best practices and considerations for coloring, choosing the right visual, numeric visualization and many other tips through the course. You will learn about custom visuals, how to build custom visuals. all tips and tricks in visualizationis including filters, slicers, drill down/up, best practices of map visualizations and many other real-world examples with hands on lab and lecture. You will leave this module with a Jedi level of visualization.

Agenda in details;

  1. Visualization Basics
    1. Basic Charts
    2. Clustered vs Stacked
    3. Quick Calcs in Power BI
    4. Interaction of Visuals
    5. Pages
  2. Visualization Best Practices
    1. Pie Chart; Be Careful
    2. Coloring
    3. Show me the Numbers
    4. Which Chart is the Best?
    5. Story Telling
    6. Combining Visuals
  3. Slicing and Dicing
    1. Filters
    2. Slicers
    3. Filtering Slicers
    4. Drill Down/Up
  4. Custom Formatting Visuals
    1. Labels
    2. Legend
    3. Color
    4. Extended visualization formatting
  5. Special Visuals
    1. Waterfall Chart; Cashflow
    2. Scatter Chart; Story Telling
    3. Line Chart; Powerful
    4. Table and Matrix with Conditional Formatting
    5. R Visualization
  6. Map Visualization
    1. Default map visual
    2. Filled Map; Good, bad, and the ugly
    3. Shape Map
    4. Custom Map Visuals
    5. ArcGIS
  7. Custom Visuals
    1. Using Custom Visuals
    2. SynopticPanel by SQLBI
    3. Chicklet Slicer
    4. 3D Map with GlobeMap Visual
    5. Sunburst
    6. Time Slicers
  8. KPIs in Power BI
    1. KPI Default visual
    2. Dial Gauge
    3. Linear Gauge
    4. Bullet Chart

Module 4: Advanced Gateways, Power BI Service, Administration, and Architecture

In this module, you will learn everything about Power BI Service. Difference of dashboard and Report. Setting up a gateway. Difference of personal and on-premises gateway. You will learn all tips and tricks of configuration and installtion and performance of gateway to connect Power BI on cloud report to on-premises data source and schedule it to refresh. You will also learn about DirectQuery and LiveQuery and difference between these two method. You will learn about architecturing a solution with Power BI considering integration options of Power BI with other applications.You will also learn all tips about sharing and security with best practices. This module will be delivered in lecture style plus hands on lab experiments.

Agenda in details;

  1. Gateways
    1. What is Gateway?
    2. Two types of Gateway
    3. Personal Gateway; an example
    4. On-premises Gateway; not only for Power BI
  2. On-premises Gateway in Details
    1. Requirements for on-premises gateway
    2. Architecture Flow diagram of the gateway
    3. Installation and Configuration considerations
    4. Install and Configure Gateway example
    5. Data Source Definition in Power BI Service
    6. Schedule Refresh
  3. Gateway Performance
    1. Performance Considerations
    2. Limitations of Gateway
    3. Gateway for SSAS Live data source
  4. DirectQuery
    1. Step beyond 1/10GB Limitation in Power BI
    2. Limitations of DirectQuery
    3. Dashboard and Report considerations for DirectQuery
    4. Performance Consdierations
  5. Live Query
    1. Using SSAS Live data source
    2. Difference of DirectQuery and Live Query
    3. Enterprise Scale architecture
    4. UPN Mapping
    5. Architecture of Gateway and SSAS Live Connection
  6. Power BI Service
    1. Dashboard vs Report
    2. Dashboard Tiles
    3. Data Classification
    4. Power Q&A
    5. Tips and Tricks for Power Q&A
  7. Sharing
    1. Dashboard Sharing
    2. Work Groups in Power BI
    3. Content Packs
    4. Publish to Web
    5. Comparison of all Sharing methods
    6. Sharing Architecture for enterprise
  8. Administration
    1. Admin Portal
    2. Set Power BI Administrator
    3. Tenant Settings
    4. Audit Log
  9. Row Level Security
    1. Row Level Security in Power BI; regardless of Data Source
    2. Row Level Security in SSAS Live Connection
    3. Dynamic Row Level Security
  10. Integration
    1. Analyze in Excel
    2. Power BI Publisher for Excel
    3. Reporting Services and Power BI
    4. Power BI and Power Point
  11. Real-time
    1. REST API of Power BI
    2. Real-time Tiles
    3. Azure Stream Analytics
  12. Architecture
    1. Enterprise Scale
    2. Reporting
    3. Self-Service
    4. Licensing



Instructor: Reza Rad

Our trainer is the world well-known name in the Microsoft BI field. Reza Rad is invited speaker in world’s best and biggest SQL Server and BI conferences such as PASS Summits, PASS Rallys, SQLBits, TechEds, Ignites, and so on.  He is author of books on this topic, and he has more than 15 years’ experience in the Microsoft BI technologies. 10 years of his experience focused on training and consulting to the largest BI teams in the world. He is also Microsoft Certified Trainer for years. He is Microsoft SQL Server MVP (Most Valuable Professional) focused on BI and Data Analysis, He has been awarded MVP from Microsoft because of his dedication and expertise in Microsoft BI technologies. He is author of Power BI online book; from Rookie to Rock Star



What others say about the training and trainer;


Jeffrey Weir; Independent Consultant, Wellington, New Zealand

I learned a lot from this course: it gave a great overview of what’s possible both right from the UI, and what more awaits if you lift the hood and pimp the engine. It’s incredible to see just how much PowerBI and PowerQuery radically simplify what I would otherwise have to do with many, many lines of complicated VBA code. And Reza is a masterful user of this tool.

Note that Reza covers a lot of ground in this course, and it is pretty fast paced. While you can take a laptop and follow along as Reza takes PowerBI/Powerquery through its paces, for the one-day course my recommendation would be to keep that laptop closed, and simply sit back and watch Reza in action, so you can fully concentrate on just how simple this tool is to use in the right hands as well as think about how you might use the tool to your own ends and ask questions along those lines. You can always purchase the video course (if it’s not already included in your course) and step through the material again at your own pace.

Rob Wilby; Independent Consultant, UK

I must say your “Power BI Essentials” course has been the most comprehensive Power BI course I have found to date and seeing how you work with data especially the little tricks naturally introduced at just the right moments has made this course a pleasure to take.

Martin Catherall – Data Platform MVP, PASS Regional Mentor

I attended Reza’s Power BI pre-con “From Rookie to Rock star” at SQL Saturday Brisbane in May 2016. I was a complete Power BI novice, having only previously accomplished a few very basic things with Power BI. Reza took the group through all the components that make up environment. He explained how they had evolved and took the group through walk-throughs of each one. Reza was great an answering questions from the audience at the event and going into extra detail if necessary. All-in all, I would say that I’m now a very confident Power BI user after attending Reza’s pre-con.

Dan Cheshire, Senior Project Manager Air New Zealand

Reza does not just know Power BI like the back of his hand, he is the most commercially aware BI person I know.  He understands that companies work in the real world and that data isn’t always clean and needs manipulation for it to work and be useful.  When Reza worked on our project, nothing phased or flustered him, he always came up with solutions for problems that seemed to me as insurmountable.  Can’t recommend him enough, invest in Reza as he will not only save you a whole heap of time, but he comes up with long lasting and very powerful Power BI solutions for your business.

Sonia Buckley, Data Analyst at Zoom2u, Sydney, Australia

We have started to use Microsoft Power BI and whilst Microsoft tutorials are helpful, if you are not used to DAX then it is very difficult to get your head around and there is little other resources that don’t cost an arm and a leg.  I had a call with Reza who made my life so much easier in just under an hour!  Any time spent speaking to Reza will be well worth it – I have saved hours of trying to get my head around our dashboards by getting some very useful tips and explanations on why things are done the DAX way!  Not only that but he is very patient and straight forward.  Highly recommended!




Cancellation up to 5 weeks before the event: fully refund minus administration fee ($50) and credit-card processing fees (if applicable).

Cancellation from 5 weeks to 2 weeks before the event: 50% cancellation charge, 50% refund

Cancellation from 2 weeks before the event: 100% cancellation charge, 0% refunded.
Transfer fee to another event date* (up to 2 weeks before event): 25% of the standard price of the event to transfer

Transfer fee to another event date* (from 2 weeks to 1 day before event): 40% of the standard price of the event to transfer

Transfer fee at the day of event*: 60% of the standard price of the event to transfer

*transfer can be done only once, and it can be only transferred to another date not later than 6 months from the original event.
No Show:
No fee will be refunded for no show.















Additional Information


Melbourne 14th-15th Feb 2017, Sydney 21st-22nd Feb 2017, Auckland 2nd-3rd Mar 2017, Wellington 9th-10th Mar 2017, Brisbane 16th-17th Mar 2017, Perth 28th-29th Mar 2017


There are no reviews yet.

Be the first to review “Advanced Power BI – In-Person Course”