Excel is one of the most widely used tools for data analysis, but traditional Excel has some real limitations: it can be very time-consuming importing and cleaning data; Excel can only fit a million rows; and while pivot tables are a great analysis tool they can only get data from a single flattened table and the calculations they perform are very limited. With the new power tools that has all changed! In this advanced course we introduce Power Query for automating the process of getting and transforming data, Power Pivot for building related data models able to contain millions of rows and DAX functions which allow us to perform sophisticated analyses in our Pivots.

POWER QUERY FOR GETTING DATA
• Get data from Excel
• Get data from other sources
• Get data from a folder

POWER QUERY AND M FOR TRANSFORMING DATA
• Fill in the blanks
• Remove unwanted columns and rows
• Remove duplicates
• Merge, Split and add columns
• Pivot and Unpivot data

DATA VISUALISATION
• Add Conditional Formatting to Pivot tables
• Create Pivot Charts
• Add slicers and link to multiple pivot charts
• Convert Pivot Tables to OLAP cubes for more visualisation options
• Use visualisations to create a dashboard

POWER PIVOT
• What is Power Pivot and how to enable it
• Add data to the Data Model
• Create a Calendar table
• Add Relationships

DAX
• Introduction to DAX (Data Analysis Expressions)
• Create calculated columns using DAX
• Create simple measures using DAX
• Create Pivot tables using the data model and DAX measures

POWER QUERY FOR COMBINING DATA SETS
• Append queries
• Understand merge query options
• Perform a range of merge queries

Ideal for individual delegates who have specific requirements from the software, or where previous knowledge makes group training inappropriate. The pace and content can be adjusted to the individuals exact needs.

PREQUALIFICATIONS
Advanced Excel training is for people who can already create moderately complex spreadsheets and want to add more sophistication and automation. Candidates should have completed the Intermediate level, or feel competent they have an intermediate knowledge of Excel.

• 1-day Face to Face instructor-led program
• 2 x 3-hour web-based virtual sessions

  • Extensive Learning & Reference Guides provided on USB
  • Participation in the full day workshop
  • Access to a Microsoft technical expert throughout and post the workshop