About the Masterclasses

 

1. Masterclass for Advanced Users: “Master Your Data Using Power Query”

  

26th May, 2020 

 

 Lector: Ken Puls, Canada – Microsoft MVP, President of Excelguru Consulting

Ken Puls is Data Platform and Excel Expert, master of VBA programming, Power Query, Power Pivot, Data cleansing and reshaping.

He is blogger, author and trainer with over 20 years of business and financial modelling experience. His passion lies in exploring tools to turn data into information, and teaching others how to benefit from them.

Course Overview

The sad reality is that not all data is stored in nicely curated databases and often – even when it is – the data analyst doesn’t have access. Instead we have to piece together data provided in text files, Excel files, web pages and even the body of emails to build the business intelligence solutions we need. Until now this has been a painful process with a great deal of cut and paste effort, and updates have been tedious and error prone. That stops today.

In this course, you’ll learn how Power Query can clean up, reshape and combine your data with ease – no matter where it comes from. Converting ASCII files into tables, combining multiple text files in one shot and even un-pivoting data is not only simple, but an investment in the future refreshable with a single click when next needed.

 

If you need to learn one skill in Excel today, how to work with Power Query is it. Not only will it change the way you “Get & Transform” data in Excel, but it’s also the system used to collect data for Power BI desktop, meaning these skills are transferable to other programs.

Software Requirements

Power Query is built in to Excel 2016. For Excel 2010 and 2013, you’ll need to ensure that you have the free Power Query add-in installed. *

*Note that depending on your version of Excel 2013, you may not be able to connect to some “business” data sources like Microsoft Exchange, SQL Azure or SharePoint. All other data sources demoed in the course are available regardless of the Office 2013 version you have.

Course at а Glance

Review of Essential Tools

  • The blueprint of “good data”
  • Working with Excel tables
  • Working with PivotTables
  • Pivot Table layouts and formatting

Importing Data

  • Individual CSV, text and Excel files
  • Individual Non-delimited text files
  • Importing multiple “flat” files at one time
  • Cleaning and manipulating data
  • Refreshing imports

Appending and Merging Tables

  • Append (stack) data from multiple tables
  • 7 ways to merge (join) data from multiple tables (with no VLOOKUPs)
  • Many to many merges

Pivoting, Un-Pivoting and Transposing Data

  • Un-pivot tables with ease
  • Pivoting stacked data
  • Understanding the Transpose feature
  • Un-pivoting subcategorized data
  • Grouping data

Conditional Logic

  • Creating conditional columns
  • Manual IF and IFERROR tests
  • Creating columns from example

Best Practices

  • Query structuring
  • Query folding

This is a hands-on course. Participants should bring a laptop running one of the following:

  • Excel 2013 Professional Plus or Excel 2013 ProPlus with the free Power Query add-in installed
  • Excel 2016 or higher
  • Excel 365

The lectures during the Conference and Masterclasses will be in English.

 

 

2. Masterclass for Experts: “My Perfect Data Model in Modern Excel”

  

26th May, 2020 

 

 

 Lector: Gašper Kamenšek, Slovenia – Microsoft MVP, owner of Excel Olympics

Gašper Kamenšek is Excel and Power BI expert, owner of Excel Olympics. Speaker at more than 50 conferences and events, Trainer with more than 900 courses and seminars. When he is not lecturing Excel, Gasper works into various BI projects consisting either of PowerPivot and SQL with Excel or VBA. His passion in Excel made him starts an Excel blog in 2014. It’s called Excel Unplugged.

 Introduction

There have been numerous sessions on conferences lately (and I’ve done a few myself) titled “The greatest Sales report in the world” or “The best DAX formula in the world”… The problem is that every such view is subjective. What might be the best-looking chart in the world to me, might make no sense to you. I might love VLOOKUP, some of you haven’t used VLOOKUP for years in favor of the INDEX-MATCH combination.

And this is why the title of this Masterclass is not “THE Perfect Data Model…” but is instead “MY perfect Data model…”. This Masterclass will give you insights into the way I use Modern Excel (Power Query, Power Pivot, advanced DAX and advanced Excel functions) to build models that are easy to maintain and are built for best performance and easy scalability.

Software Requirements

Attendees should have Excel Professional Plus (Pro Plus) 2010 or higher. If they use Excel 2016 Pro Plus or 2013 Pro Plus they need no extra installations, for version 2010 Pro Plus Power Pivot Add-Inn for Excel 2010 will need to be installed.

Course at а Glance

This Masterclass will be a hands-on Workshop where we will build the Data Model and a report on top of that, and most of all it will be an advanced DAX Masterclass along with a few tips and tricks I use to make things dynamic and reusable. For those of you, that remember last years T1000 measure, this will be the development of that idea but getting the most out of it. This Masterclass requires basic Power Query and Power Pivot knowledge.

During our day together we will start with basic data, do some amazing data transformations with Power Query (so Power Query must be installed on the participant’s computers), load Data directly into Power Pivot, where we will build a data model and write all the DAX measures we will need. (Please get a good nights sleep before attending as we will dig deep into advanced DAX concepts and functions). Now by this point in the Masterclass, you should already be in love with our data model.

Nevertheless, we will take it a step further and build a report on top of our Data Model so we will all have a nicely wrapped BI solution and a sense of achievement by the end of this Masterclass.

Along with 64 bit Excel, Power Query and Power Pivot also bring a smile and willingness to learn.

The lectures during the Conference and Masterclasses will be in English.

 

 

3. Masterclass for Excel Users: Impress with Interactive Excel Dashboards

  

26th May, 2020 

 

 

 Lector: Boriana Petrova, Bulgaria – Microsoft MVP, owner of ITraining Ltd.

Microsoft Certified Trainer with more than 15 years’ experience as an IT instructor and Consultant.

Certifications she has: Мiсrоѕоft Оffiсе Ѕресiаliѕt Маѕtеr (МОЅ Маѕtеr), Мiсrоѕоft Сеrtifiеd ІТ Рrоfеѕѕiоnаl (МСІТР), Мiсrоѕоft Сеrtifiеd Тесhniсаl Ѕресiаliѕt fоr Мiсrоѕоft Рrојесt Ѕеrvеr (МСТЅ), Мiсrоѕоft Сеrtifiеd Тесhniсаl Ѕресiаlіѕt fоr Мiсrоѕоft Рrојесt (МСТЅ).

Five years Microsoft MVP for Excel.

In 2008 she is awarded among the TOP 25 of the best instructors in Microsoft World Company business evaluation and measurement - Knowledge Advisors.

Speaker of at numerous Мicrosoft events in Bulgaria, USA, Spain, Nederland, Australia, United Kingdom.

Founder and administrator of the user group Microsoft Office Bulgaria for Microsoft Bulgaria.

Founder and organizer of “Bulgaria Excel Days” – one of the biggerst global events dedicated to Microsoft Excel.

Owner and CEO of the company for Microsoft Training and IT business consulting - ITraining Ltd.

Introduction:

  • In this hands-on session we create an impressive, interactive KPI (Key performance Indicator) Dashboard from scratch.
  • We use Excel’s core functionality together with form controls to add interactivity to our report.
  • There is NO VBA, NO Power Query, just smart use of formulas and neat Excel tricks.
  • Prerequisites: Comfortable with writing basic Excel formulas (e.g. SUMIF, VLOOKUP, IF)

Benefits:

  • You don’t just learn how to create the KPI dashboard, you also learn:
  • The process you should follow for a successful Excel dashboard Project.
  • Advanced formulas that will come in handy if you ever do complex analysis in Excel.
  • Smart use of Excel’s core functionality to get impressive results.
  • Form controls without VBA to add impressive interactivity to any of your reports.

The lectures during the Conference and Masterclasses will be in English.

 

 

 

4. Masterclass for Business Anlysts: Business Intelligence and Data Analysis Tool: Power BI

  

26th May, 2020 

 

 

 Lector: Dr. Leila Etaati, New Zealand – Excel MVP

Microsoft AI and Data Platform MVP, Author, Consultant, Trainer

Co-Founder,Data Scientist, Consultant, and Mentor at RADACAD: http://radacad.com

Author of Power BI with R

Co-Founder of Difinity Conference

Co-Founder and Co-Organizer of Power BI user group in Auckland

Microsoft Artificial Intelligence MVP, Data Scientist, Consultant, Trainer

Detailed Abstract

Power BI is the newest Microsoft Business Intelligence and Data Analysis tool. In this module, we will go through the basics of this product and introduce all components of Power BI (Power Query, Power Pivot, Power View, and Power Q&A). You will see some demos and introduction about Power BI desktop, Office 365 Power BI subscription, and Power BI website, and mobile apps. You will see some basic demos of how easy to use is Power BI in some scenarios.

  • Introduction to Power BI: What is Power BI?
  • Power BI Desktop; The First Experience
  • Power BI Website; You’ll Need Just a Web Browser
  • Introduction to Power BI Components: Power Query, Power Pivot, Power View, and Power Q&A.

1.2: Getting Data and Transformation

Getting Data is the first experience of working with Power BI. You can connect to many data sources on-premises or on the cloud. In this section, you will learn how the Get data experience in Power BI is and how you can transform the data in a way to get it ready for modelling.

  • What is Power Query: Introduction to Data Mash-Up Engine of Power BI
  • Different versions of Power Query
  • Power Query Introduction
  • Query Editor
  • Transformation GUI
  • Get Started with Power Query: Movies Data Mash-Up
  • Power BI Get Data from Excel: Everything You Need to Know
  • What is the Role of Power Query in a Power BI Solution

1.3: Data Modelling and DAX

Data Modelling in Power BI is an in-memory-based technology. You will learn about the structure of modelling in Power BI, and you will learn the importance of relationships and their direction. You will also learn about calculations in Power BI and how to write them. DAX is Data Analytical expression language. DAX has a similar structure to excel functions, but it is different. In this module, you will learn DAX about DAX too.

The content that you will learn in this module includes but not limited to;

  • Power BI xVelocity engine basics and concepts
  • Relationships in Power BI
  • Hierarchies and Formatting
  • Sorting by other columns
  • Date Table
  • Introduction to DAX
  • Calculated Columns, Measures, and Calculated Tables

1.4: Data Visualization

Data Visualization is the front end of any BI application; this is the user viewpoint of your system. It is critical to visualise measures, and dimensions effectively so the BI system could tell the story of the data. In this module, you will learn conceptual best practices of data visualisations which is valid through all data visualisation tools. You will learn Power BI visualisation skills. You will learn how to create effective charts, and dashboards using these tools as well as best practices for working with Power BI Desktop.

The content that you will learn in this module includes but not limited to;

  • Power BI Desktop Visualization
  • Formatting Visuals in Power BI Desktop
  • Basic Charts and Visuals in Power BI
  • Sorting, Filtering, and categorisation
  • Custom Visuals in Power BI Desktop
  • Maps and Geo-Spatial Visualization

1.5: Power BI Service

Power BI components can be deployed into different environments such as Power BI website, Office 365 subscription, or even SharePoint on-premises. In this module, we will go through deployment options, Configurations, and requirements required for each environment. You will learn how to deploy your Power BI files into Power BI website. You will also learn how to build dashboards on the website, and how to work with Mobile Apps for Apple, Android and Windows Phone.

The content that you will learn in this module includes but not limited to;

  • Basic Sharing and using workspaces in Power BI web site
  • Dashboard vs. Report
  • Power Q&A
  • Mobile Dashboard Design
  • Row Level Security
  • Schedule Refresh vs. Other types of connections
  • Gateway’s Role in the Service

1.6: Visualization Basics

Visualization is an important part of any BI system. In Power BI, Visualization plays a critical part. In this section, you will learn about why visualization is important, with few basics of visualizations such as comparing stacked vs. clustered chart. You will also learn in this section about data exploration techniques such as drill down/up, using pages, etc.

  • Importance of Visualization
  • Basic Charts; Bar and Column Charts
  • Clustered vs. Stacked Charts
  • Q&A for Reports
  • Quick Measures in Power BI
  • Drill Down and Up using visuals and hierarchies
  • Pages

1.7: Slicing and Dicing

The importance of slicing and dicing data in Power BI is critical. Power BI is not a visualization tool only, but it is also a data exploration tool. There are several ways you can filter the data. In this section, you will learn the difference between filters and slicers. You also learn about different scopes of filtering, as well as filtering modes. For slicers, you will learn different types of slicers and some advanced features such as syncing slicers through different pages.

  • Filters
  • Scopes of Filters: Visual Level, Page Level, Report Level
  • Modes of Filtering: Basic, Advanced, Relative, Top N
  • Drill through Page Filter: Creating master-details page scenario in Power BI
  • Slicers
  • Types of Slicers; Date, Text, Number
  • Slicer Formatting
  • Syncing Slicer between Pages

The lectures during the Conference and Masterclasses will be in English.

 

 

5. Masterclass for Financial Analysts: Budget and Forecast Modelling in Excel

  

26th May, 2020 

 

 

 Lector: Danielle Stein Fairhurst, Australia – Financial Modeller

Danielle Stein Fairhurst is the Principal of Plum Solutions, a Sydney-based consultancy specialising in financial modelling and analysis.  She is the author of “Using Excel for Business and Financial Modelling”, Third Edition published by Wiley Finance 2019 and “Financial Modeling in Excel for Dummies” published by Wiley in 2017.

Introduction

If you are using Excel for building budgets, forecasts and management reports, this one-day masterclass covers the practical tools and modelling techniques to manage the budget process.  We explore the difference between building budgets and forecasts, and learn how to create your own budget model, perform what-if analysis, break-even and forecast scenarios.

Topics covered:

  • Budgeting and forecasting as a planning and management tool
  • Forecast modelling best practice
  • Using Management Reporting to promote accountability
  • Building dynamic variance analysis reports
  • Budgeting Techniques such as top-down, bottom-up and zero-based budgeting
  • Limitations of budgets and critique of the budget process
  • Budgeting "Soft" Skills
  • Creating a "rolling" forecast with live data
  • Phasing and seasonality in forecast models
  • Scenarios - Stress-testing, break-even and sensitivity analysis
  • Budgeting for Capital Expenditure and Depreciation

During the class, participants will build their own forecasting models to bring a practical application to the skills they have learned.   The model will include capex spend, cashflow and depreciation calculations as well as scenarios and sensitivity testing. Examples, templates and practical exercises will be provided; please bring a laptop for this hands-on and interactive session. 

The course is demonstrated using the latest Microsoft Excel 365, but participants can use an earlier version if they prefer.

The lectures during the Conference and Masterclasses will be in English.

 

 

SPEAKERS

Learn Excel from the best

Lectures by leading world experts in Microsoft Excel, PowerBI for Excel, Data Analysis and Visualisation, all of them – awarded by Microsoft’s most prestigious award – Microsoft Most Valuable Professional (MVP)