Excel Power BI Tools

Course level – Intermediate to Advanced

Prerequisites

The course material includes advanced features of Excel and participants will gain the maximum benefit from this course if they are already competent spreadsheet users.

It is recommended that they already have attended the ‘Data Analysis with Excel – Advanced’ course, or have the equivalent level of knowledge. Prior knowledge of the Desktop Application is not necessary.

At a minimum, it is assumed that participants will know how to do some of the following:

• Navigate confidently in Excel

• An understanding of the benefits in nesting functions

• Work with inherited spreadsheets from others

• Enjoy using Excel for analysis and similar business processes, using tools such as PivotTables

Course Outline and Objective

This course covers the newly introduced range of Excel Power BI tools (Power Business Intelligence). Learn how to use all of these latest features introduced in Excel which will help in business processing, data analysis, financial reporting, and with day-to-day decision making.

Become confident with what Microsoft are now including as standard, and with what is now starting to become a benchmark in advanced Excel skills.

This one day course is very hands-on with Excel, and offers all the advice and software practice utilising these advanced tools which include, building queries to extract, transform, and load data, and also to create elegant data models with PowerPivot. This involves analysing multiple data sets, working with cross-sectional data, merging different sets to create one data model, and using a key range of brand new formulas in Excel.

The course also covers an introduction into the world of DAX formulas (Data Analysis Expressions), designed to work with relational data and perform dynamic aggregation.

All of the latest Excel power BI tools in one course!

Learning outcomes include obtaining a greater understanding of:

• Best practice in producing quality reports

• Picking up sample data and creating a report

• Introduction to PowerPivot

  • How PowerPivot differs from a PivotTable

  • Filtering rows/columns

  • Data and diagram view

  • Combining data sources

• PowerPivot data models

  • Create a PowerPivot data model

  • Extract meaningful insight from a data model

  • Creating and editing data relationships

• Get data / power import from external sources

  • What is Power Query and when to use it

  • Query editor overview

  • Managing data sources and queries

  • Column, row and calculated transform actions

• Introduction to DAX (BI) functions

  • What is DAX?

  • How to create effective DAX formulas, nesting

  • Functions: SUMX; CALCULATEX; RELATED; AGGREGATE; and more

  • Ranking

• Power BI Desktop: Produce a forecast model

  • Identify the need for Power BI tools via the desktop application

  • Upload manipulated data into the application from Excel

  • Discover patterns and trends in data to aid decision-making

  • Create a simple dashboard using built-in tools

• Deeper dive into Power BI Desktop

  • Data modelling features and handling data from multiple sources

  • Further DAX functions and calculations

  • How to maintain data as it changes

• Bonus topics

  • DAX cheat sheet

  • Calculated items and KPIs

  • Fully integrated Power Report to take away for own use

  • Further exploration and advice into queries and database connectivity

  • Homework topics for further study

Laptops with the correct software installation can be provided, however if you wish to use your own computer, it is advisable to have the Power BI Desktop App installed.