Microsoft Excel PowerQuery

Excel PowerQuery

TRAINING OVERVIEW

Power Query can connect to a wide range of data sources. Databases such as SQL Server, Oracle, Text files, Big data and Hadoop data sources, online searches and OData feeds, Cloud storage, Facebook, SharePoint and many other types of data storage. Power Query extracts the data from sources and does transformations such as splitting columns, data conversions, lookups, merge, append, data cleansing and many other data transformations AUTOMATICALLY without the use of VBA. Power Query introduced a new expression language called “M” for data transformations. Power Query is simply the best transformation and data manipulation tool for Excel and easier to use, understand and master than other tools.

TRAINING OBJECTIVE

  • Import / connect to external data from multiple sources.
  • Automate the data transformation & data cleansing processes.
  • Combine data from multiple sources into a single source.
  • Merging data without the need of formulas such as VLOOKUP, INDEX & MATCH.
  • Perform calculation to aggregate

TARGET AUDIENCE

Excel Power Users, data analysts, business analysts, managers, MIS
professionals and BI Consultants / BI Developers

OUTLINE

Introduction To Power Query

This module explains what is a power query and how you can download and install the power query on your computer. Learn how data files are imported/connected whether it’s a text file, CSV file, database or any other source. Once the connection is built, refreshing the connection will automatically load the latest
changes.

Appending Data From Multiple Sources / Formats

In this module, you will append data together into one source from multiple file formats, multiple sources and even an entire folder for the purpose of analysis. If the source is an Excel file, learn to pull data from multiple workbooks, multiple sheets, and multiple tables.

Merging Tables

This module shows how easy to merge data without using formulas such as VLOOKUP, INDEX & MATCH.
Perform more advanced merges using Inner, Outer, Full, and Anti Joins. Learn to automate reconciliations
and throw out exceptional items.

Power Query Formulas

This module shows how to perform calculations such as SUM, AVERAGE & COUNT just like a pivot table.
More complicated data transformation can be done using “M” language for Power Query.

Transforming Data

In Power Query, you will be able to transform selected columns into attribute-value pairs where columns
become rows for data analysis which is required for Pivot Tables. Data that is structured incorrectly can be
transformed easily

Cleaning Dirty Data

The complete removal of dirty data from a source is virtually impossible. Power Query has some tools which
can be used to clean some common problems such as missing data, data formatted incorrectly or date
problems.

No announcements at this moment.

Be the first to add a review.

Please, login to leave a review
Add to Wishlist
Duration: 1 Day
Level: Intermediate

Archive

Working hours

Monday9:30 am - 6.00 pm
Tuesday9:30 am - 6.00 pm
Wednesday9:30 am - 6.00 pm
Thursday9:30 am - 6.00 pm
Friday9:30 am - 5.00 pm
SaturdayClosed
SundayClosed