Skip to main content

Remote training Excel Power Query: Importing and editing data with Power Query

Version: Office 365
Duration: 1 day
Price incompany training: € 950 VAT excl. / Terms for incompany training.

Description

You often have to re-edit the data you enter into Power Pivot: splitting up the text into different columns, converting text to upper case, adding extra columns with certain calculations, filling up blank cells and much more. This means that you cannot use certain sources straight away, because you first need to delete unnecessary information. Or because that information has a different layout. With the aid of Power Query you can get around these problems without difficulty. You input the data, define what is to be done and then enter the optimised data, in Excel, or in the Power Pivot data model. And this without programming in VBA!

Objectives

After this remote training, you can easily:

  • Input different data sources in Power Query
  • ‘Mould’ data so that it is ready to be analysed
  • Load data into Excel or in the PowerPivot data model

Target Group

Anyone who ends up having to re-edit data before it can be used in Excel or in PowerPivot .

Prerequisites

A sound knowledge of Excel is required. Knowledge of PowerPivot is not required.

Content

Part 1 Power Query

  • Why use Power Query instead of functions in Excel?

Part 2 Importing data

  • Importing tables contained in the Excel file
  • Getting external data (Excel, txt, csv, all files in a folder, SQL…)
  • Data source settings (Storing login data from previous connections)

Part 3 Uploading data

  • To a table on a worksheet
  • To Power Pivot
  • Not uploading Queries

Part 4 Transforming data

  • Converting (US/European format, changing data types, deduplicating, deleting records, filtering, aggregations, blank cells, UnPivot…)
  • Adding columns (Predefined options in the menu, letting Power Query decide based on examples, entering calculations yourself)
  • Extending queries with data from other queries (cfr: Vlookup/Xlookup)
  • Comparing queries with each other (What is common? What is only in query 1 and not in query 2? …)
  • Merging similar queries (placing below each other)

Part 5 M

  • Introduction to the M formula language to write even more powerful queries
View all our trainings

Request a quote

"*" indicates required fields

Address*
Name*
In which language(s) do you wish to organise this training?
You apply for this course as:
Do you want the training online or on-site?