Thank you for sending your enquiry! One of our team members will contact you shortly.
Thank you for sending your booking! One of our team members will contact you shortly.
Course Outline
Software overview
- Creating a data model: Power BI desktop - required
- Microsoft SQL Server Management Studio - optional
- DAX Studio - optional for working with DAX
- Visual Studio Code - optional for working with Power Query M
Working with Power BI - key work steps
- Preparing data for Power BI Desktop in Power Query.
- Optimising and parameterising data, including 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 its capabilities.
- Applications of M (Power BI, Power Pivot, Dataverse).
- The fundamentals and concept of the M language
- Limitations of the M language and risks associated with case sensitivity.
- 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 function libraries: text, numbers, time.
- Data types and their conversion, user regional settings.
- Merging and splitting 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 the M language.
- Building objects: list, data set, table using code.
- Creating custom functions in the M language.
Dynamic queries - parameters in M language.
- Create and manage parameters
- Parameterisation 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 execution order of SQL statements.
- Operators and criteria in queries
Optimising 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 for ordered results, PARTITION BY for partitioning, and ORDER BY for sorting query results. 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.
Power Query and SQL advanced parameterisation
- Modify a query using the M parameter
- Parameter in a remote SQL query
- Controlling parameter values from within Power BI
- Fragmenter in Power BI Desktop visualisation and parameter in Power Query.
DAX language in the Power BI data model
- What is the DAX language. Fundamentals and concept of the DAX language.
- Applications of DAX (Power BI, Power Pivot, Analysis Services).
- Overview of the environment and tools useful for working with DAX.
Introduction to the DAX language
- Data model - what it is and how it works
- Good data organisation practices.
- Data types, type conversion, and handling of possible errors.
- Data type vs. data format. Data format management and customisation.
- Creating relationships between tables
- Data model relationships: active and inactive
- Parameter tables
- Filtering directions
- Hiding columns in user view
- Operators in the DAX language
Calculation columns and built-in functions in DAX
- What are calculation columns
- Creation and modification of calculation columns
- Operators and their priorities in the DAX language
- Basic functions of the DAX language: ROUND, IF, SWITCH
- Time functions: YEAR, MONTH, DAY, WEEKDAY, WEEKNUM, EOMONTH
- Logical functions: NOT, OR, AND and operators || and &&
- Text functions: LEFT, RIGHT, MID, LOWER, UPPER
- Numeric functions: ROUND, ROUNDUP, ROUNDDOWN
- Conversion functions: FORMAT
Requirements
This is a general-purpose training course aimed at individuals involved in processing and analysing large volumes of data, including analysts, accountants, as well as software developers and testers. Training topics cover working with SQL Server, processing in Power Query M, and building data models in DAX.
21 Hours
Testimonials (1)
engagement with the attendees
