Power BI desktop DAX + M

Prerequisites:

This is a general-purpose training course aimed at people involved in processing and analyzing large amounts of data, analysts, accountants, as well as software developers and testers. Training topics include working with SQL Server, processing in Power Query M and building a data model in DAX.

Overview:

About the training
Microsoft Power BI desktop training focusing on the data layer aspect, extended to include topics on working with Power Query M and the DAX language and SQL database. Training aimed at people familiar with the basics of building Power BI reports, involved in processing and analyzing large amounts of data, for whom the standard import of data into the model is not enough. Extension with elements of M and DAX languages working with the database allows for more efficient and faster work with data.

Training objective
To get acquainted with tools that extend the possibilities in working with Microsoft Power BI desktop working with SQL database server, data services or file data. To learn the basics of working with M and DAX language and using them in creating reports and analysis.

What will Power BI improve in your work?
You will learn how to retrieve data efficiently. You will optimize ways to retrieve and transform data located on SQL Server, file sources and services. You will expand your skills in using the best analytical tool. You will increase your confidence in your job by acquiring new skills. You will dramatically increase your chances for promotion. You will radically raise your level of professional competence.

Where will you use the acquired knowledge?
You will increase the efficiency of your work related to the creation of reports and in BI tools related to SQL database. You will also notice an increase in comfort in accessing data. You will achieve a new higher quality of data presentation and updating.

What you will learn.
You will learn how to effectively use the enhanced capabilities of Power BI. You will be able to create a unified reporting system based on SQL Server data. Standardization of methods for generating reports and creating statements for analysis will no longer be a problem for you. At the same time, you will move the security of the created analyses to a new higher level.

Course Outline:

Software overview

Creating a data model: Power BI desktop - required
Microsoft SQL Server Management Studio - optional
DAX Studio - optional to work with DAX
Visual Studio Code - optional to work with Power Query M

Working with Power BI - major work steps

Preparation of data for Power BI Desktop in Power Query.
Optimization and parameterization of data, also using SQL.
Working with the DAX data model: relationships, tables, calculated columns, calculated tables and measures.
Building a report in the Power BI Desktop application.
Publishing and sharing a report in the Power BI service.
Managing access control to the data model.
Reusing a published data model.
Updating a report online

Power Query M language

What the Power Query M language is, where it is used and what capabilities it has.
Applications of M (Power BI, Power Pivot, Dataverse).
The basics and concept of the M language
Limitations of the M language and risks associated with Case Sensitive.
Data updating.

Data sources for the Power BI Desktop model

CSV, Excel, JSON, XML, PDF files.
Internet data sources: Tables published on the World Wide Web.
Streaming data services - Odata.
Mass import of files from a folder.
SharePoint 365 libraries as a file repository - overview.
Relational SQL databases (Microsoft SQL Server by default).

Power Query in combining and transforming data

Filtering and sorting of input data,
Data quality control: searching for anomalies, errors and inconsistencies.
Calculation and conditional columns.
Creation of custom computed columns.
Direct editing of M code: formula bar and advanced editor.
Duplicating a query versus referencing a query.
Transferring queries between applications.
Multiple references to objects.
Working with libraries of functions: text, numbers, time.
Data types and their conversion, user regional settings.
Merge and split columns and rows.

Working with tables in Power Query M language.

Joining and merging tables. Tracking query dependencies.
Table functions and managing transformation steps.
Removing duplicate values - tables and column sets.
PIVOT transformations and their inverse: UNPIVOT.
Aggregation and counting of data.

M Query advanced operations

Advanced functionalities of M language.
Building objects: list, data set, table using code.
Creating custom functions in M language.

Dynamic queries - parameters in M language.

Create, manage parameters
Parameterization of queries
Parameter handling from the Power BI report level

Power Query and SQL Server

SQL Server modes of operation: Data Import vs. Direct Query. Capabilities and limitations.
Importing SQL objects that can be used to build a model
Tables - the primary source of data for the model
Views - stored database query,
Procedures that return data (overview)
Table functions
Queries in SQL code

Selection queries - DQL (Data Query Language).

Query designer in applications and creating a database query.
Basic data types in SQL and their use. SQL standard in the Power BI data model.
Data retrieval - SELECT: command syntax and order of execution of SQL statements.
Operators and criteria in queries

Optimizing SQL queries in Power Query

SQL language functions in queries
Operations on joined tables: SQL joins (SQL JOIN).
Combining the results of a UNION, UNION ALL, INTERSECT, EXCEPT/MINUS query
Aggregation of data on the SQL server side
Window functions in SQL: OVER ordered results, PARTITION BY partitioning and ORDER BY sorting of query result. Row references: previous, next, first and last in a group.
Subqueries in SQL: Query result as a WHERE condition in a query. A query based on another query.
CTE table expressions.