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 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.
Power Query and SQL advanced parameterization
- 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 visualization and parameter in Power Query.
DAX language in the Power BI data model
- What is the DAX language. Basics and concept of DAX language.
- Applications of DAX (Power BI, Power Pivot, Analisys Services).
- Overview of the environment and tools useful for working with DAX.
Introduction to the DAX language
- Data model - what it is how it works
- Good data organization practices.
- 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 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 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.
21 Hours
Testimonials (9)
dużo praktyki w trakcie szkolenia
Katarzyna Grześkowiak - mTab Sp. z o.o.
Course - Power BI - wizualizacja danych
dyskusja na temat legalnosci umieszczania danych w chmurze i dobre praktyki przy modelowaniu i raportowaniu.
Course - Microsoft Power BI Data Analyst (szkolenie autoryzowane PL-300)
Constructive exchanges
Cécile - Transdev Compagnie Axonaise
Course - Power BI
Machine Translated
The Trainer is professional. He can make me feel this program not too hard to use as I thought before training.
Benjaporn - Thai Asahi Kasei Spandex Co.,Ltd
Course - Power BI for Business Analysts
Dpt ilmu baru
Yani - PT. Becton Dickinson Indonesia
Course - Power BI for Developers
The pace, activities and screen mirroring. Power Bi is new to me and I am looking forward to working with it more.
Michelle - Childhood Development Initiative CLG
Course - Creating Dashboards Using Microsoft Power BI
the personalized part during which I was able to work on my project and directly apply the elements of the training
Caroline - Schreder
Course - Power BI Dashboards
Machine Translated
Great pace during the training. very knowledgeable and teaching style
James - Gilead Sciences Ireland
Course - Excel to Power BI
What was very interesting in this training was the direct handling of the power bi tool. What was presented was very informative and provided a real answer to my problems, while trying to learn on the job. The trainer gave us keys to be better organized and structured in our files. Which is interesting if we have to give up our work. It would have taken a few more hours to produce our own report, that's the only downside.
Linedie - Transdev Nord
Course - Advanced Power BI
Machine Translated
