RADACAD Blog

Power BI Q&A; How to Ask Questions?

In previous chapters of Power BI from Rookie to Rock Star you’ve learned that Power BI reports and models can be published in Power BI website for sharing, scheduling and some other purposes. One of the features in Power BI website which is unique to Power BI and is not available in many BI tools in the market is Power Q&A engine. Power Q&A is a natural question and answering engine in Power BI. With Q&A you can ask your questions from the existing model with natural language and get response with visualization elements! In this post I’ll show you some of intelligence questions that you can ask from Q&A to get a very useful response.

Prerequisite for Running Samples of this Post

If you want to run sample of this post and see how they works in action you need to create the Publication dashboard from the Power BI report built on top of Pubs database. In one of my earlier posts in the book I explained how to create that report. You can also ask similar questions from any other model in your Power BI dashboard with just changing name of fields and values to whatever you have in your dataset.

Explore the Data

When you published your Power BI file into the Power BI desktop, usually you create a dashboard for it. For Power Q&A to work (the version of Power Q&A at the time of writing this post) you should create a dashboard for your report. After creating the dashboard you will see the question bar of Q&A on the top of your dashboard.

Let’s start by the most basic question:

Explore a Dataset

by typing name of a dataset you can get it in a table view. You can either type in name of the table; such as “sales” or put a “show” keyword before it likc “show sales”

As you can see the Q&A is also smart enough to suggest you a sentense to ask the question.

Filter Values

You can filter values with a WHERE keyword in your sentense. example below fetch all sales for the book titled “Is Anger Enemey?”

You can do date filters as well simply by mentioning the date. below sample shows sales for year 1994:

Explore Related Datasets

You can explore data from related datasets. For example to get titles and authors (which are in two separate datasets) you can ask:

Sorting Data

You can use Ascending or Descending commands in your question.

Also notice that you can change the order with simply hovering your mouse on each column and clicking on sort icon that appears there. I also have the “As Table” at the end of question above that force result set to be viewed as a table visualization.

More Filters

Equity Filter

You can filter on equity of values. such as sales on the payerms Net 60

Range Filter

Use “between” keyword to filter for a range

Exact Date Filter

You can mention the exact date simply like sales October 28, 1993

Date Range Filter

You can filter dateset for dates before or after a date as below

Filter Relative to Today’s Date

Fetch data with filters such as past or next periods. for example fetch dataset information for past 10 years or so.

Aggregation

You can even do calulations in your question, how impresive is that. For example ask Total Sales amount or Average Sales Amount

You can then aggregate across attribute values. such as; average sales amount per author

Visualization Customization

You can ask what type of visual you want to see the result set to be shown.

Combination of some above

Now that you know some keywords to put in your question, you can ask questions that have combined items to create more useful reports such as:

You can pin every answer you want to your dashboard to save it for later.

There are some other keywords that helps you to ask better questions and get better answers from Power BI, but let’s keep this post simple for now. In future posts I’ll explain how to build you Power BI model in a way that responds best to question from Q&A. tips in creating relationship, naming columns, data types and etc.

Your Turn

Now go to your Power BI dashboard and have fun with Q&A and bring some of examples you’ve done. I’d love to see what you will find

Reza Rad is an Author, Trainer, Speaker and DW/BI Consultant. He has a BSc in Computer engineering; he has more than 15 years’ experience in databases, programming and development mostly on Microsoft technologies. He is a Microsoft MVP in Data Platform for seven continues years (from 2011 till now) for his dedication in Microsoft BI. He is author of some SQL Server and BI books, and also Power BI online book; from Rookie to Rock Star.

Event Details

Course Title: Power BI from Rookie to Rock Star

Length:

Instructor: Reza Rad

In this training course you will learn Power BI from beginner to advance. You will learn how to use Power BI for simple data analysis situations as well as complex business intelligence scenarios. You will learn about Power BI Components such as Power Query (Get Data and Transform), Modeling and DAX, Visualization, Power BI Desktop as the main tool, Power BI Service, Gateway configuration, and architecture. You will learn all the concepts with live demos. Expect learning best practices with great scenarios in this course.

Power BI from Rookie to Rock Star

This is the most comprehensive course for Power BI which split in daily modules. You can enroll in any of these modules separately or take the whole course. Modules designed independently, which means each module can be taken regardless of the order of modules. Here are list and detailed agenda of each module:

Module 1: Power BI Essentials

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

In this training course, you will learn Power BI from beginner to advance. You will learn how to use Power BI for simple data analysis situations as well as complex business intelligence scenarios. You will learn about Power BI Desktop, Power BI Website, and components of Power BI which are; Get and Transform (or Power Query), Modeling (or Power Pivot), and Visualization. You will also learn about Power Query Formula Language (Called M informally), and DAX. This course designed to give you an end to end view of Power BI, so you be able to use Power BI straight away in your everyday challenges for data analysis.

1.1: Introduction to Power BI

Power BI is the newest Microsoft Business Intelligence and Data Analysis tool. In this module, we will go through basics of this product, and introduce all five components of Power BI (Power Query, Power Pivot, Power View, Power Map, and Power Q&A). You will see some demos and introduction about Power BI desktop, Office 365 Power BI subscription, and Power BI website, and mobile apps. You will see some basic demos of how easy to use is Power BI in some scenarios.

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, Power Map, and Power Q&A.

1.2: Getting Data

Getting Data is the first experience of working with Power BI. You can connect to many data sources on-premises or on cloud. For some data sources, you can have a live or direct connection, for some connection works offline. For some connections, you need a gateway or connector to be installed. In this module, you will learn everything about get data experience of Power BI.

What is Power Query: Introduction to Data Mash-Up Engine of Power BI

Get Started with Power Query: Movies Data Mash-Up

Power BI Get Data from Excel: Everything You Need to Know

Definitive Guide to Power BI Personal Gateway and Enterprise Gateway

File Sources

Folder as a Source

Database Sources

Analysis Services Connection

Get Data from Azure SQL Database

Azure SQL Data Warehouse Source

Software as A Source

Web Source

1.3: Power Query for Data Transformation

Data analysis and BI world starts from data extraction and transformation. Power Query is the data transformation engine of Power BI. Power Query comes as part of Excel 2016, or as an add-in for Excel 2013 and 2010. Power Query is also part of Power BI Desktop. In this module you will learn about all different version of Power Query, their similarities, and differences, as well as configuration and requirements of using them.

Power Query uses a graphical user interface to apply transformations on the dataset. However Power Query works with a functional scripting language behind the scenes. The key to learn Power Query is learning the functional language called M. There are many features in M that are not available in the Power Query GUI. In this module you will learn how to understand M, and how to write M scripts even from scratch. You will learn writing custom functions in M as well as many other useful features.

The content that you will learn in this module includes but not limited to;

Different versions of Power Query

Power Query Introduction

Query Editor

Transformation GUI

Row Transformations

Column Transformations

Data Type

Adding Column

Text Transformations

Number Column Calculations

Date and Time Calculations

Data types, Lists, Records, and tables in M

M built-in functions

Generators in M

Writing Custom Functions

Error handling

Advanced Data Transformations with M

1.4: Data Modelling and DAX

Power Pivot is xVelocity in-memory data modelling engine of the Power BI. Modelling effectively is the key of high performance BI solution. In this module, you will learn basics of Power Pivot such as creating relationships, and calculated members, as well as advanced best practices and DAX expressions. DAX is Data Analytical eXpression language. DAX has similar structure to excel functions, but it is different. In this module, you will learn DAX from beginner to advance. You will learn basic functions as well as complex functions and scenarios of using them in real world challenges.

The content that you will learn in this module includes but not limited to;

Power Pivot xVelocity engine basics and concepts

Relationships in Power BI

Best practices

Introduction to DAX

Calculated Columns

Measures

Calculated Tables

Row Context vs Set Context

Advanced calculations using Calculate functions

Time Intelligence Functions

DAX Advanced

1.5: Data Visualization

Data Visualization is the front end of any BI application; this is the user view point of your system. It is critical to visualize measures, and dimensions effectively so the BI system could tell the story of the data clearly. In this module, you will learn conceptual best practices of data visualizations which is valid through all data visualization tools. You will learn Power View and Power Map skills. Power View is the interactive data visualization tool and Power Map is 3D geo-spatial data visualization tool. You will learn how to create effective charts, and dashboards using these tools as well as best practices for working with these tools. There are two version of Power View; Power View for Excel, and Power View for SharePoint which will be covered in this module as well.

The content that you will learn in this module includes but not limited to;

Power BI Desktop Visualization

Custom Visuals in Power BI Desktop

Formatting Visuals in Power BI Desktop

Waterfall Chart

Built-in Charts and Graphs in Power BI

Sorting, Filtering, and categorization

KPIs

Maps and Geo-Spatial Visualization

1.6: Power BI Service, Dashboards, Q&A, and Gateways

Power BI introduced a new engine for users which focus on users who wants to play with the data more, and understand the story behind the data better. Power Q&A is question and answering engine which works based on human natural language. Power Q&A automatically will be enabled on Power BI files deployed into Power BI website, or Office 365 subscription. However, there are some tips and tricks about how to design your model to get the best type of answers through Power Q&A. In this module, you will learn best practices for modelling that effect on the response of Power Q&A.

The content that you will learn in this module includes but not limited to;

Power Q&A introduction

Design best practices for Power Q&A

Power BI Service

Dashboard vs Report

Gateways

1.7: Administration & Security

Power BI components can be deployed into different environments such as Power BI website, Office 365 subscription, or even SharePoint on-premises. In this module, we will go through deployment options, Configurations and requirements required for each environment. You will learn how to deploy your Power BI files into Power BI website. you will also learn how to build dashboards in the website, and how to work with Mobile Apps for Apple, Android and Windows Phone.

The content that you will learn in this module includes but not limited to;

Groups and Work Spaces

Sharing

Row Level Security

Schedule Refresh

Module 2: Advanced Power Query and Data Transformation in Power BI

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

In this course, 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 hands-on labs and lecture. After this course you will be able to implement any types of data transformation through Power Query in Excel or Power BI.

Agenda;

2.1: Get Data

Different Data Sources

What is Power Query?

Power Query in Excel and Power BI

Basic Transformations

Get Data from Web

2.2: Query Editor

Base structures in Power Query

Query Editor in Details

Get Data from SQL Server

Get Data from CSV Files

Get Data from Excel

Get Data from JSON

Transformation Pane

2.3: Power Query Syntax

Data Types in Power Query

Query Properties

Transformation Steps

Query Operations; Duplicate, and Reference

2.4: Combine Queries

Merge, Joining queries

Append, creating a big list

Combine Binaries; Looping through files in a folder

2.5: Transformations

Column Operations

Row Operations

Filtering

Sorting

Add as new query / Drill Down

2.6: Table Transformations

Group By

Extending Group By with Expression Editor

Transpose

Pivot, Unpivot

2.7: Text Transformations

Split

Merge (Concatenate)

Parse

Extract

2.8: Numeric Transformations

Standard

Scientific

Statistics

2.9: More Transformations

Date Transformations (Year, Month, Quarter)

Extending Fiscal Date Column

Time Transformations

Adding Time/Date banding

Structured Column

Expand

Aggregate

Add Custom Column

2.10: Power Query Formula Language: M

What is M? and the importance of learning M

M Syntax

Things to Consider for M Scripting

Basic examples of M

Values in M

Logical Operations

2.11: Advanced M Scripting

#Shared Keyword; function library of Power Query

Parameters

Custom Functions

Error Handling in Power Query

Generators in Power Query: Implementing Loop Structure

EACH singleton function

2.12: Use Cases

Date Dimension with Power Query

Day Number of Year Custom Function

Looping through files in a folder with Power Query

Online Date Time Scheduler with Power Query

Module 3: Advanced Power BI Modeling and DAX

This course is module 3 of the Power BI from Rookie to Rock Star course.

In this course, 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;

3.1: Power BI Modeling 101

Relationship in Power BI

Relationship based on multiple Columns

Role Playing Dimension

Formatting

Hide/Unhide

Hierarchy

Basic Calculated Column and Measure

Sort by Column

3.2: Introduction to DAX

Syntax of DAX

Naming in DAX

Logical Operations

Data Types in Power BI Model

Overview of Functions

Variables

3.3: Functions in DAX in Details

Text Functions

Information Functions

Logical Functions

Date and Time Functions

Aggregation Functions

3.4: Filter, Value, and Relationship Functions

ALL

Examples of using ALL function

Filter Function

Related

RelatedTable

3.5: Evaluation Contexts

Row Context

Filter Context

Exception for Row Context

Exception for Filter Context

Calculate Function

3.6: Time Intelligence Functions

Year to Date, Quarter to Date

Fiscal Year to Date

Same Period Last Year

Running Total

Rolling 12 Month Sales

Average 12 Month Sales

Rolling 6 Months

Flexible banding

3.7: Calculated Tables

Role Playing Dimension

Top 10 Customers

More Scenarios of Using Calculated Tables

Performance Consideration

3.8: Best Practices Tips and Tricks

Solving DAX Now and Today Time zone Issue

Modeling Best Practices

Advanced Time Intelligence

Advanced Relationship in DAX

Hierarchies in DAX

Module 4: Advanced Data Visualization with Power BI

This course is module 4 of the Power BI from Rookie to Rock Star course.

In this course, you will learn all things about data visualization in Power BI. Day 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 visualizations 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 course with a Jedi level of visualization.

Agenda;

4.1: Visualization Basics

Basic Charts

Clustered vs Stacked

Quick Calcs in Power BI

Interaction of Visuals

Pages

4.2: Visualization Best Practices

Pie Chart; Be Careful

Coloring

Show me the Numbers

Which Chart is the Best?

Story Telling

Combining Visuals

4.3: Slicing and Dicing

Filters

Slicers

Filtering Slicers

Drill Down/Up

4.4: Custom Formatting Visuals

Labels

Legend

Color

Extended visualization formatting

Group / bin

4.5: Special Visuals

Waterfall Chart; Cashflow

Scatter Chart; Story Telling

Line Chart; Powerful

Table and Matrix with Conditional Formatting

R Visualization

4.6: Map Visualization

Default map visual

Filled Map; Good, bad, and the ugly

Shape Map

Custom Map Visuals

ArcGIS

4.7: Custom Visuals

Using Custom Visuals

SynopticPanel by SQLBI

Chicklet Slicer

3D Map with GlobeMap Visual

Sunburst

Time Slicers

4.8: KPIs in Power BI

KPI Default visual

Dial Gauge

Linear Gauge

Bullet Chart

4.9: Building a Custom Visual

Basics of scripting language

Writing first simple custom visual

Components of a custom visual component

Module 5: Power BI Service, Gateways, and Architecture

This course is module 5 of the Power BI from Rookie to Rock Star course.

In this course, 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 installation 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 architecting 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 course will be delivered in lecture style plus hands on lab experiments.

Agenda;

5.1: Gateways

What is Gateway?

Two types of Gateway

Personal Gateway; an example

On-premises Gateway; not only for Power BI

5.2: On-premises Gateway in Details

Requirements for on-premises gateway

Architecture Flow diagram of the gateway

Installation and Configuration considerations

Install and Configure Gateway example

Data Source Definition in Power BI Service

Schedule Refresh

5.3: Gateway Performance

Performance Considerations

Limitations of Gateway

Gateway for SSAS Live data source

5.4: DirectQuery

Step beyond 1/10GB Limitation in Power BI

Limitations of DirectQuery

Dashboard and Report considerations for DirectQuery

Performance Considerations

5.5: Live Query

Using SSAS Live data source

Difference of DirectQuery and Live Query

Enterprise Scale architecture

UPN Mapping

Architecture of Gateway and SSAS Live Connection

5.6: Power BI Service

Dashboard vs Report

Dashboard Tiles

Data Classification

Power Q&A

Tips and Tricks for Power Q&A

5.7: Sharing

Dashboard Sharing

Work Groups in Power BI

Content Packs

Publish to Web

Comparison of all Sharing methods

Sharing Architecture for enterprise

5.8: Administration

Admin Portal

Set Power BI Administrator

Tenant Settings

Audit Log

5.9: Row Level Security

Row Level Security in Power BI; regardless of Data Source

Row Level Security in SSAS Live Connection

Dynamic Row Level Security

5.10: Integration

Analyze in Excel

Power BI Publisher for Excel

Reporting Services and Power BI

Power BI and Power Point

5.11: Real-time

REST API of Power BI

Real-time Tiles

Azure Stream Analytics

5.12: Architecture

Architecture for Enterprise Scale

Architecture for Reporting

Architecture for Self-Service

Licensing

Power BI Free

Power BI Pro

Power BI Premium

Why Power BI Training?

Power BI is the newest Microsoft BI tool for data mash up, build modelling, and visualize it effectively. It supports mobile devices, it support self-service, and along with its great functionalities, it doesn’t require heavy development or production system.

Common Myths about Power BI

There are some myths about Power BI that sometimes prevents people and organization to move towards using this product. We’ve listed some of myths in following. Please note that these are Myths, and they are not right assumptions.

Myth 1: Power BI is a self-service Excel tool, and isn’t suitable for Enterprise Solutions.

There are many scenarios that Power BI has been used in enterprise solutions. Power BI features for data mash up (with the very powerful data transformation component: Power Query), and data modelling based on xVelocity in-memory engine (Power Pivot), and great data visualization components such as Power View and Power Map, along with mobility support of this product, are rarely available in other products. We have designed, implemented and have seem many BI enterprise solutions based on Power BI within very large organizations.

Myth 2: Power BI is easy to use, and doesn’t require training course.

Power BI is an easy to use tool, but for basic functions. Every BI application requires calculated measure, which needs to be written in DAX. Every BI application requires advanced data transformations which need to be handled with Power Query M scripting language. Designing and implementing solutions with Best Practices is always a requirement for reliable BI applications. We designed our Power BI training in a way that delivers advanced content for all components of Power BI, and fortify your team’s knowledge on the product, so they can solve any complex challenge with this tool.

Myth 3: Power BI is only for Microsoft based environments and platforms.

Power Query is the core of data extraction in Power BI. Power Query supports wide range of data sources. Not only SQL Server, but also Oracle, MySQL, web service result set, DB2 and many other data source are supported. There is also possibility to connect to many systems such as salesforce. The Visualization component of Power BI is also HTML5 supported, and is available for all mobile devices. Power BI app in AppStore, and Google PlayStore as well as Microsoft AppStore enables dashboards and data visualizations to be available in all environments.

Myth 4: Power BI is not a powerful and fully functional BI tool, and cannot be compared with other tools in the market.

In fact the reverse is true. Power BI is such powerful that can be replacement of many other expensive BI tools in the market. We’ve been through scenarios of successful Power BI solutions that have been replacement of Cognos, IBM products, Oracle WB, SAP BO, and so on. Power BI has 5 powerful components that cover main aspects of BI and data analysis system.

In addition there are always possibility to use this as a hybrid approach and save lots of time and money along with great features that are only available in this product.

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 book from Rookie to Rock Star.

In-Person Training;

Our Power BI in person training will be held in high quality hotels with recommendation for hotel bookings for attendees. There will be special group rating fee as well as early bird and past attendees discount. for schedule of our in person training follow this link:

Online Training;

We run online training with GoToWebinar and GoToTraining applications. These applications provide highly reliable communication channel between instructor and attendees. For schedule of our online training follow this link:

What others say about the training and trainer

I spent probably about two weeks looking for a good Microsoft Power BI class that would cover all of the aspects of the program. I wanted to walk away from the class being able to apply what I learned immediately. Most training companies that I looked into were in a state of flux with Power BI mostly due to the transitions that Power BI itself was going through. Then I stumbled on the RADACAD web site. Reza Rad touted a course that he taught named “Power BI, From Rookie to Rock star.” If the class was indeed an A to Z course, then it was exactly what I was looking for. The class outline seemed to include every topic on my checkoff list plus much more. I decided to give Reza a try.
Reza completely lived up to his hype. He took us through a natural progression of the software, using example after example of how to complete a task. We went through real world scenarios, a bare minimum of two scenarios for each minor topic. I was completely impressed with his Power BI knowledge, understanding and abilities. Any question we came to Reza with was answered completely and with even more examples.
After the class, my goal of immediately applying what I learned was attained. Right away I was able to successfully pull and massage data, create useful reports and post them to dashboards.
I would recommend Radacad Power BI Training without hesitation.

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!

You keep hearing about the machine learning and R recently. R become a language for data analysis and machine learning that makes the need for learning it more clearly. Moreover, Microsoft introduces R server as a comprehensive platform for using machine learning and R functionality inside the data analysis tools such as SQL Server 2016 and Power BI.

This training is three days of training using Machine Learning and R with SQL Server, Power BI, and concepts of Machine Learning.

Product Description

Length: 5 days

Prerequisite: This course is a beginner to advanced level course. You don’t need any prerequisites for attending in this course.

Instructor: Dr. Leila Etaati

You keep hearing about the machine learning and R recently. R become a language for data analysis and machine learning that makes the need for learning it more clearly. Moreover, Microsoft introduces R server as a comprehensive platform for using machine learning and R functionality inside the data analysis tools such as SQL Server 2016 and Power BI.

At this training, you will learn:

Day 1: Azure ML, R Basics

In the first day, you will be familiar with the basic concepts of Machine Learning in Azure ML and some basic concepts of R in R Studio and Microsoft Visual studio 2015. In Day 1 you will learn;

sqldf to use SQL Statements for query dataset, RODBC for fetching data from SQL database

The main concepts of basic statistics and how they can be helpful like: mean, median, standard division, and so forth will be discussed.

Practice in R and using some packages

Day 2: R algorithms and Power BI

The main aim of days 2 is to learn some of main machine learning algorithms, and understand how they work and how they can solve different type of real life problems. You will familiar with these algorithms and their syntax in R, and how to use them in Power BI. Moreover, you will learn how to embed some R visualization in Power BI.

In day 2 you will learn;

How classification algorithm like KNN works. and its relevant code in R.

KNN concepts

Write the KNN in R code

How to evaluate the result

How to improve the result by changing related parameters

Decision Tree concepts and its relevant R codes

What is decision tree, the concepts and how it works

Introduce different type of decision tree algorithms that are more common.

Introduce Rpart packages, how to write the related R code in Rstudio and Rvisual studio

See the Rpart package in Power BI visual

Package C50 for decision tree how to evaluate result and how to improve the model

Associative Rules

Audience will learn the main concepts behid the Associative rules

Where to use it and how it brings insight for users

They will learn to write the code in R

Become familiar with concepts such as support, confidence and Lift.

Learn how Apriori packages and algorithm works

How to evaluate the association rules result

How to display it in Power BI

How to improve the results

How to write R codes in Power BI for transformation, and also creating new queries

How to set up the R in Power BI

How to write Simple R code for correlation analysis in R

How to create R visuals in Power BI

Do data transformation in Power BI with R functions

How to create interactive R reports in Power BI

How to call azure ML API into Power BI

Shows how to create functions in Power BI to call the API from Azure ML

Create a prediction model in Power BI and show the result in visualization.

Day 3: R algorithms and SQL Server 2016

In third day, you will learn how to bring analytics and intelligence using SQL server 2016. Moreover, you will also learn some other important machine learning algorithms.

In Day 3 you will learn:

Neural Network algorithm

The concepts behind it and how it works

The ksvm() function

Evaluate the model and how to improve the performance

Time series algorithm

Time series concept difference between seasonality and trend

The function that is used to create the time series model

How to improve and evaluate it

Regression algorithm

Concept behind the regression

Predict numeric data (e.g. using linear regression) the function used to do the linear regression

How to create a model that support the nonlinear models

Multiple regression, logistic regression and so forth

How to evaluate the results

Recommendation (content-based filtering and collaborative filtering).

What is recommendation and what is content filtering and collaborating filtering

How to implement content filtering and collaborative filtering

Using classification and clustering algorithms to create a recommendation system

Azure ML recommendation model

Moreover, you will learn how to use machine learning in SQL Server 2016 and in SSRS.

Set up SQL Server to run the R scripts

The best practice for implementing a prediction process in SQL Server 2016

Create a correlation analysis in SQL Server

Create a decision tree diagram in SSRS via writing code in R

See an end to end predictive analysis code from getting data, create models, evaluate models and publish the result.

Practice: creating a predictive solution in SQL Server 2016

This course is full of hands on labs, and you will experiment all examples through real-world demos. At the end of the 3-day training course, you will be able to use techniques and concepts of this training in your Analytics challenges.

Instructor: Dr. Leila Etaati

Dr. Leila Etaati gained her PhD in University of Auckland. She is world well-known speaker in Machine Learning and Analytics topics, and spoke in world’s best international conferences in Data Platform topics, such as; PASS Summits, PASS Rally, SQL Nexus, Microsoft Ignite, and so on. She has more than 10 years experience in Data Mining and Analytics. She is also Microsoft Most Valuable Professional (MVP) because of her dedication on Microsoft Analytics and Machine Learning technologies. She writes blog posts in RADACAD and also publishes YouTube videos in our channel. She also is an invited lecturer in universities such as University of Auckland, and Unitec, and some other universities. She worked in many industries including banking financial, power and utility, manufacturing, tourism, and so on.

What others say about the training and trainer

Kenny McMillan, Sports Physiologist / Data Analyst, Frankfurt, Germany:
I attended RADACADs “Advanced Analytics” course recently in Frankfurt in May 2017. Being a regular user of Power BI (with a science background ) the course was extremely helpful in showing me how to incorporate R data visualisations into Power BI dashboards and for introducing me to machine learning using the Microsoft ML Studio. Leila is an excellent and extremely knowledgeable instructor and explained complex data analytical concepts and methodologies in an easy-to-understand manner. I thoroughly recommend this course to anyone who wants to expand their data analytical skills and knowledge.

Event Details

Course Title: Power BI from Rookie to Rock Star

Length:

Instructor: Reza Rad

In this training course you will learn Power BI from beginner to advance. You will learn how to use Power BI for simple data analysis situations as well as complex business intelligence scenarios. You will learn about Power BI Components such as Power Query (Get Data and Transform), Modeling and DAX, Visualization, Power BI Desktop as the main tool, Power BI Service, Gateway configuration, and architecture. You will learn all the concepts with live demos. Expect learning best practices with great scenarios in this course.

Power BI from Rookie to Rock Star

This is the most comprehensive course for Power BI which split in daily modules. You can enroll in any of these modules separately or take the whole course. Modules designed independently, which means each module can be taken regardless of the order of modules. Here are list and detailed agenda of each module:

Module 1: Power BI Essentials

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

In this training course, you will learn Power BI from beginner to advance. You will learn how to use Power BI for simple data analysis situations as well as complex business intelligence scenarios. You will learn about Power BI Desktop, Power BI Website, and components of Power BI which are; Get and Transform (or Power Query), Modeling (or Power Pivot), and Visualization. You will also learn about Power Query Formula Language (Called M informally), and DAX. This course designed to give you an end to end view of Power BI, so you be able to use Power BI straight away in your everyday challenges for data analysis.

1.1: Introduction to Power BI

Power BI is the newest Microsoft Business Intelligence and Data Analysis tool. In this module, we will go through basics of this product, and introduce all five components of Power BI (Power Query, Power Pivot, Power View, Power Map, and Power Q&A). You will see some demos and introduction about Power BI desktop, Office 365 Power BI subscription, and Power BI website, and mobile apps. You will see some basic demos of how easy to use is Power BI in some scenarios.

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, Power Map, and Power Q&A.

1.2: Getting Data

Getting Data is the first experience of working with Power BI. You can connect to many data sources on-premises or on cloud. For some data sources, you can have a live or direct connection, for some connection works offline. For some connections, you need a gateway or connector to be installed. In this module, you will learn everything about get data experience of Power BI.

What is Power Query: Introduction to Data Mash-Up Engine of Power BI

Get Started with Power Query: Movies Data Mash-Up

Power BI Get Data from Excel: Everything You Need to Know

Definitive Guide to Power BI Personal Gateway and Enterprise Gateway

File Sources

Folder as a Source

Database Sources

Analysis Services Connection

Get Data from Azure SQL Database

Azure SQL Data Warehouse Source

Software as A Source

Web Source

1.3: Power Query for Data Transformation

Data analysis and BI world starts from data extraction and transformation. Power Query is the data transformation engine of Power BI. Power Query comes as part of Excel 2016, or as an add-in for Excel 2013 and 2010. Power Query is also part of Power BI Desktop. In this module you will learn about all different version of Power Query, their similarities, and differences, as well as configuration and requirements of using them.

Power Query uses a graphical user interface to apply transformations on the dataset. However Power Query works with a functional scripting language behind the scenes. The key to learn Power Query is learning the functional language called M. There are many features in M that are not available in the Power Query GUI. In this module you will learn how to understand M, and how to write M scripts even from scratch. You will learn writing custom functions in M as well as many other useful features.

The content that you will learn in this module includes but not limited to;

Different versions of Power Query

Power Query Introduction

Query Editor

Transformation GUI

Row Transformations

Column Transformations

Data Type

Adding Column

Text Transformations

Number Column Calculations

Date and Time Calculations

Data types, Lists, Records, and tables in M

M built-in functions

Generators in M

Writing Custom Functions

Error handling

Advanced Data Transformations with M

1.4: Data Modelling and DAX

Power Pivot is xVelocity in-memory data modelling engine of the Power BI. Modelling effectively is the key of high performance BI solution. In this module, you will learn basics of Power Pivot such as creating relationships, and calculated members, as well as advanced best practices and DAX expressions. DAX is Data Analytical eXpression language. DAX has similar structure to excel functions, but it is different. In this module, you will learn DAX from beginner to advance. You will learn basic functions as well as complex functions and scenarios of using them in real world challenges.

The content that you will learn in this module includes but not limited to;

Power Pivot xVelocity engine basics and concepts

Relationships in Power BI

Best practices

Introduction to DAX

Calculated Columns

Measures

Calculated Tables

Row Context vs Set Context

Advanced calculations using Calculate functions

Time Intelligence Functions

DAX Advanced

1.5: Data Visualization

Data Visualization is the front end of any BI application; this is the user view point of your system. It is critical to visualize measures, and dimensions effectively so the BI system could tell the story of the data clearly. In this module, you will learn conceptual best practices of data visualizations which is valid through all data visualization tools. You will learn Power View and Power Map skills. Power View is the interactive data visualization tool and Power Map is 3D geo-spatial data visualization tool. You will learn how to create effective charts, and dashboards using these tools as well as best practices for working with these tools. There are two version of Power View; Power View for Excel, and Power View for SharePoint which will be covered in this module as well.

The content that you will learn in this module includes but not limited to;

Power BI Desktop Visualization

Custom Visuals in Power BI Desktop

Formatting Visuals in Power BI Desktop

Waterfall Chart

Built-in Charts and Graphs in Power BI

Sorting, Filtering, and categorization

KPIs

Maps and Geo-Spatial Visualization

1.6: Power BI Service, Dashboards, Q&A, and Gateways

Power BI introduced a new engine for users which focus on users who wants to play with the data more, and understand the story behind the data better. Power Q&A is question and answering engine which works based on human natural language. Power Q&A automatically will be enabled on Power BI files deployed into Power BI website, or Office 365 subscription. However, there are some tips and tricks about how to design your model to get the best type of answers through Power Q&A. In this module, you will learn best practices for modelling that effect on the response of Power Q&A.

The content that you will learn in this module includes but not limited to;

Power Q&A introduction

Design best practices for Power Q&A

Power BI Service

Dashboard vs Report

Gateways

1.7: Administration & Security

Power BI components can be deployed into different environments such as Power BI website, Office 365 subscription, or even SharePoint on-premises. In this module, we will go through deployment options, Configurations and requirements required for each environment. You will learn how to deploy your Power BI files into Power BI website. you will also learn how to build dashboards in the website, and how to work with Mobile Apps for Apple, Android and Windows Phone.

The content that you will learn in this module includes but not limited to;

Groups and Work Spaces

Sharing

Row Level Security

Schedule Refresh

Module 2: Advanced Power Query and Data Transformation in Power BI

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

In this course, 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 hands-on labs and lecture. After this course you will be able to implement any types of data transformation through Power Query in Excel or Power BI.

Agenda;

2.1: Get Data

Different Data Sources

What is Power Query?

Power Query in Excel and Power BI

Basic Transformations

Get Data from Web

2.2: Query Editor

Base structures in Power Query

Query Editor in Details

Get Data from SQL Server

Get Data from CSV Files

Get Data from Excel

Get Data from JSON

Transformation Pane

2.3: Power Query Syntax

Data Types in Power Query

Query Properties

Transformation Steps

Query Operations; Duplicate, and Reference

2.4: Combine Queries

Merge, Joining queries

Append, creating a big list

Combine Binaries; Looping through files in a folder

2.5: Transformations

Column Operations

Row Operations

Filtering

Sorting

Add as new query / Drill Down

2.6: Table Transformations

Group By

Extending Group By with Expression Editor

Transpose

Pivot, Unpivot

2.7: Text Transformations

Split

Merge (Concatenate)

Parse

Extract

2.8: Numeric Transformations

Standard

Scientific

Statistics

2.9: More Transformations

Date Transformations (Year, Month, Quarter)

Extending Fiscal Date Column

Time Transformations

Adding Time/Date banding

Structured Column

Expand

Aggregate

Add Custom Column

2.10: Power Query Formula Language: M

What is M? and the importance of learning M

M Syntax

Things to Consider for M Scripting

Basic examples of M

Values in M

Logical Operations

2.11: Advanced M Scripting

#Shared Keyword; function library of Power Query

Parameters

Custom Functions

Error Handling in Power Query

Generators in Power Query: Implementing Loop Structure

EACH singleton function

2.12: Use Cases

Date Dimension with Power Query

Day Number of Year Custom Function

Looping through files in a folder with Power Query

Online Date Time Scheduler with Power Query

Module 3: Advanced Power BI Modeling and DAX

This course is module 3 of the Power BI from Rookie to Rock Star course.

In this course, 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;

3.1: Power BI Modeling 101

Relationship in Power BI

Relationship based on multiple Columns

Role Playing Dimension

Formatting

Hide/Unhide

Hierarchy

Basic Calculated Column and Measure

Sort by Column

3.2: Introduction to DAX

Syntax of DAX

Naming in DAX

Logical Operations

Data Types in Power BI Model

Overview of Functions

Variables

3.3: Functions in DAX in Details

Text Functions

Information Functions

Logical Functions

Date and Time Functions

Aggregation Functions

3.4: Filter, Value, and Relationship Functions

ALL

Examples of using ALL function

Filter Function

Related

RelatedTable

3.5: Evaluation Contexts

Row Context

Filter Context

Exception for Row Context

Exception for Filter Context

Calculate Function

3.6: Time Intelligence Functions

Year to Date, Quarter to Date

Fiscal Year to Date

Same Period Last Year

Running Total

Rolling 12 Month Sales

Average 12 Month Sales

Rolling 6 Months

Flexible banding

3.7: Calculated Tables

Role Playing Dimension

Top 10 Customers

More Scenarios of Using Calculated Tables

Performance Consideration

3.8: Best Practices Tips and Tricks

Solving DAX Now and Today Time zone Issue

Modeling Best Practices

Advanced Time Intelligence

Advanced Relationship in DAX

Hierarchies in DAX

Module 4: Advanced Data Visualization with Power BI

This course is module 4 of the Power BI from Rookie to Rock Star course.

In this course, you will learn all things about data visualization in Power BI. Day 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 visualizations 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 course with a Jedi level of visualization.

Agenda;

4.1: Visualization Basics

Basic Charts

Clustered vs Stacked

Quick Calcs in Power BI

Interaction of Visuals

Pages

4.2: Visualization Best Practices

Pie Chart; Be Careful

Coloring

Show me the Numbers

Which Chart is the Best?

Story Telling

Combining Visuals

4.3: Slicing and Dicing

Filters

Slicers

Filtering Slicers

Drill Down/Up

4.4: Custom Formatting Visuals

Labels

Legend

Color

Extended visualization formatting

Group / bin

4.5: Special Visuals

Waterfall Chart; Cashflow

Scatter Chart; Story Telling

Line Chart; Powerful

Table and Matrix with Conditional Formatting

R Visualization

4.6: Map Visualization

Default map visual

Filled Map; Good, bad, and the ugly

Shape Map

Custom Map Visuals

ArcGIS

4.7: Custom Visuals

Using Custom Visuals

SynopticPanel by SQLBI

Chicklet Slicer

3D Map with GlobeMap Visual

Sunburst

Time Slicers

4.8: KPIs in Power BI

KPI Default visual

Dial Gauge

Linear Gauge

Bullet Chart

4.9: Building a Custom Visual

Basics of scripting language

Writing first simple custom visual

Components of a custom visual component

Module 5: Power BI Service, Gateways, and Architecture

This course is module 5 of the Power BI from Rookie to Rock Star course.

In this course, 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 installation 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 architecting 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 course will be delivered in lecture style plus hands on lab experiments.

Agenda;

5.1: Gateways

What is Gateway?

Two types of Gateway

Personal Gateway; an example

On-premises Gateway; not only for Power BI

5.2: On-premises Gateway in Details

Requirements for on-premises gateway

Architecture Flow diagram of the gateway

Installation and Configuration considerations

Install and Configure Gateway example

Data Source Definition in Power BI Service

Schedule Refresh

5.3: Gateway Performance

Performance Considerations

Limitations of Gateway

Gateway for SSAS Live data source

5.4: DirectQuery

Step beyond 1/10GB Limitation in Power BI

Limitations of DirectQuery

Dashboard and Report considerations for DirectQuery

Performance Considerations

5.5: Live Query

Using SSAS Live data source

Difference of DirectQuery and Live Query

Enterprise Scale architecture

UPN Mapping

Architecture of Gateway and SSAS Live Connection

5.6: Power BI Service

Dashboard vs Report

Dashboard Tiles

Data Classification

Power Q&A

Tips and Tricks for Power Q&A

5.7: Sharing

Dashboard Sharing

Work Groups in Power BI

Content Packs

Publish to Web

Comparison of all Sharing methods

Sharing Architecture for enterprise

5.8: Administration

Admin Portal

Set Power BI Administrator

Tenant Settings

Audit Log

5.9: Row Level Security

Row Level Security in Power BI; regardless of Data Source

Row Level Security in SSAS Live Connection

Dynamic Row Level Security

5.10: Integration

Analyze in Excel

Power BI Publisher for Excel

Reporting Services and Power BI

Power BI and Power Point

5.11: Real-time

REST API of Power BI

Real-time Tiles

Azure Stream Analytics

5.12: Architecture

Architecture for Enterprise Scale

Architecture for Reporting

Architecture for Self-Service

Licensing

Power BI Free

Power BI Pro

Power BI Premium

Why Power BI Training?

Power BI is the newest Microsoft BI tool for data mash up, build modelling, and visualize it effectively. It supports mobile devices, it support self-service, and along with its great functionalities, it doesn’t require heavy development or production system.

Common Myths about Power BI

There are some myths about Power BI that sometimes prevents people and organization to move towards using this product. We’ve listed some of myths in following. Please note that these are Myths, and they are not right assumptions.

Myth 1: Power BI is a self-service Excel tool, and isn’t suitable for Enterprise Solutions.

There are many scenarios that Power BI has been used in enterprise solutions. Power BI features for data mash up (with the very powerful data transformation component: Power Query), and data modelling based on xVelocity in-memory engine (Power Pivot), and great data visualization components such as Power View and Power Map, along with mobility support of this product, are rarely available in other products. We have designed, implemented and have seem many BI enterprise solutions based on Power BI within very large organizations.

Myth 2: Power BI is easy to use, and doesn’t require training course.

Power BI is an easy to use tool, but for basic functions. Every BI application requires calculated measure, which needs to be written in DAX. Every BI application requires advanced data transformations which need to be handled with Power Query M scripting language. Designing and implementing solutions with Best Practices is always a requirement for reliable BI applications. We designed our Power BI training in a way that delivers advanced content for all components of Power BI, and fortify your team’s knowledge on the product, so they can solve any complex challenge with this tool.

Myth 3: Power BI is only for Microsoft based environments and platforms.

Power Query is the core of data extraction in Power BI. Power Query supports wide range of data sources. Not only SQL Server, but also Oracle, MySQL, web service result set, DB2 and many other data source are supported. There is also possibility to connect to many systems such as salesforce. The Visualization component of Power BI is also HTML5 supported, and is available for all mobile devices. Power BI app in AppStore, and Google PlayStore as well as Microsoft AppStore enables dashboards and data visualizations to be available in all environments.

Myth 4: Power BI is not a powerful and fully functional BI tool, and cannot be compared with other tools in the market.

In fact the reverse is true. Power BI is such powerful that can be replacement of many other expensive BI tools in the market. We’ve been through scenarios of successful Power BI solutions that have been replacement of Cognos, IBM products, Oracle WB, SAP BO, and so on. Power BI has 5 powerful components that cover main aspects of BI and data analysis system.

In addition there are always possibility to use this as a hybrid approach and save lots of time and money along with great features that are only available in this product.

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 book from Rookie to Rock Star.

In-Person Training;

Our Power BI in person training will be held in high quality hotels with recommendation for hotel bookings for attendees. There will be special group rating fee as well as early bird and past attendees discount. for schedule of our in person training follow this link:

Online Training;

We run online training with GoToWebinar and GoToTraining applications. These applications provide highly reliable communication channel between instructor and attendees. For schedule of our online training follow this link:

What others say about the training and trainer

I spent probably about two weeks looking for a good Microsoft Power BI class that would cover all of the aspects of the program. I wanted to walk away from the class being able to apply what I learned immediately. Most training companies that I looked into were in a state of flux with Power BI mostly due to the transitions that Power BI itself was going through. Then I stumbled on the RADACAD web site. Reza Rad touted a course that he taught named “Power BI, From Rookie to Rock star.” If the class was indeed an A to Z course, then it was exactly what I was looking for. The class outline seemed to include every topic on my checkoff list plus much more. I decided to give Reza a try.
Reza completely lived up to his hype. He took us through a natural progression of the software, using example after example of how to complete a task. We went through real world scenarios, a bare minimum of two scenarios for each minor topic. I was completely impressed with his Power BI knowledge, understanding and abilities. Any question we came to Reza with was answered completely and with even more examples.
After the class, my goal of immediately applying what I learned was attained. Right away I was able to successfully pull and massage data, create useful reports and post them to dashboards.
I would recommend Radacad Power BI Training without hesitation.

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!

You keep hearing about the machine learning and R recently. R become a language for data analysis and machine learning that makes the need for learning it more clearly. Moreover, Microsoft introduces R server as a comprehensive platform for using machine learning and R functionality inside the data analysis tools such as SQL Server 2016 and Power BI.

This training is three days of training using Machine Learning and R with SQL Server, Power BI, and concepts of Machine Learning.

Product Description

Length: 5 days

Prerequisite: This course is a beginner to advanced level course. You don’t need any prerequisites for attending in this course.

Instructor: Dr. Leila Etaati

You keep hearing about the machine learning and R recently. R become a language for data analysis and machine learning that makes the need for learning it more clearly. Moreover, Microsoft introduces R server as a comprehensive platform for using machine learning and R functionality inside the data analysis tools such as SQL Server 2016 and Power BI.

At this training, you will learn:

Day 1: Azure ML, R Basics

In the first day, you will be familiar with the basic concepts of Machine Learning in Azure ML and some basic concepts of R in R Studio and Microsoft Visual studio 2015. In Day 1 you will learn;

sqldf to use SQL Statements for query dataset, RODBC for fetching data from SQL database

The main concepts of basic statistics and how they can be helpful like: mean, median, standard division, and so forth will be discussed.

Practice in R and using some packages

Day 2: R algorithms and Power BI

The main aim of days 2 is to learn some of main machine learning algorithms, and understand how they work and how they can solve different type of real life problems. You will familiar with these algorithms and their syntax in R, and how to use them in Power BI. Moreover, you will learn how to embed some R visualization in Power BI.

In day 2 you will learn;

How classification algorithm like KNN works. and its relevant code in R.

KNN concepts

Write the KNN in R code

How to evaluate the result

How to improve the result by changing related parameters

Decision Tree concepts and its relevant R codes

What is decision tree, the concepts and how it works

Introduce different type of decision tree algorithms that are more common.

Introduce Rpart packages, how to write the related R code in Rstudio and Rvisual studio

See the Rpart package in Power BI visual

Package C50 for decision tree how to evaluate result and how to improve the model

Associative Rules

Audience will learn the main concepts behid the Associative rules

Where to use it and how it brings insight for users

They will learn to write the code in R

Become familiar with concepts such as support, confidence and Lift.

Learn how Apriori packages and algorithm works

How to evaluate the association rules result

How to display it in Power BI

How to improve the results

How to write R codes in Power BI for transformation, and also creating new queries

How to set up the R in Power BI

How to write Simple R code for correlation analysis in R

How to create R visuals in Power BI

Do data transformation in Power BI with R functions

How to create interactive R reports in Power BI

How to call azure ML API into Power BI

Shows how to create functions in Power BI to call the API from Azure ML

Create a prediction model in Power BI and show the result in visualization.

Day 3: R algorithms and SQL Server 2016

In third day, you will learn how to bring analytics and intelligence using SQL server 2016. Moreover, you will also learn some other important machine learning algorithms.

In Day 3 you will learn:

Neural Network algorithm

The concepts behind it and how it works

The ksvm() function

Evaluate the model and how to improve the performance

Time series algorithm

Time series concept difference between seasonality and trend

The function that is used to create the time series model

How to improve and evaluate it

Regression algorithm

Concept behind the regression

Predict numeric data (e.g. using linear regression) the function used to do the linear regression

How to create a model that support the nonlinear models

Multiple regression, logistic regression and so forth

How to evaluate the results

Recommendation (content-based filtering and collaborative filtering).

What is recommendation and what is content filtering and collaborating filtering

How to implement content filtering and collaborative filtering

Using classification and clustering algorithms to create a recommendation system

Azure ML recommendation model

Moreover, you will learn how to use machine learning in SQL Server 2016 and in SSRS.

Set up SQL Server to run the R scripts

The best practice for implementing a prediction process in SQL Server 2016

Create a correlation analysis in SQL Server

Create a decision tree diagram in SSRS via writing code in R

See an end to end predictive analysis code from getting data, create models, evaluate models and publish the result.

Practice: creating a predictive solution in SQL Server 2016

This course is full of hands on labs, and you will experiment all examples through real-world demos. At the end of the 3-day training course, you will be able to use techniques and concepts of this training in your Analytics challenges.

Instructor: Dr. Leila Etaati

Dr. Leila Etaati gained her PhD in University of Auckland. She is world well-known speaker in Machine Learning and Analytics topics, and spoke in world’s best international conferences in Data Platform topics, such as; PASS Summits, PASS Rally, SQL Nexus, Microsoft Ignite, and so on. She has more than 10 years experience in Data Mining and Analytics. She is also Microsoft Most Valuable Professional (MVP) because of her dedication on Microsoft Analytics and Machine Learning technologies. She writes blog posts in RADACAD and also publishes YouTube videos in our channel. She also is an invited lecturer in universities such as University of Auckland, and Unitec, and some other universities. She worked in many industries including banking financial, power and utility, manufacturing, tourism, and so on.

What others say about the training and trainer

Kenny McMillan, Sports Physiologist / Data Analyst, Frankfurt, Germany:
I attended RADACADs “Advanced Analytics” course recently in Frankfurt in May 2017. Being a regular user of Power BI (with a science background ) the course was extremely helpful in showing me how to incorporate R data visualisations into Power BI dashboards and for introducing me to machine learning using the Microsoft ML Studio. Leila is an excellent and extremely knowledgeable instructor and explained complex data analytical concepts and methodologies in an easy-to-understand manner. I thoroughly recommend this course to anyone who wants to expand their data analytical skills and knowledge.

Event Details

Event Details

Length: 3 days

Prerequisite: This course is a beginner to advanced level course. You don’t need any prerequisites for attending in this course.

Instructor: Dr. Leila Etaati

You keep hearing about the machine learning and R recently. R become a language for data analysis and machine learning that makes the need for learning it more clearly. Moreover, Microsoft introduces R server as a comprehensive platform for using machine learning and R functionality inside the data analysis tools such as SQL Server 2016 and Power BI.

At this training, you will learn:

Day 1: Azure ML, R basics

In the first day, you will be familiar with basic concepts of Machine Learning in Azure ML and some basic concepts of R in RStudio and Microsoft Visual studio 2015. In Day 1 you will learn;

How to use some of the main packages in R such as ggplot2, dplyer, sqldf and so forth.

The main concepts of basic statistics and how they can be helpful like: mean, median, standard division, and so forth.

Practice in R and using some packages

Day 2: R algorithms and Power BI

The main aim of days 2 is to learn some of main algorithms, and understand how they work and how they can solve different type of real life problems. You will familiar with these algorithms and their syntax in R, and how to use them in Power BI. Moreover, you will learn how to embed some R visualization in Power BI.

In day 2 you will learn;

How classification algorithm like KNN works. and its relevant code in R.

Decision Tree concepts and its relevant R codes

How Associative Rules works, and what is the concepts behind it, plus the relevant codes

How to write R codes in Power BI for transformation, and also creating new queries

Day 3: R algorithms and SQL Server 2016

In third day, you will learn how to bring analytics and intelligence using SQL server 2016. Moreover, you will also learn some other important machine learning algorithms.

In Day 3 you will learn:

Concepts and R codes for

Neural Network algorithm

Time series algorithm

Regression algorithm

Recommendation (content-based filtering and collaborative filtering). Moreover, you will learn how to use machine learning in SQL Server 2016 and in SSRS.

Using R SQL Server 2016 for rung the R code inside the SQL Server scripts will be shown.

The general procedure for creating R diagram in SSRS will be discussed

Two different scenarios will be discussed in class so audience will understand how to create a machine learning process in SQL Server 2016.

This course is full of hands on labs, and you will experiment all examples through real-world demos. At the end of the 3-day training course, you will be able to use techniques and concepts of this training in your Analytics challenges.

Instructor: Dr. Leila Etaati

Dr. Leila Etaati gained her PhD in University of Auckland. She is world well-known speaker in Machine Learning and Analytics topics, and spoke in world’s best international conferences in Data Platform topics, such as; PASS Summits, PASS Rally, SQL Nexus, Microsoft Ignite, and so on. She has more than 10 years experience in Data Mining and Analytics. She is also Microsoft Most Valuable Professional (MVP) because of her dedication on Microsoft Analytics and Machine Learning technologies. She writes blog posts in RADACAD and also publishes YouTube videos in our channel. She also is an invited lecturer in universities such as University of Auckland, and Unitec, and some other universities. She worked in many industries including banking financial, power and utility, manufacturing, tourism, and so on.

What others say about the training and trainer

I attended RADACADs “Advanced Analytics” course recently in Frankfurt in May 2017. Being a regular user of Power BI (with a science background ) the course was extremely helpful in showing me how to incorporate R data visualisations into Power BI dashboards and for introducing me to machine learning using the Microsoft ML Studio. Leila is an excellent and extremely knowledgeable instructor and explained complex data analytical concepts and methodologies in an easy-to-understand manner. I thoroughly recommend this course to anyone who wants to expand their data analytical skills and knowledge.

Event Details

Event Details

I spent probably about two weeks looking for a good Microsoft Power BI class that would cover all of the aspects of the program. I wanted to walk away from the class being able to apply what I learned immediately. Most training companies that I looked into were in a state of flux with Power BI mostly due to the transitions that Power BI itself was going through. Then I stumbled on the RADACAD web site. Reza Rad touted a course that he taught named “Power BI, From Rookie to Rock star.” If the class was indeed an A to Z course, then it was exactly what I was looking for. The class outline seemed to include every topic on my checkoff list plus much more. I decided to give Reza a try.

Reza completely lived up to his hype. He took us through a natural progression of the software, using example after example of how to complete a task. We went through real world scenarios, a bare minimum of two scenarios for each minor topic. I was completely impressed with his Power BI knowledge, understanding and abilities. Any question we came to Reza with was answered completely and with even more examples.

After the class, my goal of immediately applying what I learned was attained. Right away I was able to successfully pull and massage data, create useful reports and post them to dashboards.

I would recommend Radacad Power BI Training without hesitation.

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!

Course Details

This is the most comprehensive course for Power BI which split in daily modules. You can enroll in any of these modules separately or take the whole course. Modules designed independently, which means each module can be taken regardless of the order of modules. Here are list and detailed agenda of each module:

Module 1: Power BI Essentials

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

In this training course, you will learn Power BI from beginner to advance. You will learn how to use Power BI for simple data analysis situations as well as complex business intelligence scenarios. You will learn about Power BI Desktop, Power BI Website, and components of Power BI which are; Get and Transform (or Power Query), Modeling (or Power Pivot), and Visualization. You will also learn about Power Query Formula Language (Called M informally), and DAX. This course designed to give you an end to end view of Power BI, so you be able to use Power BI straight away in your everyday challenges for data analysis.

1.1: Introduction to Power BI

Power BI is the newest Microsoft Business Intelligence and Data Analysis tool. In this module, we will go through basics of this product, and introduce all five components of Power BI (Power Query, Power Pivot, Power View, Power Map, and Power Q&A). You will see some demos and introduction about Power BI desktop, Office 365 Power BI subscription, and Power BI website, and mobile apps. You will see some basic demos of how easy to use is Power BI in some scenarios.

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, Power Map, and Power Q&A.

1.2: Getting Data

Getting Data is the first experience of working with Power BI. You can connect to many data sources on-premises or on cloud. For some data sources, you can have a live or direct connection, for some connection works offline. For some connections, you need a gateway or connector to be installed. In this module, you will learn everything about get data experience of Power BI.

What is Power Query: Introduction to Data Mash-Up Engine of Power BI

Get Started with Power Query: Movies Data Mash-Up

Power BI Get Data from Excel: Everything You Need to Know

Definitive Guide to Power BI Personal Gateway and Enterprise Gateway

File Sources

Folder as a Source

Database Sources

Analysis Services Connection

Get Data from Azure SQL Database

Azure SQL Data Warehouse Source

Software as A Source

Web Source

1.3: Power Query for Data Transformation

Data analysis and BI world starts from data extraction and transformation. Power Query is the data transformation engine of Power BI. Power Query comes as part of Excel 2016, or as an add-in for Excel 2013 and 2010. Power Query is also part of Power BI Desktop. In this module you will learn about all different version of Power Query, their similarities, and differences, as well as configuration and requirements of using them.

Power Query uses a graphical user interface to apply transformations on the dataset. However Power Query works with a functional scripting language behind the scenes. The key to learn Power Query is learning the functional language called M. There are many features in M that are not available in the Power Query GUI. In this module you will learn how to understand M, and how to write M scripts even from scratch. You will learn writing custom functions in M as well as many other useful features.

The content that you will learn in this module includes but not limited to;

Different versions of Power Query

Power Query Introduction

Query Editor

Transformation GUI

Row Transformations

Column Transformations

Data Type

Adding Column

Text Transformations

Number Column Calculations

Date and Time Calculations

Data types, Lists, Records, and tables in M

M built-in functions

Generators in M

Writing Custom Functions

Error handling

Advanced Data Transformations with M

1.4: Data Modelling and DAX

Power Pivot is xVelocity in-memory data modelling engine of the Power BI. Modelling effectively is the key of high performance BI solution. In this module, you will learn basics of Power Pivot such as creating relationships, and calculated members, as well as advanced best practices and DAX expressions. DAX is Data Analytical eXpression language. DAX has similar structure to excel functions, but it is different. In this module, you will learn DAX from beginner to advance. You will learn basic functions as well as complex functions and scenarios of using them in real world challenges.

The content that you will learn in this module includes but not limited to;

Power Pivot xVelocity engine basics and concepts

Relationships in Power BI

Best practices

Introduction to DAX

Calculated Columns

Measures

Calculated Tables

Row Context vs Set Context

Advanced calculations using Calculate functions

Time Intelligence Functions

DAX Advanced

1.5: Data Visualization

Data Visualization is the front end of any BI application; this is the user view point of your system. It is critical to visualize measures, and dimensions effectively so the BI system could tell the story of the data clearly. In this module, you will learn conceptual best practices of data visualizations which is valid through all data visualization tools. You will learn Power View and Power Map skills. Power View is the interactive data visualization tool and Power Map is 3D geo-spatial data visualization tool. You will learn how to create effective charts, and dashboards using these tools as well as best practices for working with these tools. There are two version of Power View; Power View for Excel, and Power View for SharePoint which will be covered in this module as well.

The content that you will learn in this module includes but not limited to;

Power BI Desktop Visualization

Custom Visuals in Power BI Desktop

Formatting Visuals in Power BI Desktop

Waterfall Chart

Built-in Charts and Graphs in Power BI

Sorting, Filtering, and categorization

KPIs

Maps and Geo-Spatial Visualization

1.6: Power BI Service, Dashboards, Q&A, and Gateways

Power BI introduced a new engine for users which focus on users who wants to play with the data more, and understand the story behind the data better. Power Q&A is question and answering engine which works based on human natural language. Power Q&A automatically will be enabled on Power BI files deployed into Power BI website, or Office 365 subscription. However, there are some tips and tricks about how to design your model to get the best type of answers through Power Q&A. In this module, you will learn best practices for modelling that effect on the response of Power Q&A.

The content that you will learn in this module includes but not limited to;

Power Q&A introduction

Design best practices for Power Q&A

Power BI Service

Dashboard vs Report

Gateways

1.7: Administration & Security

Power BI components can be deployed into different environments such as Power BI website, Office 365 subscription, or even SharePoint on-premises. In this module, we will go through deployment options, Configurations and requirements required for each environment. You will learn how to deploy your Power BI files into Power BI website. you will also learn how to build dashboards in the website, and how to work with Mobile Apps for Apple, Android and Windows Phone.

The content that you will learn in this module includes but not limited to;

Groups and Work Spaces

Sharing

Row Level Security

Schedule Refresh

Module 2: Advanced Power Query and Data Transformation in Power BI

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

In this course, 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 hands-on labs and lecture. After this course you will be able to implement any types of data transformation through Power Query in Excel or Power BI.

Agenda;

2.1: Get Data

Different Data Sources

What is Power Query?

Power Query in Excel and Power BI

Basic Transformations

Get Data from Web

2.2: Query Editor

Base structures in Power Query

Query Editor in Details

Get Data from SQL Server

Get Data from CSV Files

Get Data from Excel

Get Data from JSON

Transformation Pane

2.3: Power Query Syntax

Data Types in Power Query

Query Properties

Transformation Steps

Query Operations; Duplicate, and Reference

2.4: Combine Queries

Merge, Joining queries

Append, creating a big list

Combine Binaries; Looping through files in a folder

2.5: Transformations

Column Operations

Row Operations

Filtering

Sorting

Add as new query / Drill Down

2.6: Table Transformations

Group By

Extending Group By with Expression Editor

Transpose

Pivot, Unpivot

2.7: Text Transformations

Split

Merge (Concatenate)

Parse

Extract

2.8: Numeric Transformations

Standard

Scientific

Statistics

2.9: More Transformations

Date Transformations (Year, Month, Quarter)

Extending Fiscal Date Column

Time Transformations

Adding Time/Date banding

Structured Column

Expand

Aggregate

Add Custom Column

2.10: Power Query Formula Language: M

What is M? and the importance of learning M

M Syntax

Things to Consider for M Scripting

Basic examples of M

Values in M

Logical Operations

2.11: Advanced M Scripting

#Shared Keyword; function library of Power Query

Parameters

Custom Functions

Error Handling in Power Query

Generators in Power Query: Implementing Loop Structure

EACH singleton function

2.12: Use Cases

Date Dimension with Power Query

Day Number of Year Custom Function

Looping through files in a folder with Power Query

Online Date Time Scheduler with Power Query

Module 3: Advanced Power BI Modeling and DAX

This course is module 3 of the Power BI from Rookie to Rock Star course.

In this course, 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;

3.1: Power BI Modeling 101

Relationship in Power BI

Relationship based on multiple Columns

Role Playing Dimension

Formatting

Hide/Unhide

Hierarchy

Basic Calculated Column and Measure

Sort by Column

3.2: Introduction to DAX

Syntax of DAX

Naming in DAX

Logical Operations

Data Types in Power BI Model

Overview of Functions

Variables

3.3: Functions in DAX in Details

Text Functions

Information Functions

Logical Functions

Date and Time Functions

Aggregation Functions

3.4: Filter, Value, and Relationship Functions

ALL

Examples of using ALL function

Filter Function

Related

RelatedTable

3.5: Evaluation Contexts

Row Context

Filter Context

Exception for Row Context

Exception for Filter Context

Calculate Function

3.6: Time Intelligence Functions

Year to Date, Quarter to Date

Fiscal Year to Date

Same Period Last Year

Running Total

Rolling 12 Month Sales

Average 12 Month Sales

Rolling 6 Months

Flexible banding

3.7: Calculated Tables

Role Playing Dimension

Top 10 Customers

More Scenarios of Using Calculated Tables

Performance Consideration

3.8: Best Practices Tips and Tricks

Solving DAX Now and Today Time zone Issue

Modeling Best Practices

Advanced Time Intelligence

Advanced Relationship in DAX

Hierarchies in DAX

Module 4: Advanced Data Visualization with Power BI

This course is module 4 of the Power BI from Rookie to Rock Star course.

In this course, you will learn all things about data visualization in Power BI. Day 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 visualizations 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 course with a Jedi level of visualization.

Agenda;

4.1: Visualization Basics

Basic Charts

Clustered vs Stacked

Quick Calcs in Power BI

Interaction of Visuals

Pages

4.2: Visualization Best Practices

Pie Chart; Be Careful

Coloring

Show me the Numbers

Which Chart is the Best?

Story Telling

Combining Visuals

4.3: Slicing and Dicing

Filters

Slicers

Filtering Slicers

Drill Down/Up

4.4: Custom Formatting Visuals

Labels

Legend

Color

Extended visualization formatting

Group / bin

4.5: Special Visuals

Waterfall Chart; Cashflow

Scatter Chart; Story Telling

Line Chart; Powerful

Table and Matrix with Conditional Formatting

R Visualization

4.6: Map Visualization

Default map visual

Filled Map; Good, bad, and the ugly

Shape Map

Custom Map Visuals

ArcGIS

4.7: Custom Visuals

Using Custom Visuals

SynopticPanel by SQLBI

Chicklet Slicer

3D Map with GlobeMap Visual

Sunburst

Time Slicers

4.8: KPIs in Power BI

KPI Default visual

Dial Gauge

Linear Gauge

Bullet Chart

4.9: Building a Custom Visual

Basics of scripting language

Writing first simple custom visual

Components of a custom visual component

Module 5: Power BI Service, Gateways, and Architecture

This course is module 5 of the Power BI from Rookie to Rock Star course.

In this course, 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 installation 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 architecting 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 course will be delivered in lecture style plus hands on lab experiments.

Agenda;

5.1: Gateways

What is Gateway?

Two types of Gateway

Personal Gateway; an example

On-premises Gateway; not only for Power BI

5.2: On-premises Gateway in Details

Requirements for on-premises gateway

Architecture Flow diagram of the gateway

Installation and Configuration considerations

Install and Configure Gateway example

Data Source Definition in Power BI Service

Schedule Refresh

5.3: Gateway Performance

Performance Considerations

Limitations of Gateway

Gateway for SSAS Live data source

5.4: DirectQuery

Step beyond 1/10GB Limitation in Power BI

Limitations of DirectQuery

Dashboard and Report considerations for DirectQuery

Performance Considerations

5.5: Live Query

Using SSAS Live data source

Difference of DirectQuery and Live Query

Enterprise Scale architecture

UPN Mapping

Architecture of Gateway and SSAS Live Connection

5.6: Power BI Service

Dashboard vs Report

Dashboard Tiles

Data Classification

Power Q&A

Tips and Tricks for Power Q&A

5.7: Sharing

Dashboard Sharing

Work Groups in Power BI

Content Packs

Publish to Web

Comparison of all Sharing methods

Sharing Architecture for enterprise

5.8: Administration

Admin Portal

Set Power BI Administrator

Tenant Settings

Audit Log

5.9: Row Level Security

Row Level Security in Power BI; regardless of Data Source

Row Level Security in SSAS Live Connection

Dynamic Row Level Security

5.10: Integration

Analyze in Excel

Power BI Publisher for Excel

Reporting Services and Power BI

Power BI and Power Point

5.11: Real-time

REST API of Power BI

Real-time Tiles

Azure Stream Analytics

5.12: Architecture

Architecture for Enterprise Scale

Architecture for Reporting

Architecture for Self-Service

Licensing

Instructor: Reza Rad

Reza Rad is invited speaker in world’s best and biggest SQL Server and BI conferences such as PASS Summits, PASS Rallys, PASS BA Conference, SQLBits, TechEds, Ignites, and many other conferences. 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 Data Platform 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

Prices: (GST Exclusive)

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!

Event Details

Event Details

Prerequisite: This course is a beginner to advanced level course. You don’t need any prerequisites for attending in this course.

Instructor: Dr. Leila Etaati

You’ve heard about the machine learning and R recently. R become a language for data analysis and machine learning. R also can be used for visualization widely. Microsoft power BI as a self-service BI tool helps people to easily extract data from different sources, apply data transformation and do data wrangling, and finally load and visual data in an appropriate way. Recently, Microsoft enabled users to use R codes and visuals inside the power BI which is very beneficial for Data Scientists, Analysts, and BI developers.

At this training, you will learn:

Day 1. Basics of R and R Visualization inside Power BI, and Basics of Machine Learning

In the first day, you will be familiar with the R language basics, the important of R packages for data cleaning, data manipulation, and data visualization. Also, you will learn how to embeded some of the R visualizations in Power BI. At the end of the day we will move to machine learning component. This day includes, but not limited to contents below;

Installing R studio, and setting up R in power BI,

Understanding main data structures in R such as Data frame, Vector and List.

Learning the main concepts of basic statistics. Learning how they can be helpful, statistics operations like: mean, median, standard division, and so forth will be discussed.

Getting familiar with the main packages like dplyer for data cleaning and manipulating. Functions like filter() for Comparisons, Logical operators, Missing values. Or function “select”.

Learning how to use some of the main packages in R such as ggplot2 to visualize data. Learning how to visualise data with aim of data comparison (among items and over times), relationship between variables (two or more variables), data distributions (few data point, large data, two or three variables), and data composition (static or changing over time).

How to write R codes in Power BI for transformation, and also creating new queries

How to write Simple R code for correlation analysis in R

How to create R visuals in Power BI

Do data transformation in Power BI with R functions

How to create interactive R reports in Power BI

How classification algorithm like KNN works. and its relevant code in R.

KNN concepts

Write the KNN in R code

How to evaluate the result

How to improve the result by changing related parameters

Day 2. Machine Learning inside Power BI

The main aim of days 2 is to learn some of the main machine learning algorithms, and understand how they work, and how they can solve different type of real life problems. You will be familiar with these algorithms and their syntax in R, and how to use them in Power BI. Examples of Predictive analytics, Descriptive analytics, and prescriptive analytics will be covered in this day of training. This day includes, but not limited to contents below;

Decision Tree concepts and its relevant R codes

What is decision tree, the concepts and how it works.

Introduction to different type of decision tree packages that are more common such as rpart and C5.

How to change some of paraments of Rpart and C5, how to draw and customize a decision tree

Learn how to evaluate result and improve the model.

See how to have a decision tree visualization in Power BI.

How to use decision tree in Power BI for prediction

Associative Rules

Learning the main concepts behind the Associative rules.

Where to use it, and how it brings insight for users.

Writing Associative rule codes in R.

Learning concepts such as support, confidence and Lift.

Learning how Apriori packages and its algorithm works.

How to evaluate the association rules result.

How to display it in Power BI.

How to improve the results.

How to use power BI visualization to better show the associative rules

Neural Network algorithm

The concepts behind it and how it works.

The ksvm() function.

Evaluate the model and how to improve the performance.

Regression algorithm

Concept behind the regression.

Predict numeric data (e.g. using linear regression) the function used to do the linear regression.

How to create a model that support the nonlinear models.

Multiple regression, logistic regression and so forth.

How to evaluate the results.

Recommendation (content-based filtering and collaborative filtering).

What is recommendation, and what is content filtering and collaborating filtering.

How to implement content filtering and collaborative filtering using R.

Example of recommending new items to a customer. First Using clustering algorithms (K-mean) for clustering the current customers based on their purchased behaviour. Then employing a multiple classification algorithm such as KNN to predict a new customer cluster, which lead to recommend the items to him.

Instructor: Dr. Leila Etaati

Dr. Leila Etaati gained her PhD in University of Auckland. She is world well-known speaker in Machine Learning and Analytics topics, and spoke in world’s best international conferences in Data Platform topics, such as; PASS Summits, PASS Rally, SQL Nexus, Microsoft Ignite, and so on. She has more than 10 years experience in Data Mining and Analytics. She is also Microsoft Most Valuable Professional (MVP) because of her dedication on Microsoft Analytics and Machine Learning technologies. She writes blog posts in RADACAD and also publishes YouTube videos in our channel. She also is an invited lecturer in universities such as University of Auckland, and Unitec, and some other universities. She worked in many industries including banking financial, power and utility, manufacturing, tourism, and so on.

Event Details

Event Details

Length: 3 days

Prerequisite: This course is a beginner to advanced level course. You don’t need any prerequisites for attending in this course.

Instructor: Dr. Leila Etaati

You keep hearing about the machine learning and R recently. R become a language for data analysis and machine learning that makes the need for learning it more clearly. Moreover, Microsoft introduces R server as a comprehensive platform for using machine learning and R functionality inside the data analysis tools such as SQL Server 2016 and Power BI.

At this training, you will learn:

Day 1: Azure ML, R basics

In the first day, you will be familiar with basic concepts of Machine Learning in Azure ML and some basic concepts of R in RStudio and Microsoft Visual studio 2015. In Day 1 you will learn;

How to use some of the main packages in R such as ggplot2, dplyer, sqldf and so forth.

The main concepts of basic statistics and how they can be helpful like: mean, median, standard division, and so forth.

Practice in R and using some packages

Day 2: R algorithms and Power BI

The main aim of days 2 is to learn some of main algorithms, and understand how they work and how they can solve different type of real life problems. You will familiar with these algorithms and their syntax in R, and how to use them in Power BI. Moreover, you will learn how to embed some R visualization in Power BI.

In day 2 you will learn;

How classification algorithm like KNN works. and its relevant code in R.

Decision Tree concepts and its relevant R codes

How Associative Rules works, and what is the concepts behind it, plus the relevant codes

How to write R codes in Power BI for transformation, and also creating new queries

Day 3: R algorithms and SQL Server 2016

In third day, you will learn how to bring analytics and intelligence using SQL server 2016. Moreover, you will also learn some other important machine learning algorithms.

In Day 3 you will learn:

Concepts and R codes for

Neural Network algorithm

Time series algorithm

Regression algorithm

Recommendation (content-based filtering and collaborative filtering). Moreover, you will learn how to use machine learning in SQL Server 2016 and in SSRS.

Using R SQL Server 2016 for rung the R code inside the SQL Server scripts will be shown.

The general procedure for creating R diagram in SSRS will be discussed

Two different scenarios will be discussed in class so audience will understand how to create a machine learning process in SQL Server 2016.

This course is full of hands on labs, and you will experiment all examples through real-world demos. At the end of the 3-day training course, you will be able to use techniques and concepts of this training in your Analytics challenges.

Instructor: Dr. Leila Etaati

Dr. Leila Etaati gained her PhD in University of Auckland. She is world well-known speaker in Machine Learning and Analytics topics, and spoke in world’s best international conferences in Data Platform topics, such as; PASS Summits, PASS Rally, SQL Nexus, Microsoft Ignite, and so on. She has more than 10 years experience in Data Mining and Analytics. She is also Microsoft Most Valuable Professional (MVP) because of her dedication on Microsoft Analytics and Machine Learning technologies. She writes blog posts in RADACAD and also publishes YouTube videos in our channel. She also is an invited lecturer in universities such as University of Auckland, and Unitec, and some other universities. She worked in many industries including banking financial, power and utility, manufacturing, tourism, and so on.

What others say about the training and trainer

I attended RADACADs “Advanced Analytics” course recently in Frankfurt in May 2017. Being a regular user of Power BI (with a science background ) the course was extremely helpful in showing me how to incorporate R data visualisations into Power BI dashboards and for introducing me to machine learning using the Microsoft ML Studio. Leila is an excellent and extremely knowledgeable instructor and explained complex data analytical concepts and methodologies in an easy-to-understand manner. I thoroughly recommend this course to anyone who wants to expand their data analytical skills and knowledge.

Event Details

Event Details

I spent probably about two weeks looking for a good Microsoft Power BI class that would cover all of the aspects of the program. I wanted to walk away from the class being able to apply what I learned immediately. Most training companies that I looked into were in a state of flux with Power BI mostly due to the transitions that Power BI itself was going through. Then I stumbled on the RADACAD web site. Reza Rad touted a course that he taught named “Power BI, From Rookie to Rock star.” If the class was indeed an A to Z course, then it was exactly what I was looking for. The class outline seemed to include every topic on my checkoff list plus much more. I decided to give Reza a try.

Reza completely lived up to his hype. He took us through a natural progression of the software, using example after example of how to complete a task. We went through real world scenarios, a bare minimum of two scenarios for each minor topic. I was completely impressed with his Power BI knowledge, understanding and abilities. Any question we came to Reza with was answered completely and with even more examples.

After the class, my goal of immediately applying what I learned was attained. Right away I was able to successfully pull and massage data, create useful reports and post them to dashboards.

I would recommend Radacad Power BI Training without hesitation.

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!

Course Details

This is the most comprehensive course for Power BI which split in daily modules. You can enroll in any of these modules separately or take the whole course. Modules designed independently, which means each module can be taken regardless of the order of modules. Here are list and detailed agenda of each module:

Module 1: Power BI Essentials

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

In this training course, you will learn Power BI from beginner to advance. You will learn how to use Power BI for simple data analysis situations as well as complex business intelligence scenarios. You will learn about Power BI Desktop, Power BI Website, and components of Power BI which are; Get and Transform (or Power Query), Modeling (or Power Pivot), and Visualization. You will also learn about Power Query Formula Language (Called M informally), and DAX. This course designed to give you an end to end view of Power BI, so you be able to use Power BI straight away in your everyday challenges for data analysis.

1.1: Introduction to Power BI

Power BI is the newest Microsoft Business Intelligence and Data Analysis tool. In this module, we will go through basics of this product, and introduce all five components of Power BI (Power Query, Power Pivot, Power View, Power Map, and Power Q&A). You will see some demos and introduction about Power BI desktop, Office 365 Power BI subscription, and Power BI website, and mobile apps. You will see some basic demos of how easy to use is Power BI in some scenarios.

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, Power Map, and Power Q&A.

1.2: Getting Data

Getting Data is the first experience of working with Power BI. You can connect to many data sources on-premises or on cloud. For some data sources, you can have a live or direct connection, for some connection works offline. For some connections, you need a gateway or connector to be installed. In this module, you will learn everything about get data experience of Power BI.

What is Power Query: Introduction to Data Mash-Up Engine of Power BI

Get Started with Power Query: Movies Data Mash-Up

Power BI Get Data from Excel: Everything You Need to Know

Definitive Guide to Power BI Personal Gateway and Enterprise Gateway

File Sources

Folder as a Source

Database Sources

Analysis Services Connection

Get Data from Azure SQL Database

Azure SQL Data Warehouse Source

Software as A Source

Web Source

1.3: Power Query for Data Transformation

Data analysis and BI world starts from data extraction and transformation. Power Query is the data transformation engine of Power BI. Power Query comes as part of Excel 2016, or as an add-in for Excel 2013 and 2010. Power Query is also part of Power BI Desktop. In this module you will learn about all different version of Power Query, their similarities, and differences, as well as configuration and requirements of using them.

Power Query uses a graphical user interface to apply transformations on the dataset. However Power Query works with a functional scripting language behind the scenes. The key to learn Power Query is learning the functional language called M. There are many features in M that are not available in the Power Query GUI. In this module you will learn how to understand M, and how to write M scripts even from scratch. You will learn writing custom functions in M as well as many other useful features.

The content that you will learn in this module includes but not limited to;

Different versions of Power Query

Power Query Introduction

Query Editor

Transformation GUI

Row Transformations

Column Transformations

Data Type

Adding Column

Text Transformations

Number Column Calculations

Date and Time Calculations

Data types, Lists, Records, and tables in M

M built-in functions

Generators in M

Writing Custom Functions

Error handling

Advanced Data Transformations with M

1.4: Data Modelling and DAX

Power Pivot is xVelocity in-memory data modelling engine of the Power BI. Modelling effectively is the key of high performance BI solution. In this module, you will learn basics of Power Pivot such as creating relationships, and calculated members, as well as advanced best practices and DAX expressions. DAX is Data Analytical eXpression language. DAX has similar structure to excel functions, but it is different. In this module, you will learn DAX from beginner to advance. You will learn basic functions as well as complex functions and scenarios of using them in real world challenges.

The content that you will learn in this module includes but not limited to;

Power Pivot xVelocity engine basics and concepts

Relationships in Power BI

Best practices

Introduction to DAX

Calculated Columns

Measures

Calculated Tables

Row Context vs Set Context

Advanced calculations using Calculate functions

Time Intelligence Functions

DAX Advanced

1.5: Data Visualization

Data Visualization is the front end of any BI application; this is the user view point of your system. It is critical to visualize measures, and dimensions effectively so the BI system could tell the story of the data clearly. In this module, you will learn conceptual best practices of data visualizations which is valid through all data visualization tools. You will learn Power View and Power Map skills. Power View is the interactive data visualization tool and Power Map is 3D geo-spatial data visualization tool. You will learn how to create effective charts, and dashboards using these tools as well as best practices for working with these tools. There are two version of Power View; Power View for Excel, and Power View for SharePoint which will be covered in this module as well.

The content that you will learn in this module includes but not limited to;

Power BI Desktop Visualization

Custom Visuals in Power BI Desktop

Formatting Visuals in Power BI Desktop

Waterfall Chart

Built-in Charts and Graphs in Power BI

Sorting, Filtering, and categorization

KPIs

Maps and Geo-Spatial Visualization

1.6: Power BI Service, Dashboards, Q&A, and Gateways

Power BI introduced a new engine for users which focus on users who wants to play with the data more, and understand the story behind the data better. Power Q&A is question and answering engine which works based on human natural language. Power Q&A automatically will be enabled on Power BI files deployed into Power BI website, or Office 365 subscription. However, there are some tips and tricks about how to design your model to get the best type of answers through Power Q&A. In this module, you will learn best practices for modelling that effect on the response of Power Q&A.

The content that you will learn in this module includes but not limited to;

Power Q&A introduction

Design best practices for Power Q&A

Power BI Service

Dashboard vs Report

Gateways

1.7: Administration & Security

Power BI components can be deployed into different environments such as Power BI website, Office 365 subscription, or even SharePoint on-premises. In this module, we will go through deployment options, Configurations and requirements required for each environment. You will learn how to deploy your Power BI files into Power BI website. you will also learn how to build dashboards in the website, and how to work with Mobile Apps for Apple, Android and Windows Phone.

The content that you will learn in this module includes but not limited to;

Groups and Work Spaces

Sharing

Row Level Security

Schedule Refresh

Module 2: Advanced Power Query and Data Transformation in Power BI

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

In this course, 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 hands-on labs and lecture. After this course you will be able to implement any types of data transformation through Power Query in Excel or Power BI.

Agenda;

2.1: Get Data

Different Data Sources

What is Power Query?

Power Query in Excel and Power BI

Basic Transformations

Get Data from Web

2.2: Query Editor

Base structures in Power Query

Query Editor in Details

Get Data from SQL Server

Get Data from CSV Files

Get Data from Excel

Get Data from JSON

Transformation Pane

2.3: Power Query Syntax

Data Types in Power Query

Query Properties

Transformation Steps

Query Operations; Duplicate, and Reference

2.4: Combine Queries

Merge, Joining queries

Append, creating a big list

Combine Binaries; Looping through files in a folder

2.5: Transformations

Column Operations

Row Operations

Filtering

Sorting

Add as new query / Drill Down

2.6: Table Transformations

Group By

Extending Group By with Expression Editor

Transpose

Pivot, Unpivot

2.7: Text Transformations

Split

Merge (Concatenate)

Parse

Extract

2.8: Numeric Transformations

Standard

Scientific

Statistics

2.9: More Transformations

Date Transformations (Year, Month, Quarter)

Extending Fiscal Date Column

Time Transformations

Adding Time/Date banding

Structured Column

Expand

Aggregate

Add Custom Column

2.10: Power Query Formula Language: M

What is M? and the importance of learning M

M Syntax

Things to Consider for M Scripting

Basic examples of M

Values in M

Logical Operations

2.11: Advanced M Scripting

#Shared Keyword; function library of Power Query

Parameters

Custom Functions

Error Handling in Power Query

Generators in Power Query: Implementing Loop Structure

EACH singleton function

2.12: Use Cases

Date Dimension with Power Query

Day Number of Year Custom Function

Looping through files in a folder with Power Query

Online Date Time Scheduler with Power Query

Module 3: Advanced Power BI Modeling and DAX

This course is module 3 of the Power BI from Rookie to Rock Star course.

In this course, 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;

3.1: Power BI Modeling 101

Relationship in Power BI

Relationship based on multiple Columns

Role Playing Dimension

Formatting

Hide/Unhide

Hierarchy

Basic Calculated Column and Measure

Sort by Column

3.2: Introduction to DAX

Syntax of DAX

Naming in DAX

Logical Operations

Data Types in Power BI Model

Overview of Functions

Variables

3.3: Functions in DAX in Details

Text Functions

Information Functions

Logical Functions

Date and Time Functions

Aggregation Functions

3.4: Filter, Value, and Relationship Functions

ALL

Examples of using ALL function

Filter Function

Related

RelatedTable

3.5: Evaluation Contexts

Row Context

Filter Context

Exception for Row Context

Exception for Filter Context

Calculate Function

3.6: Time Intelligence Functions

Year to Date, Quarter to Date

Fiscal Year to Date

Same Period Last Year

Running Total

Rolling 12 Month Sales

Average 12 Month Sales

Rolling 6 Months

Flexible banding

3.7: Calculated Tables

Role Playing Dimension

Top 10 Customers

More Scenarios of Using Calculated Tables

Performance Consideration

3.8: Best Practices Tips and Tricks

Solving DAX Now and Today Time zone Issue

Modeling Best Practices

Advanced Time Intelligence

Advanced Relationship in DAX

Hierarchies in DAX

Module 4: Advanced Data Visualization with Power BI

This course is module 4 of the Power BI from Rookie to Rock Star course.

In this course, you will learn all things about data visualization in Power BI. Day 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 visualizations 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 course with a Jedi level of visualization.

Agenda;

4.1: Visualization Basics

Basic Charts

Clustered vs Stacked

Quick Calcs in Power BI

Interaction of Visuals

Pages

4.2: Visualization Best Practices

Pie Chart; Be Careful

Coloring

Show me the Numbers

Which Chart is the Best?

Story Telling

Combining Visuals

4.3: Slicing and Dicing

Filters

Slicers

Filtering Slicers

Drill Down/Up

4.4: Custom Formatting Visuals

Labels

Legend

Color

Extended visualization formatting

Group / bin

4.5: Special Visuals

Waterfall Chart; Cashflow

Scatter Chart; Story Telling

Line Chart; Powerful

Table and Matrix with Conditional Formatting

R Visualization

4.6: Map Visualization

Default map visual

Filled Map; Good, bad, and the ugly

Shape Map

Custom Map Visuals

ArcGIS

4.7: Custom Visuals

Using Custom Visuals

SynopticPanel by SQLBI

Chicklet Slicer

3D Map with GlobeMap Visual

Sunburst

Time Slicers

4.8: KPIs in Power BI

KPI Default visual

Dial Gauge

Linear Gauge

Bullet Chart

4.9: Building a Custom Visual

Basics of scripting language

Writing first simple custom visual

Components of a custom visual component

Module 5: Power BI Service, Gateways, and Architecture

This course is module 5 of the Power BI from Rookie to Rock Star course.

In this course, 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 installation 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 architecting 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 course will be delivered in lecture style plus hands on lab experiments.

Agenda;

5.1: Gateways

What is Gateway?

Two types of Gateway

Personal Gateway; an example

On-premises Gateway; not only for Power BI

5.2: On-premises Gateway in Details

Requirements for on-premises gateway

Architecture Flow diagram of the gateway

Installation and Configuration considerations

Install and Configure Gateway example

Data Source Definition in Power BI Service

Schedule Refresh

5.3: Gateway Performance

Performance Considerations

Limitations of Gateway

Gateway for SSAS Live data source

5.4: DirectQuery

Step beyond 1/10GB Limitation in Power BI

Limitations of DirectQuery

Dashboard and Report considerations for DirectQuery

Performance Considerations

5.5: Live Query

Using SSAS Live data source

Difference of DirectQuery and Live Query

Enterprise Scale architecture

UPN Mapping

Architecture of Gateway and SSAS Live Connection

5.6: Power BI Service

Dashboard vs Report

Dashboard Tiles

Data Classification

Power Q&A

Tips and Tricks for Power Q&A

5.7: Sharing

Dashboard Sharing

Work Groups in Power BI

Content Packs

Publish to Web

Comparison of all Sharing methods

Sharing Architecture for enterprise

5.8: Administration

Admin Portal

Set Power BI Administrator

Tenant Settings

Audit Log

5.9: Row Level Security

Row Level Security in Power BI; regardless of Data Source

Row Level Security in SSAS Live Connection

Dynamic Row Level Security

5.10: Integration

Analyze in Excel

Power BI Publisher for Excel

Reporting Services and Power BI

Power BI and Power Point

5.11: Real-time

REST API of Power BI

Real-time Tiles

Azure Stream Analytics

5.12: Architecture

Architecture for Enterprise Scale

Architecture for Reporting

Architecture for Self-Service

Licensing

Instructor: Reza Rad

Reza Rad is invited speaker in world’s best and biggest SQL Server and BI conferences such as PASS Summits, PASS Rallys, PASS BA Conference, SQLBits, TechEds, Ignites, and many other conferences. 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 Data Platform 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

Prices: (GST Exclusive)

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!

Event Details

Event Details

Prerequisite: This course is a beginner to advanced level course. You don’t need any prerequisites for attending in this course.

Instructor: Dr. Leila Etaati

You’ve heard about the machine learning and R recently. R become a language for data analysis and machine learning. R also can be used for visualization widely. Microsoft power BI as a self-service BI tool helps people to easily extract data from different sources, apply data transformation and do data wrangling, and finally load and visual data in an appropriate way. Recently, Microsoft enabled users to use R codes and visuals inside the power BI which is very beneficial for Data Scientists, Analysts, and BI developers.

At this training, you will learn:

Day 1. Basics of R and R Visualization inside Power BI, and Basics of Machine Learning

In the first day, you will be familiar with the R language basics, the important of R packages for data cleaning, data manipulation, and data visualization. Also, you will learn how to embeded some of the R visualizations in Power BI. At the end of the day we will move to machine learning component. This day includes, but not limited to contents below;

Installing R studio, and setting up R in power BI,

Understanding main data structures in R such as Data frame, Vector and List.

Learning the main concepts of basic statistics. Learning how they can be helpful, statistics operations like: mean, median, standard division, and so forth will be discussed.

Getting familiar with the main packages like dplyer for data cleaning and manipulating. Functions like filter() for Comparisons, Logical operators, Missing values. Or function “select”.

Learning how to use some of the main packages in R such as ggplot2 to visualize data. Learning how to visualise data with aim of data comparison (among items and over times), relationship between variables (two or more variables), data distributions (few data point, large data, two or three variables), and data composition (static or changing over time).

How to write R codes in Power BI for transformation, and also creating new queries

How to write Simple R code for correlation analysis in R

How to create R visuals in Power BI

Do data transformation in Power BI with R functions

How to create interactive R reports in Power BI

How classification algorithm like KNN works. and its relevant code in R.

KNN concepts

Write the KNN in R code

How to evaluate the result

How to improve the result by changing related parameters

Day 2. Machine Learning inside Power BI

The main aim of days 2 is to learn some of the main machine learning algorithms, and understand how they work, and how they can solve different type of real life problems. You will be familiar with these algorithms and their syntax in R, and how to use them in Power BI. Examples of Predictive analytics, Descriptive analytics, and prescriptive analytics will be covered in this day of training. This day includes, but not limited to contents below;

Decision Tree concepts and its relevant R codes

What is decision tree, the concepts and how it works.

Introduction to different type of decision tree packages that are more common such as rpart and C5.

How to change some of paraments of Rpart and C5, how to draw and customize a decision tree

Learn how to evaluate result and improve the model.

See how to have a decision tree visualization in Power BI.

How to use decision tree in Power BI for prediction

Associative Rules

Learning the main concepts behind the Associative rules.

Where to use it, and how it brings insight for users.

Writing Associative rule codes in R.

Learning concepts such as support, confidence and Lift.

Learning how Apriori packages and its algorithm works.

How to evaluate the association rules result.

How to display it in Power BI.

How to improve the results.

How to use power BI visualization to better show the associative rules

Neural Network algorithm

The concepts behind it and how it works.

The ksvm() function.

Evaluate the model and how to improve the performance.

Regression algorithm

Concept behind the regression.

Predict numeric data (e.g. using linear regression) the function used to do the linear regression.

How to create a model that support the nonlinear models.

Multiple regression, logistic regression and so forth.

How to evaluate the results.

Recommendation (content-based filtering and collaborative filtering).

What is recommendation, and what is content filtering and collaborating filtering.

How to implement content filtering and collaborative filtering using R.

Example of recommending new items to a customer. First Using clustering algorithms (K-mean) for clustering the current customers based on their purchased behaviour. Then employing a multiple classification algorithm such as KNN to predict a new customer cluster, which lead to recommend the items to him.

Instructor: Dr. Leila Etaati

Dr. Leila Etaati gained her PhD in University of Auckland. She is world well-known speaker in Machine Learning and Analytics topics, and spoke in world’s best international conferences in Data Platform topics, such as; PASS Summits, PASS Rally, SQL Nexus, Microsoft Ignite, and so on. She has more than 10 years experience in Data Mining and Analytics. She is also Microsoft Most Valuable Professional (MVP) because of her dedication on Microsoft Analytics and Machine Learning technologies. She writes blog posts in RADACAD and also publishes YouTube videos in our channel. She also is an invited lecturer in universities such as University of Auckland, and Unitec, and some other universities. She worked in many industries including banking financial, power and utility, manufacturing, tourism, and so on.

Event Details

Course Title: Power BI from Rookie to Rock Star

Length:

Instructor: Reza Rad

In this training course you will learn Power BI from beginner to advance. You will learn how to use Power BI for simple data analysis situations as well as complex business intelligence scenarios. You will learn about Power BI Components such as Power Query (Get Data and Transform), Modeling and DAX, Visualization, Power BI Desktop as the main tool, Power BI Service, Gateway configuration, and architecture. You will learn all the concepts with live demos. Expect learning best practices with great scenarios in this course.

Power BI from Rookie to Rock Star

This is the most comprehensive course for Power BI which split in daily modules. You can enroll in any of these modules separately or take the whole course. Modules designed independently, which means each module can be taken regardless of the order of modules. Here are list and detailed agenda of each module:

Module 1: Power BI Essentials

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

In this training course, you will learn Power BI from beginner to advance. You will learn how to use Power BI for simple data analysis situations as well as complex business intelligence scenarios. You will learn about Power BI Desktop, Power BI Website, and components of Power BI which are; Get and Transform (or Power Query), Modeling (or Power Pivot), and Visualization. You will also learn about Power Query Formula Language (Called M informally), and DAX. This course designed to give you an end to end view of Power BI, so you be able to use Power BI straight away in your everyday challenges for data analysis.

1.1: Introduction to Power BI

Power BI is the newest Microsoft Business Intelligence and Data Analysis tool. In this module, we will go through basics of this product, and introduce all five components of Power BI (Power Query, Power Pivot, Power View, Power Map, and Power Q&A). You will see some demos and introduction about Power BI desktop, Office 365 Power BI subscription, and Power BI website, and mobile apps. You will see some basic demos of how easy to use is Power BI in some scenarios.

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, Power Map, and Power Q&A.

1.2: Getting Data

Getting Data is the first experience of working with Power BI. You can connect to many data sources on-premises or on cloud. For some data sources, you can have a live or direct connection, for some connection works offline. For some connections, you need a gateway or connector to be installed. In this module, you will learn everything about get data experience of Power BI.

What is Power Query: Introduction to Data Mash-Up Engine of Power BI

Get Started with Power Query: Movies Data Mash-Up

Power BI Get Data from Excel: Everything You Need to Know

Definitive Guide to Power BI Personal Gateway and Enterprise Gateway

File Sources

Folder as a Source

Database Sources

Analysis Services Connection

Get Data from Azure SQL Database

Azure SQL Data Warehouse Source

Software as A Source

Web Source

1.3: Power Query for Data Transformation

Data analysis and BI world starts from data extraction and transformation. Power Query is the data transformation engine of Power BI. Power Query comes as part of Excel 2016, or as an add-in for Excel 2013 and 2010. Power Query is also part of Power BI Desktop. In this module you will learn about all different version of Power Query, their similarities, and differences, as well as configuration and requirements of using them.

Power Query uses a graphical user interface to apply transformations on the dataset. However Power Query works with a functional scripting language behind the scenes. The key to learn Power Query is learning the functional language called M. There are many features in M that are not available in the Power Query GUI. In this module you will learn how to understand M, and how to write M scripts even from scratch. You will learn writing custom functions in M as well as many other useful features.

The content that you will learn in this module includes but not limited to;

Different versions of Power Query

Power Query Introduction

Query Editor

Transformation GUI

Row Transformations

Column Transformations

Data Type

Adding Column

Text Transformations

Number Column Calculations

Date and Time Calculations

Data types, Lists, Records, and tables in M

M built-in functions

Generators in M

Writing Custom Functions

Error handling

Advanced Data Transformations with M

1.4: Data Modelling and DAX

Power Pivot is xVelocity in-memory data modelling engine of the Power BI. Modelling effectively is the key of high performance BI solution. In this module, you will learn basics of Power Pivot such as creating relationships, and calculated members, as well as advanced best practices and DAX expressions. DAX is Data Analytical eXpression language. DAX has similar structure to excel functions, but it is different. In this module, you will learn DAX from beginner to advance. You will learn basic functions as well as complex functions and scenarios of using them in real world challenges.

The content that you will learn in this module includes but not limited to;

Power Pivot xVelocity engine basics and concepts

Relationships in Power BI

Best practices

Introduction to DAX

Calculated Columns

Measures

Calculated Tables

Row Context vs Set Context

Advanced calculations using Calculate functions

Time Intelligence Functions

DAX Advanced

1.5: Data Visualization

Data Visualization is the front end of any BI application; this is the user view point of your system. It is critical to visualize measures, and dimensions effectively so the BI system could tell the story of the data clearly. In this module, you will learn conceptual best practices of data visualizations which is valid through all data visualization tools. You will learn Power View and Power Map skills. Power View is the interactive data visualization tool and Power Map is 3D geo-spatial data visualization tool. You will learn how to create effective charts, and dashboards using these tools as well as best practices for working with these tools. There are two version of Power View; Power View for Excel, and Power View for SharePoint which will be covered in this module as well.

The content that you will learn in this module includes but not limited to;

Power BI Desktop Visualization

Custom Visuals in Power BI Desktop

Formatting Visuals in Power BI Desktop

Waterfall Chart

Built-in Charts and Graphs in Power BI

Sorting, Filtering, and categorization

KPIs

Maps and Geo-Spatial Visualization

1.6: Power BI Service, Dashboards, Q&A, and Gateways

Power BI introduced a new engine for users which focus on users who wants to play with the data more, and understand the story behind the data better. Power Q&A is question and answering engine which works based on human natural language. Power Q&A automatically will be enabled on Power BI files deployed into Power BI website, or Office 365 subscription. However, there are some tips and tricks about how to design your model to get the best type of answers through Power Q&A. In this module, you will learn best practices for modelling that effect on the response of Power Q&A.

The content that you will learn in this module includes but not limited to;

Power Q&A introduction

Design best practices for Power Q&A

Power BI Service

Dashboard vs Report

Gateways

1.7: Administration & Security

Power BI components can be deployed into different environments such as Power BI website, Office 365 subscription, or even SharePoint on-premises. In this module, we will go through deployment options, Configurations and requirements required for each environment. You will learn how to deploy your Power BI files into Power BI website. you will also learn how to build dashboards in the website, and how to work with Mobile Apps for Apple, Android and Windows Phone.

The content that you will learn in this module includes but not limited to;

Groups and Work Spaces

Sharing

Row Level Security

Schedule Refresh

Module 2: Advanced Power Query and Data Transformation in Power BI

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

In this course, 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 hands-on labs and lecture. After this course you will be able to implement any types of data transformation through Power Query in Excel or Power BI.

Agenda;

2.1: Get Data

Different Data Sources

What is Power Query?

Power Query in Excel and Power BI

Basic Transformations

Get Data from Web

2.2: Query Editor

Base structures in Power Query

Query Editor in Details

Get Data from SQL Server

Get Data from CSV Files

Get Data from Excel

Get Data from JSON

Transformation Pane

2.3: Power Query Syntax

Data Types in Power Query

Query Properties

Transformation Steps

Query Operations; Duplicate, and Reference

2.4: Combine Queries

Merge, Joining queries

Append, creating a big list

Combine Binaries; Looping through files in a folder

2.5: Transformations

Column Operations

Row Operations

Filtering

Sorting

Add as new query / Drill Down

2.6: Table Transformations

Group By

Extending Group By with Expression Editor

Transpose

Pivot, Unpivot

2.7: Text Transformations

Split

Merge (Concatenate)

Parse

Extract

2.8: Numeric Transformations

Standard

Scientific

Statistics

2.9: More Transformations

Date Transformations (Year, Month, Quarter)

Extending Fiscal Date Column

Time Transformations

Adding Time/Date banding

Structured Column

Expand

Aggregate

Add Custom Column

2.10: Power Query Formula Language: M

What is M? and the importance of learning M

M Syntax

Things to Consider for M Scripting

Basic examples of M

Values in M

Logical Operations

2.11: Advanced M Scripting

#Shared Keyword; function library of Power Query

Parameters

Custom Functions

Error Handling in Power Query

Generators in Power Query: Implementing Loop Structure

EACH singleton function

2.12: Use Cases

Date Dimension with Power Query

Day Number of Year Custom Function

Looping through files in a folder with Power Query

Online Date Time Scheduler with Power Query

Module 3: Advanced Power BI Modeling and DAX

This course is module 3 of the Power BI from Rookie to Rock Star course.

In this course, 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;

3.1: Power BI Modeling 101

Relationship in Power BI

Relationship based on multiple Columns

Role Playing Dimension

Formatting

Hide/Unhide

Hierarchy

Basic Calculated Column and Measure

Sort by Column

3.2: Introduction to DAX

Syntax of DAX

Naming in DAX

Logical Operations

Data Types in Power BI Model

Overview of Functions

Variables

3.3: Functions in DAX in Details

Text Functions

Information Functions

Logical Functions

Date and Time Functions

Aggregation Functions

3.4: Filter, Value, and Relationship Functions

ALL

Examples of using ALL function

Filter Function

Related

RelatedTable

3.5: Evaluation Contexts

Row Context

Filter Context

Exception for Row Context

Exception for Filter Context

Calculate Function

3.6: Time Intelligence Functions

Year to Date, Quarter to Date

Fiscal Year to Date

Same Period Last Year

Running Total

Rolling 12 Month Sales

Average 12 Month Sales

Rolling 6 Months

Flexible banding

3.7: Calculated Tables

Role Playing Dimension

Top 10 Customers

More Scenarios of Using Calculated Tables

Performance Consideration

3.8: Best Practices Tips and Tricks

Solving DAX Now and Today Time zone Issue

Modeling Best Practices

Advanced Time Intelligence

Advanced Relationship in DAX

Hierarchies in DAX

Module 4: Advanced Data Visualization with Power BI

This course is module 4 of the Power BI from Rookie to Rock Star course.

In this course, you will learn all things about data visualization in Power BI. Day 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 visualizations 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 course with a Jedi level of visualization.

Agenda;

4.1: Visualization Basics

Basic Charts

Clustered vs Stacked

Quick Calcs in Power BI

Interaction of Visuals

Pages

4.2: Visualization Best Practices

Pie Chart; Be Careful

Coloring

Show me the Numbers

Which Chart is the Best?

Story Telling

Combining Visuals

4.3: Slicing and Dicing

Filters

Slicers

Filtering Slicers

Drill Down/Up

4.4: Custom Formatting Visuals

Labels

Legend

Color

Extended visualization formatting

Group / bin

4.5: Special Visuals

Waterfall Chart; Cashflow

Scatter Chart; Story Telling

Line Chart; Powerful

Table and Matrix with Conditional Formatting

R Visualization

4.6: Map Visualization

Default map visual

Filled Map; Good, bad, and the ugly

Shape Map

Custom Map Visuals

ArcGIS

4.7: Custom Visuals

Using Custom Visuals

SynopticPanel by SQLBI

Chicklet Slicer

3D Map with GlobeMap Visual

Sunburst

Time Slicers

4.8: KPIs in Power BI

KPI Default visual

Dial Gauge

Linear Gauge

Bullet Chart

4.9: Building a Custom Visual

Basics of scripting language

Writing first simple custom visual

Components of a custom visual component

Module 5: Power BI Service, Gateways, and Architecture

This course is module 5 of the Power BI from Rookie to Rock Star course.

In this course, 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 installation 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 architecting 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 course will be delivered in lecture style plus hands on lab experiments.

Agenda;

5.1: Gateways

What is Gateway?

Two types of Gateway

Personal Gateway; an example

On-premises Gateway; not only for Power BI

5.2: On-premises Gateway in Details

Requirements for on-premises gateway

Architecture Flow diagram of the gateway

Installation and Configuration considerations

Install and Configure Gateway example

Data Source Definition in Power BI Service

Schedule Refresh

5.3: Gateway Performance

Performance Considerations

Limitations of Gateway

Gateway for SSAS Live data source

5.4: DirectQuery

Step beyond 1/10GB Limitation in Power BI

Limitations of DirectQuery

Dashboard and Report considerations for DirectQuery

Performance Considerations

5.5: Live Query

Using SSAS Live data source

Difference of DirectQuery and Live Query

Enterprise Scale architecture

UPN Mapping

Architecture of Gateway and SSAS Live Connection

5.6: Power BI Service

Dashboard vs Report

Dashboard Tiles

Data Classification

Power Q&A

Tips and Tricks for Power Q&A

5.7: Sharing

Dashboard Sharing

Work Groups in Power BI

Content Packs

Publish to Web

Comparison of all Sharing methods

Sharing Architecture for enterprise

5.8: Administration

Admin Portal

Set Power BI Administrator

Tenant Settings

Audit Log

5.9: Row Level Security

Row Level Security in Power BI; regardless of Data Source

Row Level Security in SSAS Live Connection

Dynamic Row Level Security

5.10: Integration

Analyze in Excel

Power BI Publisher for Excel

Reporting Services and Power BI

Power BI and Power Point

5.11: Real-time

REST API of Power BI

Real-time Tiles

Azure Stream Analytics

5.12: Architecture

Architecture for Enterprise Scale

Architecture for Reporting

Architecture for Self-Service

Licensing

Power BI Free

Power BI Pro

Power BI Premium

Why Power BI Training?

Power BI is the newest Microsoft BI tool for data mash up, build modelling, and visualize it effectively. It supports mobile devices, it support self-service, and along with its great functionalities, it doesn’t require heavy development or production system.

Common Myths about Power BI

There are some myths about Power BI that sometimes prevents people and organization to move towards using this product. We’ve listed some of myths in following. Please note that these are Myths, and they are not right assumptions.

Myth 1: Power BI is a self-service Excel tool, and isn’t suitable for Enterprise Solutions.

There are many scenarios that Power BI has been used in enterprise solutions. Power BI features for data mash up (with the very powerful data transformation component: Power Query), and data modelling based on xVelocity in-memory engine (Power Pivot), and great data visualization components such as Power View and Power Map, along with mobility support of this product, are rarely available in other products. We have designed, implemented and have seem many BI enterprise solutions based on Power BI within very large organizations.

Myth 2: Power BI is easy to use, and doesn’t require training course.

Power BI is an easy to use tool, but for basic functions. Every BI application requires calculated measure, which needs to be written in DAX. Every BI application requires advanced data transformations which need to be handled with Power Query M scripting language. Designing and implementing solutions with Best Practices is always a requirement for reliable BI applications. We designed our Power BI training in a way that delivers advanced content for all components of Power BI, and fortify your team’s knowledge on the product, so they can solve any complex challenge with this tool.

Myth 3: Power BI is only for Microsoft based environments and platforms.

Power Query is the core of data extraction in Power BI. Power Query supports wide range of data sources. Not only SQL Server, but also Oracle, MySQL, web service result set, DB2 and many other data source are supported. There is also possibility to connect to many systems such as salesforce. The Visualization component of Power BI is also HTML5 supported, and is available for all mobile devices. Power BI app in AppStore, and Google PlayStore as well as Microsoft AppStore enables dashboards and data visualizations to be available in all environments.

Myth 4: Power BI is not a powerful and fully functional BI tool, and cannot be compared with other tools in the market.

In fact the reverse is true. Power BI is such powerful that can be replacement of many other expensive BI tools in the market. We’ve been through scenarios of successful Power BI solutions that have been replacement of Cognos, IBM products, Oracle WB, SAP BO, and so on. Power BI has 5 powerful components that cover main aspects of BI and data analysis system.

In addition there are always possibility to use this as a hybrid approach and save lots of time and money along with great features that are only available in this product.

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 book from Rookie to Rock Star.

In-Person Training;

Our Power BI in person training will be held in high quality hotels with recommendation for hotel bookings for attendees. There will be special group rating fee as well as early bird and past attendees discount. for schedule of our in person training follow this link:

Online Training;

We run online training with GoToWebinar and GoToTraining applications. These applications provide highly reliable communication channel between instructor and attendees. For schedule of our online training follow this link:

What others say about the training and trainer

I spent probably about two weeks looking for a good Microsoft Power BI class that would cover all of the aspects of the program. I wanted to walk away from the class being able to apply what I learned immediately. Most training companies that I looked into were in a state of flux with Power BI mostly due to the transitions that Power BI itself was going through. Then I stumbled on the RADACAD web site. Reza Rad touted a course that he taught named “Power BI, From Rookie to Rock star.” If the class was indeed an A to Z course, then it was exactly what I was looking for. The class outline seemed to include every topic on my checkoff list plus much more. I decided to give Reza a try.
Reza completely lived up to his hype. He took us through a natural progression of the software, using example after example of how to complete a task. We went through real world scenarios, a bare minimum of two scenarios for each minor topic. I was completely impressed with his Power BI knowledge, understanding and abilities. Any question we came to Reza with was answered completely and with even more examples.
After the class, my goal of immediately applying what I learned was attained. Right away I was able to successfully pull and massage data, create useful reports and post them to dashboards.
I would recommend Radacad Power BI Training without hesitation.

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!

You keep hearing about the machine learning and R recently. R become a language for data analysis and machine learning that makes the need for learning it more clearly. Moreover, Microsoft introduces R server as a comprehensive platform for using machine learning and R functionality inside the data analysis tools such as SQL Server 2016 and Power BI.

This training is three days of training using Machine Learning and R with SQL Server, Power BI, and concepts of Machine Learning.

Length: 5 days

Prerequisite: This course is a beginner to advanced level course. You don’t need any prerequisites for attending in this course.

Instructor: Dr. Leila Etaati

You keep hearing about the machine learning and R recently. R become a language for data analysis and machine learning that makes the need for learning it more clearly. Moreover, Microsoft introduces R server as a comprehensive platform for using machine learning and R functionality inside the data analysis tools such as SQL Server 2016 and Power BI.

At this training, you will learn:

Day 1: Azure ML, R Basics

In the first day, you will be familiar with the basic concepts of Machine Learning in Azure ML and some basic concepts of R in R Studio and Microsoft Visual studio 2015. In Day 1 you will learn;

sqldf to use SQL Statements for query dataset, RODBC for fetching data from SQL database

The main concepts of basic statistics and how they can be helpful like: mean, median, standard division, and so forth will be discussed.

Practice in R and using some packages

Day 2: R algorithms and Power BI

The main aim of days 2 is to learn some of main machine learning algorithms, and understand how they work and how they can solve different type of real life problems. You will familiar with these algorithms and their syntax in R, and how to use them in Power BI. Moreover, you will learn how to embed some R visualization in Power BI.

In day 2 you will learn;

How classification algorithm like KNN works. and its relevant code in R.

KNN concepts

Write the KNN in R code

How to evaluate the result

How to improve the result by changing related parameters

Decision Tree concepts and its relevant R codes

What is decision tree, the concepts and how it works

Introduce different type of decision tree algorithms that are more common.

Introduce Rpart packages, how to write the related R code in Rstudio and Rvisual studio

See the Rpart package in Power BI visual

Package C50 for decision tree how to evaluate result and how to improve the model

Associative Rules

Audience will learn the main concepts behid the Associative rules

Where to use it and how it brings insight for users

They will learn to write the code in R

Become familiar with concepts such as support, confidence and Lift.

Learn how Apriori packages and algorithm works

How to evaluate the association rules result

How to display it in Power BI

How to improve the results

How to write R codes in Power BI for transformation, and also creating new queries

How to set up the R in Power BI

How to write Simple R code for correlation analysis in R

How to create R visuals in Power BI

Do data transformation in Power BI with R functions

How to create interactive R reports in Power BI

How to call azure ML API into Power BI

Shows how to create functions in Power BI to call the API from Azure ML

Create a prediction model in Power BI and show the result in visualization.

Day 3: R algorithms and SQL Server 2016

In third day, you will learn how to bring analytics and intelligence using SQL server 2016. Moreover, you will also learn some other important machine learning algorithms.

In Day 3 you will learn:

Neural Network algorithm

The concepts behind it and how it works

The ksvm() function

Evaluate the model and how to improve the performance

Time series algorithm

Time series concept difference between seasonality and trend

The function that is used to create the time series model

How to improve and evaluate it

Regression algorithm

Concept behind the regression

Predict numeric data (e.g. using linear regression) the function used to do the linear regression

How to create a model that support the nonlinear models

Multiple regression, logistic regression and so forth

How to evaluate the results

Recommendation (content-based filtering and collaborative filtering).

What is recommendation and what is content filtering and collaborating filtering

How to implement content filtering and collaborative filtering

Using classification and clustering algorithms to create a recommendation system

Azure ML recommendation model

Moreover, you will learn how to use machine learning in SQL Server 2016 and in SSRS.

Set up SQL Server to run the R scripts

The best practice for implementing a prediction process in SQL Server 2016

Create a correlation analysis in SQL Server

Create a decision tree diagram in SSRS via writing code in R

See an end to end predictive analysis code from getting data, create models, evaluate models and publish the result.

Practice: creating a predictive solution in SQL Server 2016

This course is full of hands on labs, and you will experiment all examples through real-world demos. At the end of the 3-day training course, you will be able to use techniques and concepts of this training in your Analytics challenges.

Instructor: Dr. Leila Etaati

Dr. Leila Etaati gained her PhD in University of Auckland. She is world well-known speaker in Machine Learning and Analytics topics, and spoke in world’s best international conferences in Data Platform topics, such as; PASS Summits, PASS Rally, SQL Nexus, Microsoft Ignite, and so on. She has more than 10 years experience in Data Mining and Analytics. She is also Microsoft Most Valuable Professional (MVP) because of her dedication on Microsoft Analytics and Machine Learning technologies. She writes blog posts in RADACAD and also publishes YouTube videos in our channel. She also is an invited lecturer in universities such as University of Auckland, and Unitec, and some other universities. She worked in many industries including banking financial, power and utility, manufacturing, tourism, and so on.

What others say about the training and trainer

Kenny McMillan, Sports Physiologist / Data Analyst, Frankfurt, Germany:
I attended RADACADs “Advanced Analytics” course recently in Frankfurt in May 2017. Being a regular user of Power BI (with a science background ) the course was extremely helpful in showing me how to incorporate R data visualisations into Power BI dashboards and for introducing me to machine learning using the Microsoft ML Studio. Leila is an excellent and extremely knowledgeable instructor and explained complex data analytical concepts and methodologies in an easy-to-understand manner. I thoroughly recommend this course to anyone who wants to expand their data analytical skills and knowledge.

Event Details

Course Title: Power BI from Rookie to Rock Star

Length:

Instructor: Reza Rad

In this training course you will learn Power BI from beginner to advance. You will learn how to use Power BI for simple data analysis situations as well as complex business intelligence scenarios. You will learn about Power BI Components such as Power Query (Get Data and Transform), Modeling and DAX, Visualization, Power BI Desktop as the main tool, Power BI Service, Gateway configuration, and architecture. You will learn all the concepts with live demos. Expect learning best practices with great scenarios in this course.

Power BI from Rookie to Rock Star

This is the most comprehensive course for Power BI which split in daily modules. You can enroll in any of these modules separately or take the whole course. Modules designed independently, which means each module can be taken regardless of the order of modules. Here are list and detailed agenda of each module:

Module 1: Power BI Essentials

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

In this training course, you will learn Power BI from beginner to advance. You will learn how to use Power BI for simple data analysis situations as well as complex business intelligence scenarios. You will learn about Power BI Desktop, Power BI Website, and components of Power BI which are; Get and Transform (or Power Query), Modeling (or Power Pivot), and Visualization. You will also learn about Power Query Formula Language (Called M informally), and DAX. This course designed to give you an end to end view of Power BI, so you be able to use Power BI straight away in your everyday challenges for data analysis.

1.1: Introduction to Power BI

Power BI is the newest Microsoft Business Intelligence and Data Analysis tool. In this module, we will go through basics of this product, and introduce all five components of Power BI (Power Query, Power Pivot, Power View, Power Map, and Power Q&A). You will see some demos and introduction about Power BI desktop, Office 365 Power BI subscription, and Power BI website, and mobile apps. You will see some basic demos of how easy to use is Power BI in some scenarios.

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, Power Map, and Power Q&A.

1.2: Getting Data

Getting Data is the first experience of working with Power BI. You can connect to many data sources on-premises or on cloud. For some data sources, you can have a live or direct connection, for some connection works offline. For some connections, you need a gateway or connector to be installed. In this module, you will learn everything about get data experience of Power BI.

What is Power Query: Introduction to Data Mash-Up Engine of Power BI

Get Started with Power Query: Movies Data Mash-Up

Power BI Get Data from Excel: Everything You Need to Know

Definitive Guide to Power BI Personal Gateway and Enterprise Gateway

File Sources

Folder as a Source

Database Sources

Analysis Services Connection

Get Data from Azure SQL Database

Azure SQL Data Warehouse Source

Software as A Source

Web Source

1.3: Power Query for Data Transformation

Data analysis and BI world starts from data extraction and transformation. Power Query is the data transformation engine of Power BI. Power Query comes as part of Excel 2016, or as an add-in for Excel 2013 and 2010. Power Query is also part of Power BI Desktop. In this module you will learn about all different version of Power Query, their similarities, and differences, as well as configuration and requirements of using them.

Power Query uses a graphical user interface to apply transformations on the dataset. However Power Query works with a functional scripting language behind the scenes. The key to learn Power Query is learning the functional language called M. There are many features in M that are not available in the Power Query GUI. In this module you will learn how to understand M, and how to write M scripts even from scratch. You will learn writing custom functions in M as well as many other useful features.

The content that you will learn in this module includes but not limited to;

Different versions of Power Query

Power Query Introduction

Query Editor

Transformation GUI

Row Transformations

Column Transformations

Data Type

Adding Column

Text Transformations

Number Column Calculations

Date and Time Calculations

Data types, Lists, Records, and tables in M

M built-in functions

Generators in M

Writing Custom Functions

Error handling

Advanced Data Transformations with M

1.4: Data Modelling and DAX

Power Pivot is xVelocity in-memory data modelling engine of the Power BI. Modelling effectively is the key of high performance BI solution. In this module, you will learn basics of Power Pivot such as creating relationships, and calculated members, as well as advanced best practices and DAX expressions. DAX is Data Analytical eXpression language. DAX has similar structure to excel functions, but it is different. In this module, you will learn DAX from beginner to advance. You will learn basic functions as well as complex functions and scenarios of using them in real world challenges.

The content that you will learn in this module includes but not limited to;

Power Pivot xVelocity engine basics and concepts

Relationships in Power BI

Best practices

Introduction to DAX

Calculated Columns

Measures

Calculated Tables

Row Context vs Set Context

Advanced calculations using Calculate functions

Time Intelligence Functions

DAX Advanced

1.5: Data Visualization

Data Visualization is the front end of any BI application; this is the user view point of your system. It is critical to visualize measures, and dimensions effectively so the BI system could tell the story of the data clearly. In this module, you will learn conceptual best practices of data visualizations which is valid through all data visualization tools. You will learn Power View and Power Map skills. Power View is the interactive data visualization tool and Power Map is 3D geo-spatial data visualization tool. You will learn how to create effective charts, and dashboards using these tools as well as best practices for working with these tools. There are two version of Power View; Power View for Excel, and Power View for SharePoint which will be covered in this module as well.

The content that you will learn in this module includes but not limited to;

Power BI Desktop Visualization

Custom Visuals in Power BI Desktop

Formatting Visuals in Power BI Desktop

Waterfall Chart

Built-in Charts and Graphs in Power BI

Sorting, Filtering, and categorization

KPIs

Maps and Geo-Spatial Visualization

1.6: Power BI Service, Dashboards, Q&A, and Gateways

Power BI introduced a new engine for users which focus on users who wants to play with the data more, and understand the story behind the data better. Power Q&A is question and answering engine which works based on human natural language. Power Q&A automatically will be enabled on Power BI files deployed into Power BI website, or Office 365 subscription. However, there are some tips and tricks about how to design your model to get the best type of answers through Power Q&A. In this module, you will learn best practices for modelling that effect on the response of Power Q&A.

The content that you will learn in this module includes but not limited to;

Power Q&A introduction

Design best practices for Power Q&A

Power BI Service

Dashboard vs Report

Gateways

1.7: Administration & Security

Power BI components can be deployed into different environments such as Power BI website, Office 365 subscription, or even SharePoint on-premises. In this module, we will go through deployment options, Configurations and requirements required for each environment. You will learn how to deploy your Power BI files into Power BI website. you will also learn how to build dashboards in the website, and how to work with Mobile Apps for Apple, Android and Windows Phone.

The content that you will learn in this module includes but not limited to;

Groups and Work Spaces

Sharing

Row Level Security

Schedule Refresh

Module 2: Advanced Power Query and Data Transformation in Power BI

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

In this course, 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 hands-on labs and lecture. After this course you will be able to implement any types of data transformation through Power Query in Excel or Power BI.

Agenda;

2.1: Get Data

Different Data Sources

What is Power Query?

Power Query in Excel and Power BI

Basic Transformations

Get Data from Web

2.2: Query Editor

Base structures in Power Query

Query Editor in Details

Get Data from SQL Server

Get Data from CSV Files

Get Data from Excel

Get Data from JSON

Transformation Pane

2.3: Power Query Syntax

Data Types in Power Query

Query Properties

Transformation Steps

Query Operations; Duplicate, and Reference

2.4: Combine Queries

Merge, Joining queries

Append, creating a big list

Combine Binaries; Looping through files in a folder

2.5: Transformations

Column Operations

Row Operations

Filtering

Sorting

Add as new query / Drill Down

2.6: Table Transformations

Group By

Extending Group By with Expression Editor

Transpose

Pivot, Unpivot

2.7: Text Transformations

Split

Merge (Concatenate)

Parse

Extract

2.8: Numeric Transformations

Standard

Scientific

Statistics

2.9: More Transformations

Date Transformations (Year, Month, Quarter)

Extending Fiscal Date Column

Time Transformations

Adding Time/Date banding

Structured Column

Expand

Aggregate

Add Custom Column

2.10: Power Query Formula Language: M

What is M? and the importance of learning M

M Syntax

Things to Consider for M Scripting

Basic examples of M

Values in M

Logical Operations

2.11: Advanced M Scripting

#Shared Keyword; function library of Power Query

Parameters

Custom Functions

Error Handling in Power Query

Generators in Power Query: Implementing Loop Structure

EACH singleton function

2.12: Use Cases

Date Dimension with Power Query

Day Number of Year Custom Function

Looping through files in a folder with Power Query

Online Date Time Scheduler with Power Query

Module 3: Advanced Power BI Modeling and DAX

This course is module 3 of the Power BI from Rookie to Rock Star course.

In this course, 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;

3.1: Power BI Modeling 101

Relationship in Power BI

Relationship based on multiple Columns

Role Playing Dimension

Formatting

Hide/Unhide

Hierarchy

Basic Calculated Column and Measure

Sort by Column

3.2: Introduction to DAX

Syntax of DAX

Naming in DAX

Logical Operations

Data Types in Power BI Model

Overview of Functions

Variables

3.3: Functions in DAX in Details

Text Functions

Information Functions

Logical Functions

Date and Time Functions

Aggregation Functions

3.4: Filter, Value, and Relationship Functions

ALL

Examples of using ALL function

Filter Function

Related

RelatedTable

3.5: Evaluation Contexts

Row Context

Filter Context

Exception for Row Context

Exception for Filter Context

Calculate Function

3.6: Time Intelligence Functions

Year to Date, Quarter to Date

Fiscal Year to Date

Same Period Last Year

Running Total

Rolling 12 Month Sales

Average 12 Month Sales

Rolling 6 Months

Flexible banding

3.7: Calculated Tables

Role Playing Dimension

Top 10 Customers

More Scenarios of Using Calculated Tables

Performance Consideration

3.8: Best Practices Tips and Tricks

Solving DAX Now and Today Time zone Issue

Modeling Best Practices

Advanced Time Intelligence

Advanced Relationship in DAX

Hierarchies in DAX

Module 4: Advanced Data Visualization with Power BI

This course is module 4 of the Power BI from Rookie to Rock Star course.

In this course, you will learn all things about data visualization in Power BI. Day 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 visualizations 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 course with a Jedi level of visualization.

Agenda;

4.1: Visualization Basics

Basic Charts

Clustered vs Stacked

Quick Calcs in Power BI

Interaction of Visuals

Pages

4.2: Visualization Best Practices

Pie Chart; Be Careful

Coloring

Show me the Numbers

Which Chart is the Best?

Story Telling

Combining Visuals

4.3: Slicing and Dicing

Filters

Slicers

Filtering Slicers

Drill Down/Up

4.4: Custom Formatting Visuals

Labels

Legend

Color

Extended visualization formatting

Group / bin

4.5: Special Visuals

Waterfall Chart; Cashflow

Scatter Chart; Story Telling

Line Chart; Powerful

Table and Matrix with Conditional Formatting

R Visualization

4.6: Map Visualization

Default map visual

Filled Map; Good, bad, and the ugly

Shape Map

Custom Map Visuals

ArcGIS

4.7: Custom Visuals

Using Custom Visuals

SynopticPanel by SQLBI

Chicklet Slicer

3D Map with GlobeMap Visual

Sunburst

Time Slicers

4.8: KPIs in Power BI

KPI Default visual

Dial Gauge

Linear Gauge

Bullet Chart

4.9: Building a Custom Visual

Basics of scripting language

Writing first simple custom visual

Components of a custom visual component

Module 5: Power BI Service, Gateways, and Architecture

This course is module 5 of the Power BI from Rookie to Rock Star course.

In this course, 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 installation 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 architecting 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 course will be delivered in lecture style plus hands on lab experiments.

Agenda;

5.1: Gateways

What is Gateway?

Two types of Gateway

Personal Gateway; an example

On-premises Gateway; not only for Power BI

5.2: On-premises Gateway in Details

Requirements for on-premises gateway

Architecture Flow diagram of the gateway

Installation and Configuration considerations

Install and Configure Gateway example

Data Source Definition in Power BI Service

Schedule Refresh

5.3: Gateway Performance

Performance Considerations

Limitations of Gateway

Gateway for SSAS Live data source

5.4: DirectQuery

Step beyond 1/10GB Limitation in Power BI

Limitations of DirectQuery

Dashboard and Report considerations for DirectQuery

Performance Considerations

5.5: Live Query

Using SSAS Live data source

Difference of DirectQuery and Live Query

Enterprise Scale architecture

UPN Mapping

Architecture of Gateway and SSAS Live Connection

5.6: Power BI Service

Dashboard vs Report

Dashboard Tiles

Data Classification

Power Q&A

Tips and Tricks for Power Q&A

5.7: Sharing

Dashboard Sharing

Work Groups in Power BI

Content Packs

Publish to Web

Comparison of all Sharing methods

Sharing Architecture for enterprise

5.8: Administration

Admin Portal

Set Power BI Administrator

Tenant Settings

Audit Log

5.9: Row Level Security

Row Level Security in Power BI; regardless of Data Source

Row Level Security in SSAS Live Connection

Dynamic Row Level Security

5.10: Integration

Analyze in Excel

Power BI Publisher for Excel

Reporting Services and Power BI

Power BI and Power Point

5.11: Real-time

REST API of Power BI

Real-time Tiles

Azure Stream Analytics

5.12: Architecture

Architecture for Enterprise Scale

Architecture for Reporting

Architecture for Self-Service

Licensing

Power BI Free

Power BI Pro

Power BI Premium

Why Power BI Training?

Power BI is the newest Microsoft BI tool for data mash up, build modelling, and visualize it effectively. It supports mobile devices, it support self-service, and along with its great functionalities, it doesn’t require heavy development or production system.

Common Myths about Power BI

There are some myths about Power BI that sometimes prevents people and organization to move towards using this product. We’ve listed some of myths in following. Please note that these are Myths, and they are not right assumptions.

Myth 1: Power BI is a self-service Excel tool, and isn’t suitable for Enterprise Solutions.

There are many scenarios that Power BI has been used in enterprise solutions. Power BI features for data mash up (with the very powerful data transformation component: Power Query), and data modelling based on xVelocity in-memory engine (Power Pivot), and great data visualization components such as Power View and Power Map, along with mobility support of this product, are rarely available in other products. We have designed, implemented and have seem many BI enterprise solutions based on Power BI within very large organizations.

Myth 2: Power BI is easy to use, and doesn’t require training course.

Power BI is an easy to use tool, but for basic functions. Every BI application requires calculated measure, which needs to be written in DAX. Every BI application requires advanced data transformations which need to be handled with Power Query M scripting language. Designing and implementing solutions with Best Practices is always a requirement for reliable BI applications. We designed our Power BI training in a way that delivers advanced content for all components of Power BI, and fortify your team’s knowledge on the product, so they can solve any complex challenge with this tool.

Myth 3: Power BI is only for Microsoft based environments and platforms.

Power Query is the core of data extraction in Power BI. Power Query supports wide range of data sources. Not only SQL Server, but also Oracle, MySQL, web service result set, DB2 and many other data source are supported. There is also possibility to connect to many systems such as salesforce. The Visualization component of Power BI is also HTML5 supported, and is available for all mobile devices. Power BI app in AppStore, and Google PlayStore as well as Microsoft AppStore enables dashboards and data visualizations to be available in all environments.

Myth 4: Power BI is not a powerful and fully functional BI tool, and cannot be compared with other tools in the market.

In fact the reverse is true. Power BI is such powerful that can be replacement of many other expensive BI tools in the market. We’ve been through scenarios of successful Power BI solutions that have been replacement of Cognos, IBM products, Oracle WB, SAP BO, and so on. Power BI has 5 powerful components that cover main aspects of BI and data analysis system.

In addition there are always possibility to use this as a hybrid approach and save lots of time and money along with great features that are only available in this product.

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 book from Rookie to Rock Star.

In-Person Training;

Our Power BI in person training will be held in high quality hotels with recommendation for hotel bookings for attendees. There will be special group rating fee as well as early bird and past attendees discount. for schedule of our in person training follow this link:

Online Training;

We run online training with GoToWebinar and GoToTraining applications. These applications provide highly reliable communication channel between instructor and attendees. For schedule of our online training follow this link:

What others say about the training and trainer

I spent probably about two weeks looking for a good Microsoft Power BI class that would cover all of the aspects of the program. I wanted to walk away from the class being able to apply what I learned immediately. Most training companies that I looked into were in a state of flux with Power BI mostly due to the transitions that Power BI itself was going through. Then I stumbled on the RADACAD web site. Reza Rad touted a course that he taught named “Power BI, From Rookie to Rock star.” If the class was indeed an A to Z course, then it was exactly what I was looking for. The class outline seemed to include every topic on my checkoff list plus much more. I decided to give Reza a try.
Reza completely lived up to his hype. He took us through a natural progression of the software, using example after example of how to complete a task. We went through real world scenarios, a bare minimum of two scenarios for each minor topic. I was completely impressed with his Power BI knowledge, understanding and abilities. Any question we came to Reza with was answered completely and with even more examples.
After the class, my goal of immediately applying what I learned was attained. Right away I was able to successfully pull and massage data, create useful reports and post them to dashboards.
I would recommend Radacad Power BI Training without hesitation.

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!

You keep hearing about the machine learning and R recently. R become a language for data analysis and machine learning that makes the need for learning it more clearly. Moreover, Microsoft introduces R server as a comprehensive platform for using machine learning and R functionality inside the data analysis tools such as SQL Server 2016 and Power BI.

This training is three days of training using Machine Learning and R with SQL Server, Power BI, and concepts of Machine Learning.

Product Description

Length: 5 days

Prerequisite: This course is a beginner to advanced level course. You don’t need any prerequisites for attending in this course.

Instructor: Dr. Leila Etaati

You keep hearing about the machine learning and R recently. R become a language for data analysis and machine learning that makes the need for learning it more clearly. Moreover, Microsoft introduces R server as a comprehensive platform for using machine learning and R functionality inside the data analysis tools such as SQL Server 2016 and Power BI.

At this training, you will learn:

Day 1: Azure ML, R Basics

In the first day, you will be familiar with the basic concepts of Machine Learning in Azure ML and some basic concepts of R in R Studio and Microsoft Visual studio 2015. In Day 1 you will learn;

sqldf to use SQL Statements for query dataset, RODBC for fetching data from SQL database

The main concepts of basic statistics and how they can be helpful like: mean, median, standard division, and so forth will be discussed.

Practice in R and using some packages

Day 2: R algorithms and Power BI

The main aim of days 2 is to learn some of main machine learning algorithms, and understand how they work and how they can solve different type of real life problems. You will familiar with these algorithms and their syntax in R, and how to use them in Power BI. Moreover, you will learn how to embed some R visualization in Power BI.

In day 2 you will learn;

How classification algorithm like KNN works. and its relevant code in R.

KNN concepts

Write the KNN in R code

How to evaluate the result

How to improve the result by changing related parameters

Decision Tree concepts and its relevant R codes

What is decision tree, the concepts and how it works

Introduce different type of decision tree algorithms that are more common.

Introduce Rpart packages, how to write the related R code in Rstudio and Rvisual studio

See the Rpart package in Power BI visual

Package C50 for decision tree how to evaluate result and how to improve the model

Associative Rules

Audience will learn the main concepts behid the Associative rules

Where to use it and how it brings insight for users

They will learn to write the code in R

Become familiar with concepts such as support, confidence and Lift.

Learn how Apriori packages and algorithm works

How to evaluate the association rules result

How to display it in Power BI

How to improve the results

How to write R codes in Power BI for transformation, and also creating new queries

How to set up the R in Power BI

How to write Simple R code for correlation analysis in R

How to create R visuals in Power BI

Do data transformation in Power BI with R functions

How to create interactive R reports in Power BI

How to call azure ML API into Power BI

Shows how to create functions in Power BI to call the API from Azure ML

Create a prediction model in Power BI and show the result in visualization.

Day 3: R algorithms and SQL Server 2016

In third day, you will learn how to bring analytics and intelligence using SQL server 2016. Moreover, you will also learn some other important machine learning algorithms.

In Day 3 you will learn:

Neural Network algorithm

The concepts behind it and how it works

The ksvm() function

Evaluate the model and how to improve the performance

Time series algorithm

Time series concept difference between seasonality and trend

The function that is used to create the time series model

How to improve and evaluate it

Regression algorithm

Concept behind the regression

Predict numeric data (e.g. using linear regression) the function used to do the linear regression

How to create a model that support the nonlinear models

Multiple regression, logistic regression and so forth

How to evaluate the results

Recommendation (content-based filtering and collaborative filtering).

What is recommendation and what is content filtering and collaborating filtering

How to implement content filtering and collaborative filtering

Using classification and clustering algorithms to create a recommendation system

Azure ML recommendation model

Moreover, you will learn how to use machine learning in SQL Server 2016 and in SSRS.

Set up SQL Server to run the R scripts

The best practice for implementing a prediction process in SQL Server 2016

Create a correlation analysis in SQL Server

Create a decision tree diagram in SSRS via writing code in R

See an end to end predictive analysis code from getting data, create models, evaluate models and publish the result.

Practice: creating a predictive solution in SQL Server 2016

This course is full of hands on labs, and you will experiment all examples through real-world demos. At the end of the 3-day training course, you will be able to use techniques and concepts of this training in your Analytics challenges.

Instructor: Dr. Leila Etaati

Dr. Leila Etaati gained her PhD in University of Auckland. She is world well-known speaker in Machine Learning and Analytics topics, and spoke in world’s best international conferences in Data Platform topics, such as; PASS Summits, PASS Rally, SQL Nexus, Microsoft Ignite, and so on. She has more than 10 years experience in Data Mining and Analytics. She is also Microsoft Most Valuable Professional (MVP) because of her dedication on Microsoft Analytics and Machine Learning technologies. She writes blog posts in RADACAD and also publishes YouTube videos in our channel. She also is an invited lecturer in universities such as University of Auckland, and Unitec, and some other universities. She worked in many industries including banking financial, power and utility, manufacturing, tourism, and so on.

What others say about the training and trainer

Kenny McMillan, Sports Physiologist / Data Analyst, Frankfurt, Germany:
I attended RADACADs “Advanced Analytics” course recently in Frankfurt in May 2017. Being a regular user of Power BI (with a science background ) the course was extremely helpful in showing me how to incorporate R data visualisations into Power BI dashboards and for introducing me to machine learning using the Microsoft ML Studio. Leila is an excellent and extremely knowledgeable instructor and explained complex data analytical concepts and methodologies in an easy-to-understand manner. I thoroughly recommend this course to anyone who wants to expand their data analytical skills and knowledge.

Event Details

Course Title: Power BI from Rookie to Rock Star

Length:

Instructor: Reza Rad

In this training course you will learn Power BI from beginner to advance. You will learn how to use Power BI for simple data analysis situations as well as complex business intelligence scenarios. You will learn about Power BI Components such as Power Query (Get Data and Transform), Modeling and DAX, Visualization, Power BI Desktop as the main tool, Power BI Service, Gateway configuration, and architecture. You will learn all the concepts with live demos. Expect learning best practices with great scenarios in this course.

Power BI from Rookie to Rock Star

This is the most comprehensive course for Power BI which split in daily modules. You can enroll in any of these modules separately or take the whole course. Modules designed independently, which means each module can be taken regardless of the order of modules. Here are list and detailed agenda of each module:

Module 1: Power BI Essentials

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

In this training course, you will learn Power BI from beginner to advance. You will learn how to use Power BI for simple data analysis situations as well as complex business intelligence scenarios. You will learn about Power BI Desktop, Power BI Website, and components of Power BI which are; Get and Transform (or Power Query), Modeling (or Power Pivot), and Visualization. You will also learn about Power Query Formula Language (Called M informally), and DAX. This course designed to give you an end to end view of Power BI, so you be able to use Power BI straight away in your everyday challenges for data analysis.

1.1: Introduction to Power BI

Power BI is the newest Microsoft Business Intelligence and Data Analysis tool. In this module, we will go through basics of this product, and introduce all five components of Power BI (Power Query, Power Pivot, Power View, Power Map, and Power Q&A). You will see some demos and introduction about Power BI desktop, Office 365 Power BI subscription, and Power BI website, and mobile apps. You will see some basic demos of how easy to use is Power BI in some scenarios.

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, Power Map, and Power Q&A.

1.2: Getting Data

Getting Data is the first experience of working with Power BI. You can connect to many data sources on-premises or on cloud. For some data sources, you can have a live or direct connection, for some connection works offline. For some connections, you need a gateway or connector to be installed. In this module, you will learn everything about get data experience of Power BI.

What is Power Query: Introduction to Data Mash-Up Engine of Power BI

Get Started with Power Query: Movies Data Mash-Up

Power BI Get Data from Excel: Everything You Need to Know

Definitive Guide to Power BI Personal Gateway and Enterprise Gateway

File Sources

Folder as a Source

Database Sources

Analysis Services Connection

Get Data from Azure SQL Database

Azure SQL Data Warehouse Source

Software as A Source

Web Source

1.3: Power Query for Data Transformation

Data analysis and BI world starts from data extraction and transformation. Power Query is the data transformation engine of Power BI. Power Query comes as part of Excel 2016, or as an add-in for Excel 2013 and 2010. Power Query is also part of Power BI Desktop. In this module you will learn about all different version of Power Query, their similarities, and differences, as well as configuration and requirements of using them.

Power Query uses a graphical user interface to apply transformations on the dataset. However Power Query works with a functional scripting language behind the scenes. The key to learn Power Query is learning the functional language called M. There are many features in M that are not available in the Power Query GUI. In this module you will learn how to understand M, and how to write M scripts even from scratch. You will learn writing custom functions in M as well as many other useful features.

The content that you will learn in this module includes but not limited to;

Different versions of Power Query

Power Query Introduction

Query Editor

Transformation GUI

Row Transformations

Column Transformations

Data Type

Adding Column

Text Transformations

Number Column Calculations

Date and Time Calculations

Data types, Lists, Records, and tables in M

M built-in functions

Generators in M

Writing Custom Functions

Error handling

Advanced Data Transformations with M

1.4: Data Modelling and DAX

Power Pivot is xVelocity in-memory data modelling engine of the Power BI. Modelling effectively is the key of high performance BI solution. In this module, you will learn basics of Power Pivot such as creating relationships, and calculated members, as well as advanced best practices and DAX expressions. DAX is Data Analytical eXpression language. DAX has similar structure to excel functions, but it is different. In this module, you will learn DAX from beginner to advance. You will learn basic functions as well as complex functions and scenarios of using them in real world challenges.

The content that you will learn in this module includes but not limited to;

Power Pivot xVelocity engine basics and concepts

Relationships in Power BI

Best practices

Introduction to DAX

Calculated Columns

Measures

Calculated Tables

Row Context vs Set Context

Advanced calculations using Calculate functions

Time Intelligence Functions

DAX Advanced

1.5: Data Visualization

Data Visualization is the front end of any BI application; this is the user view point of your system. It is critical to visualize measures, and dimensions effectively so the BI system could tell the story of the data clearly. In this module, you will learn conceptual best practices of data visualizations which is valid through all data visualization tools. You will learn Power View and Power Map skills. Power View is the interactive data visualization tool and Power Map is 3D geo-spatial data visualization tool. You will learn how to create effective charts, and dashboards using these tools as well as best practices for working with these tools. There are two version of Power View; Power View for Excel, and Power View for SharePoint which will be covered in this module as well.

The content that you will learn in this module includes but not limited to;

Power BI Desktop Visualization

Custom Visuals in Power BI Desktop

Formatting Visuals in Power BI Desktop

Waterfall Chart

Built-in Charts and Graphs in Power BI

Sorting, Filtering, and categorization

KPIs

Maps and Geo-Spatial Visualization

1.6: Power BI Service, Dashboards, Q&A, and Gateways

Power BI introduced a new engine for users which focus on users who wants to play with the data more, and understand the story behind the data better. Power Q&A is question and answering engine which works based on human natural language. Power Q&A automatically will be enabled on Power BI files deployed into Power BI website, or Office 365 subscription. However, there are some tips and tricks about how to design your model to get the best type of answers through Power Q&A. In this module, you will learn best practices for modelling that effect on the response of Power Q&A.

The content that you will learn in this module includes but not limited to;

Power Q&A introduction

Design best practices for Power Q&A

Power BI Service

Dashboard vs Report

Gateways

1.7: Administration & Security

Power BI components can be deployed into different environments such as Power BI website, Office 365 subscription, or even SharePoint on-premises. In this module, we will go through deployment options, Configurations and requirements required for each environment. You will learn how to deploy your Power BI files into Power BI website. you will also learn how to build dashboards in the website, and how to work with Mobile Apps for Apple, Android and Windows Phone.

The content that you will learn in this module includes but not limited to;

Groups and Work Spaces

Sharing

Row Level Security

Schedule Refresh

Module 2: Advanced Power Query and Data Transformation in Power BI

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

In this course, 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 hands-on labs and lecture. After this course you will be able to implement any types of data transformation through Power Query in Excel or Power BI.

Agenda;

2.1: Get Data

Different Data Sources

What is Power Query?

Power Query in Excel and Power BI

Basic Transformations

Get Data from Web

2.2: Query Editor

Base structures in Power Query

Query Editor in Details

Get Data from SQL Server

Get Data from CSV Files

Get Data from Excel

Get Data from JSON

Transformation Pane

2.3: Power Query Syntax

Data Types in Power Query

Query Properties

Transformation Steps

Query Operations; Duplicate, and Reference

2.4: Combine Queries

Merge, Joining queries

Append, creating a big list

Combine Binaries; Looping through files in a folder

2.5: Transformations

Column Operations

Row Operations

Filtering

Sorting

Add as new query / Drill Down

2.6: Table Transformations

Group By

Extending Group By with Expression Editor

Transpose

Pivot, Unpivot

2.7: Text Transformations

Split

Merge (Concatenate)

Parse

Extract

2.8: Numeric Transformations

Standard

Scientific

Statistics

2.9: More Transformations

Date Transformations (Year, Month, Quarter)

Extending Fiscal Date Column

Time Transformations

Adding Time/Date banding

Structured Column

Expand

Aggregate

Add Custom Column

2.10: Power Query Formula Language: M

What is M? and the importance of learning M

M Syntax

Things to Consider for M Scripting

Basic examples of M

Values in M

Logical Operations

2.11: Advanced M Scripting

#Shared Keyword; function library of Power Query

Parameters

Custom Functions

Error Handling in Power Query

Generators in Power Query: Implementing Loop Structure

EACH singleton function

2.12: Use Cases

Date Dimension with Power Query

Day Number of Year Custom Function

Looping through files in a folder with Power Query

Online Date Time Scheduler with Power Query

Module 3: Advanced Power BI Modeling and DAX

This course is module 3 of the Power BI from Rookie to Rock Star course.

In this course, 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;

3.1: Power BI Modeling 101

Relationship in Power BI

Relationship based on multiple Columns

Role Playing Dimension

Formatting

Hide/Unhide

Hierarchy

Basic Calculated Column and Measure

Sort by Column

3.2: Introduction to DAX

Syntax of DAX

Naming in DAX

Logical Operations

Data Types in Power BI Model

Overview of Functions

Variables

3.3: Functions in DAX in Details

Text Functions

Information Functions

Logical Functions

Date and Time Functions

Aggregation Functions

3.4: Filter, Value, and Relationship Functions

ALL

Examples of using ALL function

Filter Function

Related

RelatedTable

3.5: Evaluation Contexts

Row Context

Filter Context

Exception for Row Context

Exception for Filter Context

Calculate Function

3.6: Time Intelligence Functions

Year to Date, Quarter to Date

Fiscal Year to Date

Same Period Last Year

Running Total

Rolling 12 Month Sales

Average 12 Month Sales

Rolling 6 Months

Flexible banding

3.7: Calculated Tables

Role Playing Dimension

Top 10 Customers

More Scenarios of Using Calculated Tables

Performance Consideration

3.8: Best Practices Tips and Tricks

Solving DAX Now and Today Time zone Issue

Modeling Best Practices

Advanced Time Intelligence

Advanced Relationship in DAX

Hierarchies in DAX

Module 4: Advanced Data Visualization with Power BI

This course is module 4 of the Power BI from Rookie to Rock Star course.

In this course, you will learn all things about data visualization in Power BI. Day 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 visualizations 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 course with a Jedi level of visualization.

Agenda;

4.1: Visualization Basics

Basic Charts

Clustered vs Stacked

Quick Calcs in Power BI

Interaction of Visuals

Pages

4.2: Visualization Best Practices

Pie Chart; Be Careful

Coloring

Show me the Numbers

Which Chart is the Best?

Story Telling

Combining Visuals

4.3: Slicing and Dicing

Filters

Slicers

Filtering Slicers

Drill Down/Up

4.4: Custom Formatting Visuals

Labels

Legend

Color

Extended visualization formatting

Group / bin

4.5: Special Visuals

Waterfall Chart; Cashflow

Scatter Chart; Story Telling

Line Chart; Powerful

Table and Matrix with Conditional Formatting

R Visualization

4.6: Map Visualization

Default map visual

Filled Map; Good, bad, and the ugly

Shape Map

Custom Map Visuals

ArcGIS

4.7: Custom Visuals

Using Custom Visuals

SynopticPanel by SQLBI

Chicklet Slicer

3D Map with GlobeMap Visual

Sunburst

Time Slicers

4.8: KPIs in Power BI

KPI Default visual

Dial Gauge

Linear Gauge

Bullet Chart

4.9: Building a Custom Visual

Basics of scripting language

Writing first simple custom visual

Components of a custom visual component

Module 5: Power BI Service, Gateways, and Architecture

This course is module 5 of the Power BI from Rookie to Rock Star course.

In this course, 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 installation 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 architecting 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 course will be delivered in lecture style plus hands on lab experiments.

Agenda;

5.1: Gateways

What is Gateway?

Two types of Gateway

Personal Gateway; an example

On-premises Gateway; not only for Power BI

5.2: On-premises Gateway in Details

Requirements for on-premises gateway

Architecture Flow diagram of the gateway

Installation and Configuration considerations

Install and Configure Gateway example

Data Source Definition in Power BI Service

Schedule Refresh

5.3: Gateway Performance

Performance Considerations

Limitations of Gateway

Gateway for SSAS Live data source

5.4: DirectQuery

Step beyond 1/10GB Limitation in Power BI

Limitations of DirectQuery

Dashboard and Report considerations for DirectQuery

Performance Considerations

5.5: Live Query

Using SSAS Live data source

Difference of DirectQuery and Live Query

Enterprise Scale architecture

UPN Mapping

Architecture of Gateway and SSAS Live Connection

5.6: Power BI Service

Dashboard vs Report

Dashboard Tiles

Data Classification

Power Q&A

Tips and Tricks for Power Q&A

5.7: Sharing

Dashboard Sharing

Work Groups in Power BI

Content Packs

Publish to Web

Comparison of all Sharing methods

Sharing Architecture for enterprise

5.8: Administration

Admin Portal

Set Power BI Administrator

Tenant Settings

Audit Log

5.9: Row Level Security

Row Level Security in Power BI; regardless of Data Source

Row Level Security in SSAS Live Connection

Dynamic Row Level Security

5.10: Integration

Analyze in Excel

Power BI Publisher for Excel

Reporting Services and Power BI

Power BI and Power Point

5.11: Real-time

REST API of Power BI

Real-time Tiles

Azure Stream Analytics

5.12: Architecture

Architecture for Enterprise Scale

Architecture for Reporting

Architecture for Self-Service

Licensing

Power BI Free

Power BI Pro

Power BI Premium

Why Power BI Training?

Power BI is the newest Microsoft BI tool for data mash up, build modelling, and visualize it effectively. It supports mobile devices, it support self-service, and along with its great functionalities, it doesn’t require heavy development or production system.

Common Myths about Power BI

There are some myths about Power BI that sometimes prevents people and organization to move towards using this product. We’ve listed some of myths in following. Please note that these are Myths, and they are not right assumptions.

Myth 1: Power BI is a self-service Excel tool, and isn’t suitable for Enterprise Solutions.

There are many scenarios that Power BI has been used in enterprise solutions. Power BI features for data mash up (with the very powerful data transformation component: Power Query), and data modelling based on xVelocity in-memory engine (Power Pivot), and great data visualization components such as Power View and Power Map, along with mobility support of this product, are rarely available in other products. We have designed, implemented and have seem many BI enterprise solutions based on Power BI within very large organizations.

Myth 2: Power BI is easy to use, and doesn’t require training course.

Power BI is an easy to use tool, but for basic functions. Every BI application requires calculated measure, which needs to be written in DAX. Every BI application requires advanced data transformations which need to be handled with Power Query M scripting language. Designing and implementing solutions with Best Practices is always a requirement for reliable BI applications. We designed our Power BI training in a way that delivers advanced content for all components of Power BI, and fortify your team’s knowledge on the product, so they can solve any complex challenge with this tool.

Myth 3: Power BI is only for Microsoft based environments and platforms.

Power Query is the core of data extraction in Power BI. Power Query supports wide range of data sources. Not only SQL Server, but also Oracle, MySQL, web service result set, DB2 and many other data source are supported. There is also possibility to connect to many systems such as salesforce. The Visualization component of Power BI is also HTML5 supported, and is available for all mobile devices. Power BI app in AppStore, and Google PlayStore as well as Microsoft AppStore enables dashboards and data visualizations to be available in all environments.

Myth 4: Power BI is not a powerful and fully functional BI tool, and cannot be compared with other tools in the market.

In fact the reverse is true. Power BI is such powerful that can be replacement of many other expensive BI tools in the market. We’ve been through scenarios of successful Power BI solutions that have been replacement of Cognos, IBM products, Oracle WB, SAP BO, and so on. Power BI has 5 powerful components that cover main aspects of BI and data analysis system.

In addition there are always possibility to use this as a hybrid approach and save lots of time and money along with great features that are only available in this product.

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 book from Rookie to Rock Star.

In-Person Training;

Our Power BI in person training will be held in high quality hotels with recommendation for hotel bookings for attendees. There will be special group rating fee as well as early bird and past attendees discount. for schedule of our in person training follow this link:

Online Training;

We run online training with GoToWebinar and GoToTraining applications. These applications provide highly reliable communication channel between instructor and attendees. For schedule of our online training follow this link:

What others say about the training and trainer

I spent probably about two weeks looking for a good Microsoft Power BI class that would cover all of the aspects of the program. I wanted to walk away from the class being able to apply what I learned immediately. Most training companies that I looked into were in a state of flux with Power BI mostly due to the transitions that Power BI itself was going through. Then I stumbled on the RADACAD web site. Reza Rad touted a course that he taught named “Power BI, From Rookie to Rock star.” If the class was indeed an A to Z course, then it was exactly what I was looking for. The class outline seemed to include every topic on my checkoff list plus much more. I decided to give Reza a try.
Reza completely lived up to his hype. He took us through a natural progression of the software, using example after example of how to complete a task. We went through real world scenarios, a bare minimum of two scenarios for each minor topic. I was completely impressed with his Power BI knowledge, understanding and abilities. Any question we came to Reza with was answered completely and with even more examples.
After the class, my goal of immediately applying what I learned was attained. Right away I was able to successfully pull and massage data, create useful reports and post them to dashboards.
I would recommend Radacad Power BI Training without hesitation.

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!

You keep hearing about the machine learning and R recently. R become a language for data analysis and machine learning that makes the need for learning it more clearly. Moreover, Microsoft introduces R server as a comprehensive platform for using machine learning and R functionality inside the data analysis tools such as SQL Server 2016 and Power BI.

This training is three days of training using Machine Learning and R with SQL Server, Power BI, and concepts of Machine Learning.

Product Description

Length: 5 days

Prerequisite: This course is a beginner to advanced level course. You don’t need any prerequisites for attending in this course.

Instructor: Dr. Leila Etaati

You keep hearing about the machine learning and R recently. R become a language for data analysis and machine learning that makes the need for learning it more clearly. Moreover, Microsoft introduces R server as a comprehensive platform for using machine learning and R functionality inside the data analysis tools such as SQL Server 2016 and Power BI.

At this training, you will learn:

Day 1: Azure ML, R Basics

In the first day, you will be familiar with the basic concepts of Machine Learning in Azure ML and some basic concepts of R in R Studio and Microsoft Visual studio 2015. In Day 1 you will learn;

sqldf to use SQL Statements for query dataset, RODBC for fetching data from SQL database

The main concepts of basic statistics and how they can be helpful like: mean, median, standard division, and so forth will be discussed.

Practice in R and using some packages

Day 2: R algorithms and Power BI

The main aim of days 2 is to learn some of main machine learning algorithms, and understand how they work and how they can solve different type of real life problems. You will familiar with these algorithms and their syntax in R, and how to use them in Power BI. Moreover, you will learn how to embed some R visualization in Power BI.

In day 2 you will learn;

How classification algorithm like KNN works. and its relevant code in R.

KNN concepts

Write the KNN in R code

How to evaluate the result

How to improve the result by changing related parameters

Decision Tree concepts and its relevant R codes

What is decision tree, the concepts and how it works

Introduce different type of decision tree algorithms that are more common.

Introduce Rpart packages, how to write the related R code in Rstudio and Rvisual studio

See the Rpart package in Power BI visual

Package C50 for decision tree how to evaluate result and how to improve the model

Associative Rules

Audience will learn the main concepts behid the Associative rules

Where to use it and how it brings insight for users

They will learn to write the code in R

Become familiar with concepts such as support, confidence and Lift.

Learn how Apriori packages and algorithm works

How to evaluate the association rules result

How to display it in Power BI

How to improve the results

How to write R codes in Power BI for transformation, and also creating new queries

How to set up the R in Power BI

How to write Simple R code for correlation analysis in R

How to create R visuals in Power BI

Do data transformation in Power BI with R functions

How to create interactive R reports in Power BI

How to call azure ML API into Power BI

Shows how to create functions in Power BI to call the API from Azure ML

Create a prediction model in Power BI and show the result in visualization.

Day 3: R algorithms and SQL Server 2016

In third day, you will learn how to bring analytics and intelligence using SQL server 2016. Moreover, you will also learn some other important machine learning algorithms.

In Day 3 you will learn:

Neural Network algorithm

The concepts behind it and how it works

The ksvm() function

Evaluate the model and how to improve the performance

Time series algorithm

Time series concept difference between seasonality and trend

The function that is used to create the time series model

How to improve and evaluate it

Regression algorithm

Concept behind the regression

Predict numeric data (e.g. using linear regression) the function used to do the linear regression

How to create a model that support the nonlinear models

Multiple regression, logistic regression and so forth

How to evaluate the results

Recommendation (content-based filtering and collaborative filtering).

What is recommendation and what is content filtering and collaborating filtering

How to implement content filtering and collaborative filtering

Using classification and clustering algorithms to create a recommendation system

Azure ML recommendation model

Moreover, you will learn how to use machine learning in SQL Server 2016 and in SSRS.

Set up SQL Server to run the R scripts

The best practice for implementing a prediction process in SQL Server 2016

Create a correlation analysis in SQL Server

Create a decision tree diagram in SSRS via writing code in R

See an end to end predictive analysis code from getting data, create models, evaluate models and publish the result.

Practice: creating a predictive solution in SQL Server 2016

This course is full of hands on labs, and you will experiment all examples through real-world demos. At the end of the 3-day training course, you will be able to use techniques and concepts of this training in your Analytics challenges.

Instructor: Dr. Leila Etaati

Dr. Leila Etaati gained her PhD in University of Auckland. She is world well-known speaker in Machine Learning and Analytics topics, and spoke in world’s best international conferences in Data Platform topics, such as; PASS Summits, PASS Rally, SQL Nexus, Microsoft Ignite, and so on. She has more than 10 years experience in Data Mining and Analytics. She is also Microsoft Most Valuable Professional (MVP) because of her dedication on Microsoft Analytics and Machine Learning technologies. She writes blog posts in RADACAD and also publishes YouTube videos in our channel. She also is an invited lecturer in universities such as University of Auckland, and Unitec, and some other universities. She worked in many industries including banking financial, power and utility, manufacturing, tourism, and so on.

What others say about the training and trainer

Kenny McMillan, Sports Physiologist / Data Analyst, Frankfurt, Germany:
I attended RADACADs “Advanced Analytics” course recently in Frankfurt in May 2017. Being a regular user of Power BI (with a science background ) the course was extremely helpful in showing me how to incorporate R data visualisations into Power BI dashboards and for introducing me to machine learning using the Microsoft ML Studio. Leila is an excellent and extremely knowledgeable instructor and explained complex data analytical concepts and methodologies in an easy-to-understand manner. I thoroughly recommend this course to anyone who wants to expand their data analytical skills and knowledge.