Course Code: powerbipmvbspk
Duration: 21 hours
Prerequisites:

1. Understand basic Excel operations
2. Understand Excel table (no need experience in writing Excel formulas)
3. Have some experience in handling data in workplace with the business domain knowledge (e.g. sales data)

Software Requirements:

1. Power BI Desktop (2020 version or later)                                                         

2. Excel 2016 or later                                                                                            

Software Language: English (i.e. menu options showing English characters)

3. Power BI Service Accounts:  This is a licenced professional account for Power BI cloud services.  Power BI services would constitute less than 1 hour of training in this course.  This is for sharing and collaboration of Power BI dashboards on the cloud.  The students can choose to only learn the concepts about these services without practicing with it. In that case, the account is not required.  If all of the students require some practice (about 15 – 30 minutes), each of the students is required to equip with an account which is charged by Microsoft on monthly basis (maybe there is another corporate licence deal).

Overview:

Data visualization brings data to life, making you the master storyteller of the insights hidden within your numbers. Live dashboards, interactive reports, charts, graphs, and other visual representations help you unlock key business insight quickly and effectively.

This practical course enables you to automate data cleansing procedures that transform untidy data sets into useful ones. You will learn how to define relationship between data tables in a data model that enables you to effectively create dashboards and impressive charts for presentation to management.

The course primarily covers the following three parts:

  • Data Preparation with Query Editor (12 hours)
  • Building and Optimizing a Data Model (5 hours)
  • Dashboard and Chart Design for Management (6 hours 45 min)
  • Q&A on Power BI and Data Design (15 min)


Remarks:

  • Course Duration: 24 Hours (Practice: 85%), 3 days x 8 hours per day (9:30am – 5:30pm including breaks). 
  • The Practice is in the form of workshop.
  • The Course Materials are available to download from the Internet before the first day of class

Language of Instruction: Cantonese (supplemented with English)

Course Outline:

Part 1: Query Editor

1. Data Transformation:

  • Recode a Column
  • Replace Column Value
  • Add a Conditional Column
  • Change Column Data Type
  • Simple Arithmetic Column Calculations
  • Extracting Part of a Date Column
  • Changing Case
  • Merge Columns
  • Split a Column by Delimiters
  • Extract Substring of a Column
  • Extract Date Components
  • Add an Index Column
  • Keep and Remove Rows
  • Move and Remove a Column
  • Create a Column from Examples
  • Filter Rows (with a Parameter)

 

2. Data Aggregation:

  • Create a Group-By Summary Query
  • Create a new Dimension with an Existing Column
  • Merge Queries (Table Join)
  • Expand a Structured Column
  • Unpivot Queries
  • Pivot Queries

 

3. Data Consolidation:

  • Append Queries
  • Folder File Consolidation
  • Workbook / Worksheet Consolidation

Part 2: Data Modeling

In Power BI, you will create a Data Model, which is a collection of tables with relationships.

1. Build Table Relationship:

  • Manual and Auto-detect Relationships
  • Cardinality
  • Cross-Filter Direction

 

2. Optimize a Data Model:

  • Change the Sort Order of a Column
  • Change the Format of a Column
  • Categorize a Data Column
  • Create a Calculated Column
  • Create a Hierarchy
  • Create a Calculated Table
  • Create a New Measure with Quick Measure
  • Introduce DAX
  • Manage Roles
  • Hide a Column in Report View
  • Define Synonyms for Q&A

Part 3: BI Visualization

You will make use of the transformed data and created data model to build BI charts and a dashboard.

1. Building Charts:

  • Basic Bar Chart
  • Stacked Bar Chart
  • Line Chart
  • Combo Chart
  • Area Chart
  • Pie Chart
  • Doughnut Chart
  • Gauge
  • Table and Matrix Reports
  • Drill-up/Drill-down/Drill-Through
  • Simple Card and Multi-row Card
  • KPI Visual
  • Scatter Chart (with Motion)
  • Waterfall Chart
  • Funnel Chart
  • Bookmark
  • Tree Map
  • Decomposition Tree
  • Map and Filled Map
  • Smart Narrative
  • Natural Language Queries (Q&A)

2. Dashboard Design Techniques:

  • Slicers
  • Interaction
  • Conditional Formatting
  • Top-N Analysis
  • Role Level Security
  • Publish to Power BI Service