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
- Data Model Creation: 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 - Major Work Steps
- Preparation of data for Power BI Desktop in Power Query.
- Optimization and parameterization of data, including the use of 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 basics 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 libraries of functions: text, numbers, time.
- Data types and their conversion, user regional settings.
- Merge and split columns and rows.
Working with Tables in the 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.
Advanced M Query 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 the M Language.
- Create and manage parameters
- Parameterization of queries
- Parameter handling from the Power BI report level
Power Query and SQL Server
- SQL Server modes of operation: 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 queries,
- 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 standards 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.
- Combining results using UNION, UNION ALL, INTERSECT, EXCEPT/MINUS queries
- Aggregation of data on the SQL server side
- Window functions in SQL: OVER ordered results, PARTITION BY partitioning, and ORDER BY sorting of query results. Row references: previous, next, first, and last in a group.
- Subqueries in SQL: Query results as a WHERE condition in a query. A query based on another query.
- CTE table expressions.
Advanced Parameterization with Power Query and SQL
- Modify a query using an M parameter
- Parameter in a remote SQL query
- Controlling parameter values from within Power BI
- Slicer in Power BI Desktop visualization and parameter in Power Query.
DAX Language in the Power BI Data Model
- What is the DAX language. Basics 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
- Best practices for data organization.
- Data types, type conversion, handling of possible errors.
- Data type vs. data format. Data format management and customization.
- Creating relationships between tables
- Data model relationships: active and inactive
- Parameter tables
- Filtering directions
- Hiding columns in the 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 analyzing large amounts of data, including 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 using DAX.
21 Hours
Testimonials (2)
The subject was taught in an engaging way, making me want to learn more.
kgotla Moncho - Martin Engineering Africa
Course - PL-300T00: Microsoft Power BI Data Analyst
The coding and analysis, how the information come together in a report which will be useful for my work
