This training is designed for data modeler, who have one or more data sources to get data from, prepare the data with transformations, build the models, relationships, and add some business calculations to it to get the full analytical power of Power BI. In this training, you will learn best practices of Power BI modelling, Power Query, and DAX.
You will learn how you can use Power Query to prepare data model in the proper way. You will learn about basics of relationships, cardinality of relationships, how to create shared dimension and star schema. You will learn about DAX patterns that can help with the calculations needed.
At the end of this training, you will be able to use Power Query for data preparation, design the proper data model in Power BI, understand all relationship requirements and implement the right relationship, write complex DAX expressions for your analytics need, and put them all together to build the best model for your data analysis solution using Power BI.
The training includes but not limited to topics below:
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 to build dimensions and fact tables
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
3: 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
4: Power BI Modelling
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
• Hide/Unhide Columns
• Hierarchy Definition in Power BI
• Sort by Column
5: 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
6: DAX Functions used in real-world scenarios
The last part of the training focused on using advanced DAX expressions in real-world scenarios, functions included but not limited to:
• Aggregation Functions and Iterators
• Filter Functions
• Relationship Functions
• Time Intelligence Functions
• Parent-child hierarchy functions
• Dynamic DAX