Microsoft Power BI *(Advanced)

1. Introduction to the course syllabus
This document provides the detailed outline of the 2-day Microsoft Power BI Advanced course. Upon registering for the course, you will be provided you with:

• Course syllabus
• Presentation material
• Exercise material

Power BI is a radically new tool in the hands of business enterprises who are overwhelmed with the amount of data at hand and until now did not have a cost-effective tool to extract insights from it.

Upon the completion of this Power BI course online, as a trained professional, you can start using this BI tool, which is a set of tools and applications that let you extract data from multiple unrelated sources and convert it into a neatly managed and coherent set of information, visually appealing insights, interactive data, charts, graphs, and reports, etc.

Today, all business enterprises and departments need extensive business insights, and this Business Intelligence tool puts the power in your hands. You can easily work with multiple sources of data, convert the data into a report, graph, or data visualization as needed. By deploying Power BI, you get real-time insights and can deploy them faster than any other BI tool available in the market. It is open-source and highly intuitive and does not need technical knowledge or IT support.

2. Course preparation
The Intermediate level Power BI course is a prerequisite for this Advanced level course.

3. Administrative and logistical activities
There is no specific administrative nor logistical activities for this course.

4. Target group
The target group of the IT Security Planning training is drawn from:

• Analysts
• Data analytics professionals
• Accountants
• Report builders

5. Course objectives
This training course is intended to help participants understand and become familiar with the different concepts with regard to….

The following are the course objectives:

• Use advanced functionalities in Power Query Editor and M language to prepare and transform data
• Understand the different relationship types in data modelling
• Build a DAX Reference Tool: understand and use DAX most common functions to create reports with advanced analytics
• Publish in Power BI Service with Row-Level Security roles
• Develop a learning strategy to pursue M and DAX learning

For virtual training, it is recommended to have access to 2 monitors: using a laptop and a second monitor is adequate. With this setting, participants may display the instructor desktop on one monitor and complete activities on the other monitor.

6. Delivery method
6.1 Educational alternatives
This course was designed so that it makes use of the following educational alternatives:

• Deduction: Using anecdotes and examples, the training presents use cases before the concepts have even been covered. The students deduct the learning from their own analysis. The topic and its concepts are presented afterwards to demonstrate how theory applies to what was deducted.
• Questioning: Asking questions forces participants to stop and think about different variables. It also enables the participants to put the newly acquired knowledge to the test. Quizzes have been incorporated as part of the training to make sure that the concepts are well understood by the participants.
• Demonstrating: Some use cases have been incorporated to the training AFTER the concepts. These serve to explain a cause and effect relationship using the newly acquired knowledge.

7. Session plans
The following provides individual session objectives for the different modules covered in the training.

Course Outline:
1. Power Query and M language

• Explore Power Query Editor environment: Menus, Applied Steps, Advanced Editor
• Explore M language syntax
• Explore the different ways to get data: import, direct query, live connection
• Discuss query folding and incremental refresh
• Edit a query: remove columns, extract text after delimiter, add a conditional column, add a computed column, filter, unpivot columns, group by, drill down
• Manage an error message
• Edit an Applied Step using M language
• Create Values, Lists and tables from blank queries
• Use M functions: List.Dates, #date, Duration.Days, #duration, DateTime.LocalNow(), etc.
• Create a dynamic custom date table
• Search the web to resolve issues
• Explore recommended web resources

2. Understand relationship types in data modelling

• Explore data model and table relationships concept
• Visualize the impact of wrong relationships
• Create relationships: edit type and filter direction
• Use and understand the impact of Many-to-Many relationships
• Favor a star schema data model

3. Build a DAX reference tool

• Understand DAX basic concepts, the syntax and the evaluation context
• Differentiate measures and calculated columns
• Create explicit measures with DAX functions: TODAY, YEAR, MONTH, FORMAT, COMBINEVALUES, COUNTA,
• COUNTBLANK, COUNTROWS, DISTINCTCOUNT, SUM, SUMX, AVERAGEX (iterator function), MAX, FILTER, CALCULATE, ALL, ALLEXCEPT, DIVIDE, USERELATIONSHIP, IF, AND, OR, SELECTEDVALUE, TOTALYTD, SAMEPERIODLASTYEAR
• Use the CALCULATE function to override the relationships and manipulate the filter context
• Deal with an inactive relationship
• Format measures
• Use Variables in DAX formulas
• Explore recommended web resources

4. Create reports using advanced functionalities

• Import custom visuals
• Explore the AI tools: create visuals with Quick Insights and Q&A
• Address complex analytics with grouping, binning, clustering, time series analysis
• Enhance the end-user experience with hierarchies, drill through, drill down, Tooltip pages
• Program Row-Level Security roles to enable partial data availability to specific groups or individuals

5. Power BI Service workflow

• Workspace, dataset, report, dashboard, app
• chedule refresh
• Set permissions and access
• Manage Row-Level Security roles

For more information, please see the Microsoft-Power-BI-Advanced-course syllabus-EN-V1.0.pdf