sss

Microsoft BI Training Courses

Use Microsoft’s range of BI (Business Intelligence) tools to analyse data sets quickly, provide visual insights via reports and dashboards and to share that data with colleagues.

Choose from Power BI or Get and Transform to learn how to get the most from your data using Microsoft BI.

Microsoft Excel Power Query course

1 day

In this Microsoft Excel Power Query course you'll learn how Excel gets data from almost anywhere (web, databases, Excel files, SharePoint, Salesforce etc.) and enables you to tidy it up it in many ways (clean, transform, merge and append).  Non-technical Excel users can analyse data and produce reports quickly using it’s GUI interface that’s easier to learn than formulas or VBA.

Download PDF

What will you learn?

At the end of this Microsoft Excel Power Query course, delegates will be able to:
  • Create effective and professional reports
  • Gather and transform data from multiple sources
  • Discover and combine data in mashups
  • Learn about data model creation
  • Explore, analyze, and visualize data

Audience?

Anyone using Excel to clean and reformat data to produce reports.

Course Contents


Introduction

  • What is it?
  • Installing Power Query
  • The interface

Common Data Import Sources

  • Working with CSV; Text; Excel Files
  • Importing multiple files
  • Working with web data
  • Scraping Data from web pages
  • Call to a Web Service

Folders and Multiple Files

  • Get data from Windows File manager
  • Combining Data from Multiple Files

Working with Columns

  • Name; Move; Split; Merge
  • Add/Remove; Format; reorder

Filtering and Sorting

  • Using Auto-Filter
  • Using Number, Text and Date Filters
  • Filtering Rows by Range
  • Removing Duplicate Values
  • Filtering out Rows with Errors
  • Sorting
  • Grouping rows

Changing Values in a Table

  • Replacing Values
  • Transformations: - Text; Number; Date/Time
  • Replacing Missing Values
  • Removing spaces, special characters

Table Transformations

  • Unpivoting Columns to Rows
  • Transposing a Table
  • Creating Custom Columns

Loading Data

  • Into a Worksheet
  • Into the Excel Data Model
  • Table Relationships
  • Refreshing Queries Manual & Auto

Query Editor

  • Edit Query Steps and Settings
  • Refresh a Query

Queries

  • Understanding Power Query’s M language syntax
  • Merging tables and queries
  • Using functions in columns
  • Using IF statements
  • Creating custom functions
  • Implementing dynamic parameter tables
  • Creating calendar tables
  • Sharing queries
  • Best practice

Course Materials

Enliten IT will provide each delegate with a workbook and other useful reference materials where applicable.  These may be either paper based or in Adobe pdf format or a combination thereof.

“For trainers knowledge I would have scored a 10 if there was that option. Marko was brilliant and made what was a complex matter enjoyable and more importantly more easily grasped.”For trainers knowledge I would have scored a 10 if there was that option.

Additional Information

NOTE:   We only offer this Microsoft Excel Power Query course course on a private in-house basis.

Enliten IT reserves the right to modify course content and materials as required in the interests of continuous course improvement, at any time, without notification.

The suggested course duration is a guideline.  Course topics and duration may be modified by the instructor based upon the knowledge and skill level of the course participants.

Contact us to discuss your training needs...

0845 108 5481 0845 108 5481
info@enliten-it.com info@enliten-it.com