Subscribe e-Newsletter
    Member Login
    Course Information
    Email
    Pass
    Forget password? Click here
    Event Profile
    Class/
    Online
    Classroom
    Date September 24 - 25, 2018
    Time 9:00am to 5:00pm
    Venue Mandarin Orchard Singapore
    333 Orchard Road
    Singapore 238867
    Fee
    7% GST will apply
    SGD 700.00
    For Member
    SGD 665
    NoteTwo tea breaks and set lunch or buffet will be served. Limited complimentary car parking coupons are available upon request.

    Participants are required to bring a laptop installed with Microsoft Excel 2016/ 365/ 2019 for Windows. This course is not for Mac version Microsoft Excel.
    Other Date(s)1) May 06 - 07, 2024
    2) Oct 28 - 29, 2024
    Trainer
    Activity
    You may reach us via
    T: 6204 6214
    E: info@ccisg.com
    Alternatively, you may send below details to register
    Contact Person
    Company (optional), Name, Job Title, Mailing Address, Tel, Email

    Participant(s)
    Name, Job Title, Email
    This course introduces Microsoft Excel's powerful data modeling and business intelligence tools: Power Query, Power Pivot, and Data Analysis Expressions (DAX).

    Business intelligence (BI) is a collection of tools and processes that are used to gather data and turn it into meaningful information that people can use to make better decisions.

    You'll hands-on with Power Query; a tool that extract, cleanse, transform, and load data from flat files, folders, databases and more. You'll practice shaping, blending and exploring the project files, and create completely automated loading procedures with only a few clicks.

    Next, you'll explore the Data Modeling and cover the fundamentals of database design and normalization (including table relationships, hierarchies and more). You'll take a tour through Excel's data model interface, and then create your own relational database to analyze throughout the course.

    Next, you'll use Power Pivot and DAX to explore and analyze the data model. Unlike traditional Pivot Table, Power Pivot allows you to analyze hundreds of millions of rows across multiple data tables, and create powerful calculated fields and measures using a formula language called Data Analysis Expressions (or "DAX" for short). You'll learn basic DAX syntax, then introduce some of the most commonly-used and powerful functions like CALCULATE, FILTER, SUMX and more.

    IMPORTANT:
    This course is only for PC users that have a copy of Excel that is compatible with Power Pivot. Power Pivot is an add-in for Excel. Not all Excel versions can support Power Pivot add-in. You must have one of these Excel versions. Click the first link below to check which versions of Excel can support Power Pivot. Then click the second link to learn how to turns on the Power Pivot add-in.

    Link: http://bit.ly/WhereIsPowerPivot
    Link: http://bit.ly/CheckPowerPivot
    Objective
    1. Getting you up & running with Excel's data modeling & business intelligence tools
    2. How to use Power Query, Power Pivot & DAX to transform your workflow in Excel
    Outline
    Section 1: Introduction
    1. The Business Intelligence components
    2. Benefits of Business Intelligence tools
    3. When to use Power Query and Power Pivot

    Section 2: Power Query
    1. Types of data connections
    2. The Query editor and tools
    3. Data loading options
    4. Basic table transformations
    5. Using various data types specific tools
    6. Index and conditional columns
    7. Pivot and Unpivot
    8. Grouping and aggregating data
    9. Working with workbook queries; merging, appending, deleting
    10. Connecting to a folder of files
    11. Refreshing the query

    Section 3: Data Modeling
    1. Starting Data Model
    2. Changing to different views
    3. Database Normalization
    4. Data tables vs Lookup tables
    5. Primary and Foreign keys
    6. Working with tables relationship; creating, modifying, deleting
    7. Active vs Inactive relationships
    8. Relationship Cardinality
    9. Filter direction
    10. Hiding fields from client tools
    11. Defining hierarchies

    Section 4: Power Pivot
    1. Normal Pivot Table vs Power Pivot
    2. Introduction to Data Analysis Expressions (DAX)
    3. Creating Power Pivot
    4. Calculated columns
    5. Introduction to DAX Measures
    6. Creating Implicit and Explicit Measures
    7. Managing Measures
    8. Calculated Columns vs Measures

    Section 5: Common DAX Functions
    1. DAX syntax
    2. DAX operators
    3. Common DAX function categories
    4. Math & Stats functions
    5. Basic Logical functions
    6. Text functions
    7. Filter functions
    8. Iterator ("X") functions
    9. Basic Date & Time functions
    10. Time Intelligence functions (optional)
    Who should attend
    1. Excel users who want to learn more advanced data modeling & business intelligence tools
    2. Anyone looking to become a power Excel user and enhance their analytics skillset
    3. Company that have more than 1,048,576 rows of data (maximum rows in Excel worksheet) and want to go beyond the limits.

    Prerequisite:
    1. Must have Power Pivot installed (see IMPORTANT section above)
    2. Must have basic to intermediate skills on MS Excel
    3. Must know how to work with Excel functions
    4. Must already know how to use Excel Pivot Table. If not, you can check out my course on "MS Excel Pivot Table In-Depth" at CCISG

    Things to bring during the course:
    1. A laptop (preferable your personal laptop)
    2. Laptop power adaptor
    3. A USB mouse
    4. A USB thumb drive or hard-disk
    5. Software requirement: Microsoft Excel version 2019 and above including Excel 365
    Methodology
    This is a two-days hands-on course. The trainer will walk through the topic step-by-step. You will be provided with exercise files on every topic to effectively apply what have been taught. A short Q & A session will be available after each topic.
    Testimonial
    - Knowledgeable, able to answer questions and identify problems and thus resolve them
    Nagase Singapore, Pte Ltd

    - Practical tools that can be applied to data preparation
    - Trainer is friendly and knowledgeable
    Central Provident Fund Board

    - Very useful workshop. The knowledge learned can be applied in my work. The workbook is very detailed and useful.

    -  The trainer has provided a solution to my problems encountered in work.
    Alaster Leong's Profile
    Mr. Alaster Leong is an IT Trainer who possesses a strong passion in IT Training. He has more than 25 years of IT experience. He has been providing IT training in government, private institutions and organizations since 1992. He specializes in creative courses such as Adobe Photoshop, Adobe Dreamweaver, Adobe Flash, Adobe Acrobat, Access and Excel VBA, Microsoft Office Applications, etc. He is a certified Microsoft Office Specialist Expert (MOS) in Excel.
    Privacy Policy  |  Terms of Use
    Copyright © 2024 CCISG Pte Ltd  |  ACRA Reg No: 201207591D  |  GST Reg No: 201207591D