Power BI Desktop

Goals

To create reports in Power BI desktop

To learn how to manipulate data in Power Query

To learn how to manipulate a data model and write formulas

Prerequisite

Knowledge of the Windows work environment

Contents

Introduction

• What is Power BI?

• Licences,

• Power BI Desktop environment.

Importing data with Power Query

• Loading data from different types of sources,

• Transform data to fit the model:

  • Filter,

  • Pivot / un-pivot,

  • Merge queries,

  • Append data from one query to another (Append Queries),

  • Select columns to keep from the source,

  • Rename columns,

  • Duplicate / reference queries,

  • Disable query loading in the model.

• Update data

Data Model

• Data modelling and best practice implementation,

• Creating relationships in the data model,

• Cardinality of relationships (one-to-many, one-to-one, many-to-many),

• Direction of filters.

Creating calculations in the data model - Introduction to DAX (Data Analysis eXpressions)

• Create calculated columns in DAX,

• Create conditional formulas using IF,

• Use data from a related table using RELATED,

• Create measures (aggregate calculations - sums, counts, etc.) in DAX (SUM, COUNTROWS, etc.),

• Create measures using Quick Measures,

• Calculate a percentage of the total (DIVIDE, CALCULATE, ALL),

• Create time comparison measures (TOTALYTD, TOTALQTD, TOTALMTD, DATEADD),

• Customise the date tables of the model (CALENDAR, CALENDARAUTO, FORMAT, YEAR, MONTH).

Visualisations

• Graphics: Columns, Bars, Ribbon, Line, Area, Waterfall,

• Proportions: Pie, Donut, Treemap, Funnel,

• Performance indicators: KPI, Gauge, Card, Multi-Row Card,

• Geographic data: Map, Filled Map, Shape Map,

• Interactive: Key Influencers, Decomposition tree, Q&A, Smart Narrative, Slicers.

Formatting

• Formatting of report pages,

• Use of themes to harmonise reports,

• Basic formatting of visualisations,

• Conditional formatting based on colour gradients or data bars,

• Conditional formatting based on rules,

• Conditional formatting based on measures.

Filtering reports

• Filter a visualisation,

• Create a top/bottom X,

• Filter all views of a page,

• Filter all visualizations of a report,

• Drill down on the data of a visualization,

• Editing interactions between visualisations.

Publication and integration

• Publish a report,

• View a published report in Teams,

• Power BI app for report visualization (mobile and Windows),

• Integration of published reports in PowerPoint.