Microsoft Excel Power Query (Advanced)

Power Query is simply the best transformation and data manipulation tool for Excel and easier to use, understand and master than any other tools.


1 day
All levels
0 lessons
0 quizzes
0 students

Course Info

Excel 2013/2016 Power Query can connect to 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.

What Will I Learn From This Course?

Importing Data from Web Page and data feeds.

Transforming poorly structured data into useful data.

Use Power Query Functions & create customized functions.

Exploring the M code and take your Power Query skills to advance levels & create dynamic query.

Creating and implementing calendar tables.

Target Audience

Anyone who needs to work with Microsoft Excel in order to connect to external data and then process, analyse and display the data in order to produce suitable output at all levels within an organization. Excel Power Users, executives, managers, data & business analysts, IT professionals and BI Consultants.


Delegates should be proficient users of Excel, familiar with PivotTables and understand the basics of Power Query Level One.

At Course Completion:

Upon successful completion of this course, the delegate should be proficient with Power Query and have the necessary skills to implement and automate the process of pulling in data, perform data transformation and data cleansing.

Course Outline for This Programme

This module explains to connect to website to import data from tables. Learn how to connect to OData feeds to get data. OData is a standard for providing access to data over the internet.
In this module, you will reshape data complex data structures such as cross tab tables, tables with multiple headers.
This module shows how to use more power query functions and multiple condition logic, build customized functions and use it for repeated transformation.
In this module, you understand the logic of “M” code and how code is written. Complicated data transformation can be done using “M” language for Power Query. Learn how to access the HELP to Power Query functions. You will create a dynamic query using “M” code which makes the query flexible and allows others to use the query without having to modify it.
Date table is important with Time Intelligence scenarios with Power Pivot. With a dynamic date table, you will not need to extend your calendar manually as time goes by. You will also create Fiscal calendars which is a little more complicated than a normal fiscal year that ends in December.
Curriculum is empty

Academic Qualifications
  • Diploma in Accountancy
  • ACCA (Association of Chartered Certified Accountants), UK

Professional Certifications
  • Pembangunan Sumber Manusia Berhad (PSMB) Certified Trainer
  • Microsoft Certified Trainer (MCT)

Julian specialises in conducting MS Excel application courses & seminars for various companies, local & multinationals. He teaches practical on-the-job experience, which translates to real-life exercises for participants’ benefit. Julian conducts various IT courses using Word Processors, Spread sheet, Database, Multimedia & Programming applications