Manipulating Big Data Analytics Using Power Query & Power Pivot (Level 2)

The Big Data using Power Query and Power Pivot 2010/2013 tools


2 days
All levels
0 lessons
0 quizzes
0 students

Course Info

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.

Power Query is simply the best transformation and data manipulation tool for Excel and easier to use, understand and master than any other tools. Power Pivot is an add-in for Microsoft Excel 2013 that enables you to import millions of rows of data from multiple data sources into a single Excel workbook, create relationships between heterogeneous data, create calculated columns and measures using formulas, build PivotTables and Pivot Charts, and then further analyse the data so that you can make timely business decisions without requiring IT assistance.

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.

Defining Measures for business performance.

o Distinguishing the role of measures.
o Translating key business concepts into measures

Implementing DAX functions in Power Pivot

o Expressing information with measures
o Exposing hidden information from data

Exploiting data analytics with aggregation

o Quantifying information with DAX functions
o Aggregating data from other tables

Performing date and time analysis

o Grouping dates for time analysis
o Comparing and categorizing time periods with time intelligence functions


Delegates should be proficient users of Excel, familiar with PivotTables and understand the basics of Power Query. Delegates should be proficient users of Excel and be familiar with the basics in creating Power Pivots, understand the concept of relationships in related 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 organisation. Excel Power Users, executives, managers, data & business analysts, IT professionals, managers, MIS professionals and BI Consultants/ BI Developers.

Course Outline for This Programme

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