Preparing data sources for analysis using Power Query

Data is all around us. We get it from many sources and don’t always have control of its format or layout, which can make it difficult to analyse, draw insights from and visualise.

Power Query (part of Excel and Power BI) gives you the tools to connect to many data sources and apply transformations including merging, shaping and cleansing.

PowerBI Online Training Workshops


  • Dates: Jul 16th, 2019
  • Dates: Jul 24th, 2019
  • Dates: Aug 1st, 2019
  • Price: $99
  • Skill Level: Advanced
  • Duration: 90 mins
  • CPD hours: 90 mins
  • Live Q&A
  • Instructor Led
  • Class size (max): 25
  • Exercise Materials
  • Great Reviews
  • Recorded Playback

BOOK NOW

Learning Outcomes in a Nutshell

During this Live Workshop we will show you a number of common data problems and demonstrate various transformation techniques that you can put into practice straight away.

Course Content
Introduction to Power Query
  • Navigating the Power Query Workspace
  • Power Query in Excel and Power BI
  • The M language
  • Accessing the Advanced Editor and Formula bar
  • Using query steps

 Why we transform data

  • What data formats work best
  • Less columns, more rows
  • One row, one record

 Transforming simple Pivoted Data tables 

  • Removing rows
  • Using Fill up/down
  • Using Go To Special in Excel
  • Using filters on rows
  • Using Unpivot
  • Renaming columns

 Using the transformed output

  • Loading to Excel
  • Loading to Power BI for visualisation
  • Copying data direct from Power Query

 Transforming large and complex Pivot Tables

  • Transforming nested (multiple) row headers
  • Using Transpose
  • Using Merge columns
  • Testing the transformation steps for updated data

 Connecting to multiple web pages

  • Importing a web query using parameters
  • Creating a custom function
  • Defining a List to create a new Query
  • Invoking custom functions

 Appending multiple tables with varying columns

  • Appending multiple tables
  • Merging and Splitting columns

 


Course Prerequisites
There are basic skills and software requirements for this workshop. To get the most out of this webinar, make sure you have the software ready to go.

Skill Requirements:

  • Intermediate to Advanced users of Excel. Some basic knowledge of Power Query is assumed (Power BI Intermediate course level).

Software Requirements:

  • Power Query either in Excel or Power BI

How our Webinars Work

  • Click Book Now, select your Webinar and Sign Up
  • Receive Email with Login Details
  • 5 Days ahead of course check your login credentials work
  • Day of course Log On to join the Webinar
  • Q&A session will be facilitated and explained by the presenter

EXCEL CONSULTING Microsoft WORKSHOPS


Why train with Excel Consulting?

“We are passionate about Professional Development and continue to invest in our trainers, courses and material so our students can achieve fantastic learning outcomes.”

presentation

Instructor Led

cityscape-2

Remote Login

notebook

Materials

clock_re

Short Sessions

certificate

Expert Trainers

chat

Live Q&A


Meet our Trainer

Matt Burr is a Business and Data Analyst with extensive experience in both the public and private sector.

After completing a double Bachelor Degree in Economics and Commerce at the Australian National University, Matt spent over 10 years working in the Construction, Defence, Resources and Health sectors before bringing his experience and knowledge to our Canberra venue as the lead Trainer.

Matt brings current technical knowledge and relevant real-world applications which results in great learning outcomes for our students. In the last 6-months Matt has trained over 250 students, many repeat clients, maintaining an excellent student satisfaction rating of 4.7 out of 5.


Book Now or if you are eager to learn more, check out our face-to-face Power BI Courses.