HOME
 

PowerPivot / Excel Power BI

SquareOne Training
Course summary
2 days
Professional Training
Next available date: Enquire for more information - Liverpool
Course Dates
Liverpool
Enquire for more information 
London
Enquire for more information 
Manchester
Enquire for more information 

Course description

PowerPivot / Excel Power BI

Excel can do so much more than simple pivot tables, this two-day course will show you how to:

  • Create simple, elegantdata modelsinPowerPivot;
  • Use these data models to create pivot tables based on multiple tables;
  • Use queries to build queries to extract, transform and load data (this used to be calledPower Query, but is now integrated in Excel);
  • Start to use the DAX language for more in-depth analysis of data;
  • Analyse time series data ("compare this month's sales with the same period in the previous quarter");
  • Create hierarchies, and display them in pivot tables;
  • Create and display KPIs in Excel;
  • Understand what Power BI Desktop is, and how it is the same in many ways as PowerPivot / Power Query;
  • Create visualisations in Power BI Desktop (tables, charts and matrices);
  • Understand how to publish Power BI Desktop reports to the cloud, and pin tiles to dashboards.

Suitability - Who should attend?

Prerequisites

Note that you'll need to be using Excel 2016 (or 2013) Professional Plus or Office 365 ProPlus to be able to use the Power BI (Power Business Intelligence) features within Excel.

Training Course Content

PowerPivot data models

  • Importing SQL Server tables
  • Filtering rows/columns
  • Data and diagram view
  • Hiding from client view
  • Relationships

Pivot tables using PowerPivot

  • Drill-down vs Quick Explore
  • Slicers and timelines
  • Controlling what you see

Using Excel tables

  • Creating and naming tables
  • Adding to data models

Using other data sources

  • Importing Access and Excel
  • Importing cubes
  • Pasting from the clipboard

Getting data (Power Query)

  • Querying databases
  • Web pages and XML files

Transforming data (Power Query)

  • Splitting columns
  • Merging and inserting columns
  • Other transforms

Calculated columns

  • Creating calculated columns
  • Using the RELATED function
  • BLANK and SWITCH

Measures

  • The all-important query context
  • Implicit calculated fields
  • AutoSum fields
  • Creating in Excel and PowerPivot

DAX basics

  • The DAX Studio add-in
  • Calculating ratios
  • Aggregate X functions

The CALCULATE function

  • Replacing filters
  • Using ALL
  • Using VALUES to edit filters

More advanced DAX functions - If time

  • Using FILTER to filter tables
  • Ranking
  • The EARLIER function

Calendars

  • Creating in Excel or SQL
  • Linking to calendars
  • Sorting months
  • Multiple date tables

Date functions

  • Period to date
  • Fiscal year calculations
  • Parallel periods
  • Semi-additive measures

Hierarchies

  • Creating hierarchies
  • Pros and cons  

KPIs

  • Relative KPIs
  • Absolute KPIs

Perspectives

  • Creating perspectives
  • Viewing parts of a model

Power View

  • What Power View is
  • Why not to use it!

Power BI Desktop overview

  • Importing data
  • Visualisations
  • Simple drill-down
  • Publishing dashboards

Power BI Desktop maps - If time

  • Localising data
  • Types of map

Why choose SquareOne Training

25 years' experience of delivering quality IT Training Services

All trainers Certified Microsoft Office Trainer (MOS) or higher

Public and in-house training throughout the UK

Expenses

For information on pricing for this open/scheduled course, please give us a call on 0151 650 6907.

This course can also be delivered in-house and tailored to meet the particular requirements of your organisation. 

About provider

SquareOne Training - In-house IT & Computer Training

SquareOne Training

SquareOne Training is an internationally renowned IT Training company who create inspiring, motivational and cost effective training services which bring about tangible improvements in productivity. They specialise in providing classroom courses, Microsoft upgrade training, IT consultancy and large-scale rollout support...


Read more and show all courses with this provider

Request info

Fill out your details to find out more about PowerPivot / Excel Power BI.

  Contact the provider

  Get more information

  Register your interest

Contact info

SquareOne Training

2 Tower Road
CH41 1FN Birkenhead

 Show phone number
www.squareonetraining.com


Reviews

Average rating 5

Based on 3 reviews

Alfie
(5)
Steve
(5)
Barry
(5)
Very Knowledgeable and helpful
Request information

Find out more about this PowerPivot / Excel Power BI course - simply fill out your details:

U.K. L&D Report: 2018 - Benchmark Your Workplace Learning Strategy

Are you curious about the L&D strategies of some of the U.K.'s top companies?

Find out what they're up to in findcourses.co.uk's second annual U.K. L&D Report!

uk-ld-report-2019

Learn more

Find a course for you!

  Read the course
 summary on this page

 Fill in your details and Request information

 Receive all the info you need

Case Study
SquareOne Deliver IT Rollout Pro...

Read about SquareOne's global projects in New Hardware and Software Refresh and Microsoft Lync/Skype Rollout.

Read more
Excel Templates For Mexichem
SquareOne Training

At SquareOne Training we take pride in designing Spreadsheets for our customers, so we were delighted to be asked to design a solution to track staff courses and KPI alerts. This spreadsheet was implemented in 2018, but completely changed the way the company worked and made the data not only accurate but trackable.

Read more
Find more courses here