Excel Dynamic Power Query and Power Pivot Time Intelligence DAX

Nett Course Fee
$1090

Course Description

This course is tailored for professionals from both technical and non-technical backgrounds who aim to harness the power of large data sets for critical decision-making. Participants will learn to:

Utilize Excel Power Query to normalize data and create dynamic data models and queries.
Apply DAX and its time intelligence functions to perform calculations and aggregate data across various time periods, including days, months, quarters, and years.

Prerequisites

This course is suitable for those who have attended @ASK Training’s “Introduction to Power Query, Power Pivot, Data Model and DAX”.

Course Objectives

By the end of this course, participants will have acquired the following skills and knowledge:

- Normalize tables and create effective data models for data analysis.
- Understand basic M language for editing purposes.
- Convert static queries into dynamic queries using parameters.
- Consolidate data using the Group By feature in Power Query.
- Create and utilize Date/Calendar Table.
- Understand and apply DAX and its Time Intelligence functions for conducting time-based analysis.
- Use Power Pivot Data Model and tools to create dashboards that facilitate decision-making by highlighting key data insights.

Course Outline

Power Query

Normalise tables with Power Query
Understand Basic M Language
Explore Group By Features
Create Dynamic Query with Parameter

Power Pivot

Formulate Calculated Columns and Measures
Difference between Implicit and Explicit Measures
Work with Date/Calendar Table
Must Know DAX Functions:
1. Understanding Expression
2. Calculate, Filter
3. CountRows, DistinctCount
4. X Functions : Sumx, Averagex, Countx
5. All, AllExcept, AllSelected
6. Error Handling

Create Time Intelligence DAX for Data Analysis

PreviousYear & NextYear
DateYTD & TotalYTD
FirstDate & LastDate
DatesBetween
Closing/OpeningBalance
DateAdd
ParallelPeriod

Apply Power Pivot Useful Tools

Named Sets
Hide from Client Tools & Perspectives
Hierarchies

Understand CUBE functions

This 1-day ‘Bridging Big Data Analytics Using Power BI’ course, endorsed by SkillsFuture Singapore (SSG), offers a solution to efficiently streamline data analysis and aid in proposal creation.

Course Duration:
1 Day (9.00am – 5.00pm)

View course details here

Visual Basic for Applications in Microsoft Excel – Fundamental

The ‘Visual Basic for Applications in Microsoft Excel – Fundamental’ course focuses on practical VBA programming skills to unlock spreadsheet potential, enhancing productivity through task automation and simplification, such as copying data between sheets or performing statistical functions across multiple sheets, addressing repetitive or intricate tasks effectively.

Course Duration:
1 Day (9.00am – 5.00pm)

View course details here