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
1. Building Power BI Desktop Reports
Objective: Create a series of Power BI visualizations based on raw data.
- Configuring the Power BI environment.
- Building a Power BI report from a single spreadsheet.
- Visualization components: Charts, cards, tables, matrices, indicators, and maps.
- General rules for linking visualizations to data and formatting objects.
- Filtering reports: Selection-based filtering, using the filter pane, and slicers.
- Data Modeling: Creating reports based on a model of relational tables from a single spreadsheet. Importing, transforming, and cleaning data in Power Query. Establishing automatic and manual relationships between tables, including principles and types of relationships.
- Data Integration: Working with distributed data sources and Power Query. Exploring popular data sources for Power BI. Understanding the Power Query M language and its capabilities.
- Data Sources for Power BI: CSV files, Excel, JSON, XML, PDF, web tables, OData streaming data, folder imports, SharePoint libraries, and SQL databases.
- Query Management: Joining and merging tables, tracking query dependencies, handling duplicates, performing PIVOT-type transformations, and grouping/aggregating data.
- Dynamic Queries: Using variables (parameters) for optimization. Creating, managing, and handling parameters at the Power BI report level.
2. Power BI Reports with DAX Elements
Objective: Build Power BI reports with an introduction to the DAX language.
- Understanding the DAX language and its role in the Power BI model.
- DAX Data Model Components: Columns, calculated columns, tables, filtered tables, calendar tables, measures, and relationships. Understanding data types and formats.
- Best practices for data organization.
- Computed Columns: Definition, creation, modification, operator priorities, and hiding columns from the user view.
- DAX Functions: Text, Number, Time, Logical, Conditional, Convert, Array, and Filter functions.
- Relationships in the Data Model: Active vs. inactive relations, filtering directions, and joining tables without direct relationships.
- Computed Tables: Filtering tables and query contexts. Using FILTER, ALL, and ALLEXCEPT functions.
- Measures in DAX: Purpose, creation, comparison with computed columns regarding query execution, and understanding the CALCULATE function. Using aggregate, count, and statistical functions in measures.
- DAX Context: Row level, query level, and filter context.
- Hierarchies in Analysis: Automatically generated and manually defined hierarchies.
- Time Intelligence in Practice: Using calendar tables and Time Intelligence functions for time-based operations.
3. Reports Based on Data Stored on SQL Server
Objective: Introduction to SQL database server operations, saving time on data generation, processing, and import.
- SQL Server Operation Modes: DirectQuery vs. Import. Understanding capabilities and limitations.
- Importing SQL Server Objects for Modeling: Tables, Views, data-returning Procedures (overview), and Table functions.
- Working with SQL Queries: Classic data retrieval using SELECT—syntax and execution order. Low-code query development via the Query Designer. SQL standards in the Power BI data model.
- Optimizing SQL Usage: Retrieving only necessary data. Using SQL functions in queries. Performing SQL joins on joined tables and combining results. Aggregating data on the SQL server side.
- Parameterization and SQL: Modifying queries with M language parameters. Parameterizing SQL. Controlling parameter values from Power BI and integrating Power BI slicers with M language parameters.
- Creating and submitting dynamic SQL to the server.
4. Power BI Online
Objective: Create and share visualizations online.
- Online Reports: Publishing reports from Power BI Desktop. Creating new reports from published datasets. Exporting reports to PDF, Excel, and PowerPoint; embedding in PowerPoint. Sharing reports with colleagues or in public mode. Refreshing data by re-publishing.
- Workspace and User Areas: Key elements of Workspaces and Dashboards.
- Access Management: Managing workspace access. Creating and managing dashboards, including their capabilities and limitations.
- Data Sets and Repositories: Using existing data sets in new reports. Downloading data sources as PBIX files. Viewing query and object dependencies in Power BI Online.
- Dataverse and Power Query Online: Using Dataverse as part of the Power Platform for master data management. How and where to use it. Creating, managing, scheduling synchronization, and controlling access for data sources.
- RLS (Row Level Security): Using permission-controlled tables, connecting them to the model, setting access rules, and adding users.
- Data Gateway: Installation and configuration. Adding new sources to the gateway. Managing connections and data update schedules. Security and access control.
- Report Subscriptions: Creating subscriptions, managing recipients, and scheduling notifications.
- Power BI Integration: Creating and publishing applications in Power BI Online. Downloading applications. Publishing to SharePoint, websites, and Teams. Mobile-specific report views and permission management.
5. Summary: End-to-End Project + R and Python Language Scripts.
Objective: Build an analytics system with publishing and sharing capabilities.
- Summary exercises
Objective: (Optional) Visualization and data processing using Python.
- Python Applications in Power BI: Running scripts directly in Power BI Desktop to import data into the model. Creating and sharing reports in the Power BI service.
- Prerequisites: Python environment in Power Query. Software libraries: Pandas and NumPy.
- Working with Python: Enabling scripting support. Importing and refreshing data via scripts.
- Creating Visualizations: Generating scatter plots to examine correlations. Line charts with multiple data series. Bar charts for data presentation.
- Understanding limitations and data security in Python.
Objective: (Optional) R language visualization and data processing.
- Requirements and limitations of R language packages. Installing R language and function libraries.
- R Applications: Preparing data models, creating reports, data cleaning, advanced data shaping, and dataset analysis (including missing data imputation, forecasting, and clustering).
- Running R Scripts: Preparing and executing scripts to import and refresh data models.
- Working with R: Using R in the Power Query editor. Utilizing ready-made visualizations in Power BI. Creating visualizations based on R script data.
Requirements
This training is designed for professionals involved in processing, analyzing, and presenting large datasets, including data analysts, accountants, software developers, and testers.
PREWORK AND PREPARATION FOR TRAINING:
Objective: Familiarize yourself with software tools useful for the training.
- Data Modeling: Power BI Desktop - Mandatory
- Microsoft SQL Server Management Studio - Optional
- DAX Studio - Optional, for working with DAX
- Visual Studio Code - Optional, for working with Power Query M, Python, and R
- Microsoft R Open environment and Python
Objective: Understand the stages of report creation and lifecycle in Power BI.
- Preparing data for Power BI Desktop using Power Query.
- Optimizing and parameterizing data, including the use of SQL.
- Working with the DAX data model: relationships, tables, calculated columns, calculated tables, and measures.
- Building reports within the Power BI Desktop application.
- Publishing and sharing reports via the Power BI service.
- Managing access control for the data model.
- Reusing published data models.
- Updating reports online.
35 Hours
Testimonials (1)
engagement with the attendees
