Program

This is the Program of the 2014 Event. Dit is het programma van het evenement in 2014

All presentations during this event will be in English.
Merk op dat de voertaal van de dag Engels is.

9:00 Registration
Registratie
9:30 Start and introduction of speakers by Tony De Jonker & Jan Karel Pieterse
Begin van de dag en presentatie van de sprekers door Tony De Jonker & Jan Karel Pieterse
9:45 Tips and tricks by our presenters
Tips en trucs door onze experts
10:15 Keynote
Plenaire sessie
Bill Jelen And Ken Puls
Power Excel: PowerPivot, Power Query, Power View and Power Map in one consolidated solution!
Power Excel: PowerPivot, Power Query, Power View en Power Map in een geconsolideerde oplossing!
11:00 Break and networking opportunities
Pauze (netwerkmogelijkheid)
11:15 Round 1
Ronde 1
12:30 Lunch
13:30 Round 2
Ronde 2
14:45 Break and networking opportunities
Pauze (netwerkmogelijkheid)
15:00 Round 3
Ronde 3
16:15 Break and networking opportunities
Pauze (netwerkmogelijkheid)
16:30 Excel Visualizations by Tony De Jonker & MVP support
Excel Visualisaties door Tony De Jonker & MVP support
17:15 Wrap up by Tony De Jonker & Jan Karel Pieterse
Afsluiting door Tony De Jonker & Jan Karel Pieterse

Sessions

Round Subject Speaker
1

VLOOKUP – from Basic to Advanced.

  • Learn all aspects of using VLOOKUP, INDEX/MATCH, and LOOKUP.
  • Learn how to make your lookups operate faster
  • How to troubleshoot VLOOKUP.
  • Find the last match.
  • Find the second match.
  • Total all lookups in a single formula.
Bill Jelen

Introduction to PowerPivot.

PowerPivot is revolutionizing the way that we look at data inside Microsoft Excel. Allowing us to link multiple tables together without a single VLOOKUP statement, it enables us to pull data together from different tables and databases where we never could before. But linking data from multiple sources, while powerful, only scratches the surface of the impact that it is making in the business intelligence landscape.
In this class, Ken will walk through creating a basic PowerPivot model, demonstrating the incredible capabilities of the product. Topics to be demonstrated include:

  • Connecting to a database with PowerPivot
  • Linking tables
  • Creating a PivotTable which uses fields from multiple data sources
  • Writing custom calculated fields which return different outputs in the same column of the PivotTable
  • Aggregating critical business info into a document that your system doesn’t have

If you haven’t seen PowerPivot before, YOU NEED TO ATTEND THIS CLASS! Plain and simple, PowerPivot is the future of Business Intelligence in Excel.

Ken Puls
2

How to make Excel Calculate Your Workbooks Faster

Topics:

  • Excel’s smart calculation engine
  • The impact of Volatility and Multi-threading
  • User-defined functions and Array formulas
  • Lookups and SUMPRODUCT
  • Finding Calculation bottlenecks
  • Golden Rules for Faster Calculation
Charles
Williams

Date Intelligent Measures – Building a PowerPivot Time Machine.

To anyone who works in retail or finance, one of the most important things we want to be able to do is step back in time to look at results. Our dashboards are full of stats like Month to Date, Year to Date and so on. But what happens when we have huge amounts of data at our fingertips and we get asked to compare Month to Date versus the same period last year? Do you have to create another Pivot Table and/or some complicated formulas to pull it off? Depending on the approach this can be dangerous and maintenance can become a nightmare.
With PowerPivot this game changes significantly and with a single date pattern you can step through and answer 95% of your date period needs. From Month to Date, to Month to Date x Months ago, to same weekday last year, it can easily be done.
In this session Ken will discuss:

  • The importance of a Calendar table in your PowerPivot models
  • The “Golden Date Pattern” for calendar years
  • Forcing measures to return data based on the earliest or latest date selected in a slicer or timeline
  • Understanding and avoiding the “‘DATEADD’ only works with contiguous date selections” error
  • Returning time intelligence over an inactive relationship

Please Note: This course will be covering DAX measures in PowerPivot. Attendees should either have some working experience with PowerPivot, or attend the Introduction to PowerPivot session earlier in the day.

Ken Puls
3

Array formulas

  • Building Dynamic Ranges
  • Extracting data, based on criteria
  • Counting all Fridays between two dates
  • Unique counting formulas
  • Extracting unique lists
  • Sorting with a formula
Bill Jelen

PowerView and PowerQuery

Power Query is an amazing new tool for importing your data in Excel. Many users mainly use csv files to import data in Excel and unfortunately, this technique involves a lot of work (concatenate, delete, group data). With Power Query, you don’t need to do all of this because the tool saves all your data manipulation before importing the data into your Spreadsheet.
During the session we will see:

PowerQuery:

  • Basic Power Query manipulation
  • Import from different sources
  • Import from many sources
  • Formulas

Powerview:

  • Presentation (Table, Chart, Map)
  • Dynamic Filter
  • Drill up / Drill down
Frédéric Le Guen

Top Excel classes, training and events