Program for The Amsterdam Excel Summit, April 19, 2017
Het programma van The Amsterdam Excel Summit, 19 april 2017

(The program for day 1 is located here)

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

Registration
Registratie

Registration has closed.

Location
Locatie

Zilveren Toren Amsterdam
Stationsplein 51-53
1012 AB Amsterdam
The Netherlands
Map and Directions

Start

End

BI track

Deep Into Excel track

Registration

Start and introduction of speakers

Tony De Jonker & Jan Karel Pieterse

Key note

Guy Hunkin (Microsoft Excel Senior Program Manager)

Automate your data preparation using Power Query

Are you tired of copying and pasting data into Excel worksheets?  Power Query is the ultimate tool that automates the process of getting data into Excel and will save you hours of repetitive and error-prone work.

Power Query makes it easy to extract data from many different data sources, filter that data, aggregate/clean it, and perform calculations on it and finally loading that data into your worksheet.

Frédéric will demonstrate the following topics:

• Importing data from the web
• basic manipulation (filtering / removing columns)
• Pivoting/unpivoting tables
• Creating a time table
• Creating a personal function based on the minimum date
• Getting back on track in case you make a mistake when defining the transformation steps

Frédéric Le Guen

Make Your Life Easier with Excel Tables

Excel Tables (not “tables”) have been helping users since their introduction as “Lists” in Excel 2003. Four major versions later, Excel’s Tables have matured into a powerful and dynamic data structures. Their filtering and sorting capabilities as well as their dynamic nature make them an indispensable sidekick to Pivot Tables and Charts. We’ll look at just a few ways that Tables can make Excel more fun.

Jon will discuss:

•  Keep your data organized
• Autofill formulas and formats effortlessly
• Enable quick sorting and filtering
• Expand pivot table source data automatically
• Create truly dynamic charts without defining Names or using VBA

Jon Peltier

Power Query versus Native Excel: the Battle

There’a a lot to love about Power Query, but some things are difficult and not intuitive:

• The problem with hardcoded column headers
• Shifting rows
• Cell formatting
• Some counting starts at 0, others start at 1
• Duplicates that PQ doesn’t see as duplicates

Oz Du Soleil

A comparison of Excel Extensibility Technologies

VBA, VB6, VSTO,
XLL, Excel DNA, Javascript Office Addins, …

The objective for this session is to answer questions like:

• What are the strong points and weak points of XXX?
• I hear XXX technology is the way forward for Excel, lets do the next project using that
• Lets convert this project to XXX
• VBA is dying – what technology should I retrain in?
• I want to use XXX with Excel, where do I start?
• How long does it take to learn XXX?
• I have an idea for an Excel-based product I want to sell …

Charles Williams

Creating dynamic financial statements using Power Pivot

In this session we’ll deep-dive into some unknown DAX functions and Power pivot functionality. We will build a balance sheet and a Profit & Loss Statement. To accomplish this we use the DAX PATH functions and the new possibilities of VAR definitions within Excel 2016. Next we will build a control table to combine slicers with (predefined) measures. The prime DAX function we use in this case is the is DAX SWITCH. Basically what we’ll do is create different measures and use a slicer to present the selected measure in the desired dashboard. By combining the hierarchy with  the control table the output dashboard stays very compact.

Henk Vlootman

Bing maps & Excel

What’s the distance between two cities (addresses) if you go by car, walk or take public transportation? Where in Excel can we get that information? Short answer is nowhere.

But wait, we live in the time of internet and web API-s. And guess what. Bing Maps does allow you to create an API and do requests from Excel. In this session, we’re going to do that three different ways. The traditional Excel Way, VBA and if we really feel “Olympic”, we’re going to use Javascript.

Gašper Kamenšek

Moving beyond Excel with Power BI

If you haven’t come across Power BI yet, you’re missing a huge change in the business intelligence landscape. A free tool that lets you rapidly collect, clean and transform your data, it also allows you to build great insights quickly using the Excel skills you already know.  Even better, however, it also removes several sharing barriers that existed with Excel… have you even wanted to deploy an interactive report to your phone?  Well now you can!

Join Ken as he shows how you can take your Excel knowledge into the new and exciting world of Power BI.

Ken will cover the following topics:

• Collect and clean data using Power BI Desktop
• Quickly build interactive and engaging visuals
• Publish your report to the web
• Deploy your reports to your phone
• Generate additional insights with ease

Ken Puls

The best Excel modeling tips, tricks and traps

Tony de Jonker

Office JavaScript

Microsoft Principal Designer Jakob Nielsen and Microsoft Program Manager Sudhi Ramamurthi will be presenting this session. Introduction to the modern Office application development landscape:
In this session, we’ll go over basic tenants of web based Office extensibility solutions, including web components, add-in development lifecycle, deployment and publishing, and resources. We’ll go over the important aspects of realizing an add-in solution such as: development environments, tools, relationship between APIs/features, Office versions and platforms, publishing process, and getting help. At the end, we’ll touch upon the service based access to Office documents, mainly Excel, to address offline document access scenarios using Microsoft Graph REST APIs.

Wrap up