Masterclass Program 2

Banner
June 7 and 8, 2018,
7 en 8 juni 2018

Program for Master your data with PowerQuery, June 8, 2018
Het programma van Master your data with PowerQuery, 8 juni 2018

(The program for day 1 is located here)
(The program for the 1st masterclass is here)

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

If you would like to follow along with the presenters, make sure you bring your laptop. Our presenters will typically be using the most recent version of Office 2016. Using an older version? Make sure you have the PowerQuery and PowerPivot add-ins installed.
Wilt u meedoen met onze presentatoren? Zorg dan dat u een laptop meebrengt. Onze presentatoren zullen vooral de meest recente Office 2016 versie gebruiken. Gebruikt u een oudere versie van Excel, installeer dan in ieder geval de PowerQuery en PowerPivot invoegtoepassingen.

Registration
Registratie

We have closed registration,
hope to see you next year!

Location
Locatie

Level Eleven, Zilveren toren Amsterdam
Stationsplein 51
1012 AB Amsterdam

Please note this program may be subject to change!

Start End Subject
08:30 09:00

Registration

09:00 9:10

Start and introduction

Tony De Jonker & Jan Karel Pieterse

9:00 17:00

Master Your Data With PowerQuery

For more detail look below…

Ken Puls

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.

Target Audience

Anyone who needs to pull data into Excel, clean it up and/or consolidate it. Experience working with Pivot Tables is an asset, but not required.

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.

Time Commitment:

This course is a hands-on course and runs approximately 7 hours in length, depending on the class size and attendees experience in working with Excel.

Course At A Glance: Get Power Query & Transform your Data

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

Top Excel classes, training and events