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: Prepare a series of Power BI visualizations based on raw data.
- Configuring the Power BI environment.
- Creating a Power BI report from a single spreadsheet.
- Understanding visualization components: Charts, cards, tables, matrices, KPIs, and maps.
- Visualization rules: General guidelines for data linking and object formatting.
- Report filtering: Techniques for filtering by selection, using the filter pane, and using slicers.
- Data modeling: Building a report based on a model of relationally linked tables from a single spreadsheet. Importing, transforming, and cleaning data in Power Query. Creating relationships (automatic and manual) and understanding their principles and types.
- Data integration: Working with distributed data sources in Power BI. Common data sources for Power BI. Introduction to the Power Query M language and its capabilities.
- Power BI data sources: CSV, Excel, JSON, XML, PDF, online tables, OData streaming, folder imports, SharePoint libraries, and SQL databases.
- Query management: Joining and merging tables, tracking query dependencies, handling duplicates, PIVOT transformations, grouping, counting, and aggregation.
- Dynamic queries: Using variables (parameters) for query optimization. Creating, managing, and handling parameters from 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 model in Power BI: Definition and use cases.
- DAX data model components: Columns, calculated columns, tables, filtered tables, calendar tables, measures, and relationships. Data types and formats in the model.
- Best practices for data organization.
- Computed columns: Definition, creation, and modification; Operators and precedence in DAX; Hiding columns from user view.
- DAX functions: Text, Number, Time, Logical, Conditional, Convert, Array, and Filter functions.
- Model relationships: Active and inactive relationships and their application. Filtering directions. Joining tables without explicit relationships.
- Computed tables: Filtering tables and context within queries. Usage of FILTER, ALL, and ALLEXCEPT functions.
- DAX Measures: Definition and purpose. Distinction between measures and computed columns in query execution. Understanding the CALCULATE function. Aggregate, count, and statistical functions in measures.
- DAX Context: Understanding row-level, query-level, and filter-level contexts.
- Analysis hierarchies: Automatically generated and manually defined hierarchies.
- Time Intelligence in practice: Calendar tables in DAX and time-related DAX functions.
3. Reports Based on SQL Server Data
Objective: Introduction to SQL database server interaction, significantly saving time on generating, processing, and importing file data.
- SQL Server operating modes: Import vs. Direct Query. Capabilities and limitations.
- Importing SQL Server objects for model building: Tables, Views, data-returning Procedures (overview), and Table functions.
- SQL Query work: Classic data retrieval using SELECT, including syntax and execution order. Rapid low-code query development using the Query Designer. Adhering to SQL standards within the Power BI data model.
- SQL optimization: Retrieving only necessary data. SQL functions in queries. Operations on joined tables (SQL joins). Combining query results and aggregating data on the SQL server side.
- Parameterization with SQL: Modifying queries with M language parameters. Managing parameter values from Power BI. 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 existing Power BI Desktop reports. Creating new reports from published datasets. Exporting reports to PDF, Excel, and PowerPoint, and embedding them. Sharing reports with colleagues or in public mode. Refreshing data by republishing.
- Organization and User Areas: Workspace and Dashboard structures and their key elements.
- Workspace Access Management. Dashboard creation and management: Components, capabilities, and limitations.
- Datasets and Data Repositories: Utilizing existing datasets 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. Application scenarios. Creating and managing data sources, including synchronization scheduling and access control.
- Row Level Security (RLS). Configuring permission-controlled tables and connecting them to the model. Defining access rules and adding users.
- Data Gateway: Installation and configuration. Adding new sources. Managing connections and update schedules. Security and access control.
- Report Subscriptions: Creating subscriptions, managing recipients, and scheduling notifications.
- Power BI Integration: Creating and publishing apps in Power BI Online. Downloading ready-made apps. Publishing apps to SharePoint, websites, and Teams. Dedicated mobile report views and permission management.
5. Summary: End-to-End Project + R and Python Scripts.
Objective: Build an analytics system with publishing and sharing capabilities.
- Summary exercises
Objective: (Optional) Visualization and data processing using Python.
- Python applications: Running 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 scripting.
- Creating visualizations: Scatter plots for correlation analysis. Line charts with multiple data series. Bar charts for data presentation.
- Limitations and data security considerations in Python.
Objective: (Optional) Visualization and data processing using R.
- Requirements and limitations of R packages. Installing R language and function libraries.
- R language applications: Preparing data models, creating reports, cleaning data, advanced data shaping, dataset analysis, 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 analysts, accountants, software developers, and testers.
PRE-COURSE PREPARATION:
Objective: Familiarize yourself with software tools relevant to the training.
- Power BI Desktop (Required for creating data models)
- Microsoft SQL Server Management Studio (Optional)
- DAX Studio (Optional, for DAX operations)
- Visual Studio Code (Optional, for Power Query M, Python, and R)
- Microsoft R Open environment and Python (Optional)
Objective: Understand the Power BI workflow stages for report creation and lifecycle management.
- Preparing data in Power Query for Power BI Desktop.
- Optimizing and parameterizing data, including the use of SQL.
- Working with the DAX data model: establishing relationships, managing tables, creating 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 (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
