Posted on by — Leave a comment

Power BI Modeling Master Class of DAX and Power Query

From the authors of Power BI from Rookie to Rock Star books and training courses

Length: 3 days

Delivery method: in-person

RADACAD is the premier Power BI training provider for many years, and courses thought by our team (Reza Rad and Leila Etaati) are among the best seller Power BI courses globally. Over the years, we have developed a training content of Power BI from Rookie to Rock Star, which is now well over ten days of training, and taught this course to many Power BI enthusiasts all around the world. RADACAD website is the hub of Power BI learning for more than 100K learners each week.

Recently we have developed a special edition of our Rookie to Rock Star course, which is fitted in a five days course but includes the most popular modules of our courses and includes the tips, tricks and experiences that you need mostly when you are building a Power BI solution. This course is not just the basics of Power BI, neither it is all experts’ advice. The course has a great harmony of all you need to know to become a master of building Power BI solutions. We called this course: Power BI Master Class.

Who this training is for?

Anyone who is building Power BI reports, dashboards and solutions to solve reporting and analytical challenges. If you are business analysts in the finance or HR team, or a developer in the BI team, or a data analysts who have been tasked to do Power BI report, or someone who wants to change their career path towards the realm of Power BI, this training is for you. All the learnings from this course will help you to start building solutions straight away after the course.

The Delivery method:

The course is full of hands-on examples. You are expected to bring your laptop with Power BI Desktop installed on it. We will go through each example together, talk about what is the challenge we are trying to solve, what are the ways to solve it, what is the best method, and how to solve it using that method. This is not a lecture-only course. All the learnings are fully practical and through live examples.

Prerequisite:

There is no prerequisite for this course. We will start the course with a quick introduction. So even, If you do not have any experience with Power BI, this course will give you enough to understand the basics. However, this course is not just focusing on the basics. The main challenge this course is trying to solve is to use Power BI in real-life situations, which will require an understanding of Power Query, DAX, and also empower it with AI and Machine Learning aspects of Power BI.

What will you receive after completing the course?

All attendees will have access to all the materials of the course, all the datasets, Power BI sample files, handouts, etc. You will get a certificate of completion. You will have the chance to meet Reza and Leila through five days and ask whatever questions you have, even if the topic is outside of the course subject. And most importantly; you will leave the course knowing how to face analytical and reporting challenges and solve them using Power BI in a practical way.

Instructors

Instructor: Reza Rad

Our trainer is the world’s well-known name in the Microsoft BI field. Reza Rad is Microsoft Regional Director, a speaker in the world’s best and biggest Microsoft Data Platform, BI and Power BI conferences such as Microsoft Ignite, Microsoft Business Applications Summit, Microsoft Data Insight Summit, PASS Summits, PASS Rallys, SQLBits, TechEds, and so on.  He is the author of books on this topic, and he has more than 20 years’ experience in Microsoft BI technologies. Reza is the founder of RADACAD and a consultant for more than decades. He is also a Microsoft Certified Trainer for years. He is Microsoft Data Platform MVP (Most Valuable Professional) focused on BI and Data Analysis; Microsoft has awarded him MVP because of his dedication and expertise in Microsoft BI technologies from 2011 till now (more than nine years). He is the author of the Power BI book from Rookie to Rock Star.

Instructor: Dr. Leila Etaati

Our trainer is the world’s well-known name in the Microsoft Data Science and Power BI fields. Leila Etaati is Microsoft AI, and Data Platform (Most Valuable Professional) focused on AI and BI Microsoft technologies; Microsoft has awarded her MVP because of her dedication and expertise in Microsoft BI technologies from 2016 till now. She is a speaker in the world’s best and biggest Microsoft Data Platform, BI and Power BI, AI conferences such as Microsoft Ignite, Microsoft Business Applications Summit, Microsoft Data Insight Summit, PASS Summits, PASS Rallys, SQLBits, TechEds, and so on.  She is the author of some books on this topic, and she has more than 15 years’ experience in Microsoft BI technologies and Data science. Leila is the co-founder of RADACAD and a consultant for more than seven years.

Agenda

In this training, you will learn how to best build a data model in Power BI, we’ll start with basic concepts of relationships and star schema, and jump into advanced techniques of using Power Query and DAX for better data modelling and Analytics. At the end of this course, you would be able to build the best data model based on best practices using the learnings of this course. The content of this course is a combined content of our Power Query course and DAX and data modelling course. This is not a course about building visualization. However, you will learn how to build the best model that you can then simply visualize for any requirement.

Here are details of modules:

1: Get Data

In this section, you will learn about Power Query basics which starts with Getting data. You will learn that Power Query is the data transformation tool in Power BI. You will learn different parts of the Query Editor through an example of using Power Query to transform a dataset.

  • Introduction to Power Query
  • Query Editor
  • Get Data from Web
  • Basic Transformations
  • Get Data from Excel
  • Use First Row As Headers / Use Headers as First Row
  • Get Data from SQL Server

2: Combine Queries

One of the most common data transformations is combining datasets. Depends on the types of datasets and the way that they are related to each other, you may want to merge or append them. In this section, you will learn why you need to combine data at first, and then you will learn about scenarios that you combine data in Power Query.

  • Dimensional Modelling; Designing the data model
  • Append, creating a single big query of the same structure
  • Merge; Joining queries when the structure is different
  • Join types in Merge
  • Tips to consider after Merge or Append
  • Enable Load; Performance Boost

3: Reducing Number of Rows; Filtering

Filtering rows in Power Query is an important transformation especially when the dataset is big, or when the data needs to be cleaned. There are different ways of doing filtering in Power Query. You will learn about ways to remove some rows from the top or bottom of the table, and ways that you can filter a data table based on criteria. You will learn about basic filtering and the difference of that with the advanced filtering, and potential challenges that you may have through this process.

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

4: Column Operations

A data table in Power Query can get big if you don’t care about columns.  In this section, you will learn actions that you can do on columns, and what are best practices to make sure you have the best performance in your Power BI model considering columns in your tables. You will also learn about some generic column operations and transformations.

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

5: Table Transformations

Some of the most important table transformations will be explained in this section. You will learn about a way to change the granularity of a table; Grouping. You will also learn scenarios that grouping data can be more than a simple transformation. You will learn about transformations such as Transpose, Pivot and Unpivot, and the difference of all these items with scenarios of using it on real-world datasets.

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

6: Text, Numeric, Date and Time Transformations

When you work with text values, there are many transformations you can apply. Transformations such as a split column, removing part of a text, or adding a prefix or postfix to it, concatenating some columns together, etc.

  • Split Column by Delimiter
  • Split Column by number of Characters
  • Split into rows instead of columns
  • Merge (Concatenate)
  • Standard transformations; Divide, Integer-divide, Multiply, Add etc.
  • Scientific transformations; logarithm, power square, etc.
  • Statistics transformations;
  • Rounding
  • Information functions; Is Even, Is Odd, and Sign.
  • Date Transformations (Year, Month, Quarter, Week, etc.)
  • Extending Fiscal Date Column
  • Time Transformations (Hour, Minute, Second, etc.)
  • Adding Time/Date banding
  • Duration Transformation and Data Type
  • Age Calculation
  • Age in Years considering Leap Year
  • Local Date or Time
  • Time zone consideration for Power BI

7: Add Column Transformations

There are two types of transformations in Power Query; Transforming an existing column, or adding a column based on a transformation. In this section, you will learn about these two types, their differences, and few other transformations that we have available in the add column tab of the Power Query Editor through some examples.

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

8: Functions and Parameters; Dynamic Power Query

Power Query is a powerful tool for data transformation. This power can be amplified even more if you can make your queries dynamic. Instead of repeating several steps for similar data sources, you can create a function from those steps, and run that function for all other sources. Functions get parameters as the input. Functions and parameters can make everything in Power Query dynamic. If you want to learn Power Query advanced deep dive, this is the section to go through.

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

9: Power Query Formula Language: M

The heart of Power Query is a scripting language named Power Query Formula Language or M. If you want to be a good data wrangler or data developer with Power Query, you must learn M scripting. The good news is that M scripting is not a hard language to learn. This section goes through the basics of the language, data types, literals, and everything is needed for understanding an M script’s structure.

  • What is M? and the importance of learning M
  • M Syntax
  • End of the line
  • Variable Names
  • Special Characters
  • Escape Character
  • Step by Step Coding
  • Literals
  • Function Call
  • Comments
  • A real-world example

10: Working with Data Structures in M

As you are dealing with data in Power Query, it is important to learn how to work with table, list, and record from the code. In this section, you will learn about these three structures in code, and how to navigate between different parts of each structure.

  • Primitive Value
  • List
  • Record
  • Table
  • Function
  • Navigating through List and List functions
  • Navigating through Record and Record functions
  • Navigating through Table and table functions
  • Concatenating lists and records

11: Advanced M Scripting

Now that you know more about M scripting, it is time to see how powerful this part of Power Query can be compared to the graphical interface of query editor. In this section, you will learn features that you have access to apply using M scripting. You will learn ways to get a list of all functions, doing error handling in an advanced way. Applying some changes in functions and parameters which is only possible through the code. You will also learn an end-to-end example using everything you learn about M at the end.

  • #Shared Keyword; function library of Power Query
  • Parameters in the code
  • Custom Functions through scripting
  • Error Handling in Power Query
  • Generators in Power Query: Implementing Loop Structure
  • EACH: singleton function
  • Sample Custom Function: Day Number of Year Custom Function

12: Error Handling

In any data related solution, you should expect bad data rows to appear. If you haven’t thought about the appearance of bad data rows and you just did the transformations considering everything will be nice and tidy, then you may face many errors in Power Query. This section is all about how to handle errors, deal with bad data rows, create troubleshooting reports, etc.

  • Keep/Remove Errors; Troubleshooting report
  • Count Rows
  • Reference/Duplicate
  • Replace Errors
  • Data Type considerations

13: Use Cases

At the end of the training, we go through some end-to-end solutions using Power Query. These solutions leverage everything you learned through the training about this tool and language; you will see how all those parts come to help together to build the solution. We will go through building a date dimension which has all calendar columns, fiscal columns, and public holidays fetched live, and we will talk about combining files from a folder.

  • Date Dimension with Power Query; building the base table
  • Adding Fiscal columns to the Date dimension
  • Getting public holidays live and merging to the date dimension
  • Looping through files in a folder with Power Query

14: Power BI Modelling 101

Power BI Modelling engine is based on the same engine used in Excel Power Pivot, and SQL Server Analysis Services Tabular. Power BI uses the in-memory engine, named xVelocity. The in-memory engine of Power BI makes the analysis super-fast. Everything will respond very fast in this model. In this section, you will learn about the basics of the modeling engine and some of the differences of that with SSAS and Power Pivot.

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

15: Introduction to DAX

DAX is an abbreviated name for Data Analysis eXpression language. This is the expression language in Power BI for analytics. DAX is a dynamic expression language which will consider the interaction of the user at the time of visualization. Using DAX, you can do calculations such as year to date, year over year comparison, etc. Most of the data modeling training is about DAX. In this section, you will learn the basics of DAX.

  • Syntax of DAX
  • Naming in DAX
  • Operators and Operands
  • Logical Operations
  • Data Types in Power BI Model
  • Overview of Functions in DAX
  • Variables
  • DAX or M? When to use Which?

16: Calculations in Power BI

There are three types of calculations in Power BI. Calculated Column, Measure, and Calculated Table. You can write DAX expression in all these three types of objects. This section will teach you what the main difference between the calculated column, measure, and the calculated table is, and what are scenarios of using them.

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

17: Aggregation and Iterator Functions

The first set of important functions in DAX are aggregation functions. There is a set of normal aggregation functions such as SUM, MIN, MAX, and there is another set called iterators. The way of working with iterators is different. Iterators get an input table and an expression. Example of iterator function is SUMX. In this section, you will learn the difference between SUM and SUMX and scenarios of using those two.

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

18: Filter Functions

Filter functions are probably the most important functions in DAX. You can refer to a column in DAX (like Excel), but you cannot refer to rows. If you want to refer to rows in an expression, you must filter it, and that is why Filter functions are important. There are several filter functions, and the behavior of all of them are unique. In this section, we will talk about some of these functions through real-world examples.

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

19: Evaluation Contexts and Conditional Sum

Understanding Evaluation contexts are one of the most critical learnings in DAX. The evaluation context refers to the way that filters impact the calculation’s result in DAX. There are two types of contexts; Row context, and filter context. In this section, you will learn about the difference of all these, and you will learn scenarios that you need to be careful when the context changes.

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

20: Relationship Functions

Some of the functions in DAX are going through directions of relationship and apply some filtering based on that, like RELATED() for example. Some other functions change the behavior of relationship such as UseRelationship() function. In this section, you will learn about relationship functions in DAX and scenarios of using them.

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

21: Time Intelligence Functions

Calculations based on time and date are critical for many businesses such as finance. You can use DAX to do calculations such as year to date, fiscal year to date, year over year comparison, and rolling 12 months average. In this section, you will learn some basic time intelligence functions such as TotalYTD to a calculated year to date. You will also learn about scenarios when you do not have the built-in function for your use case and will learn how to write the combination of function usages in DAX to achieve the solution.

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

22: Dynamic DAX: Parameters

DAX calculation is dynamic based on the user interaction in a Power BI report page. However, you can take a step further, and make the expression of DAX even more dynamic. The user can change a value which is defined statically in your DAX expression using a parameter. Parameters will make your DAX expressions even more dynamic. In this section, you will learn about parameters, and their usages, and the scenario of using a parameter table to select from multiple measures dynamically.

  • Numeric Parameter Definition through GUI
  • GenerateSeries DAX function
  • SelectedValue DAX function
  • Sample Scenario: Customer Retention with Dax and Power BI
  • Other Types of Parameters? Parameter Table
  • SWITCH
  • Sample Scenario: Selection of Measures in a table dynamically

23: Parent-Child Functions

DAX can navigate through a hierarchy with an unknown number of levels. Example of such a hierarchy is a chart of accounts or organizational hierarchy. In this section, you will learn about parent-child functions which can be used for organization hierarchy. You will learn different scenarios of using these functions in real-world examples.

  • Organizational Hierarchy or Chart of Accounts: unknown levels
  • Path function
  • PathLength: getting the number of levels
  • PathItem: finding a specific level
  • PathItemReverse
  • PathContains: Security Pattern
  • LookupValue: To get the other related fields

24: Scenario Based DAX

Learn how to use all the DAX learning in a scenario based analysis. We will talk about real-world scenarios such as stock on hand for inventory, customer retention on a date period bases and etc using DAX expressions:

  • Stock on Hand using DAX: Power BI Inventory Model
  • Using IsFiltered function
  • Conditional Formatting using DAX
  • RANKX to find best customers

 25: Architecture

The last part of the training focuses on architecture blueprints for Power BI. In addition to architecture best practices for sharing, self-service, enterprise-level architecture, you will learn about a tool that can help in Power BI solution designed by RADACAD; Power BI Helper.

  • Dataflow for Power Query
  • Shared Dataset for Power BI data model
  • Architecture for multi-developer team
  • A tool that helps: Power BI Helper
Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.