BUSINESS INTELEGENCE USING POWER QUERY AND EXCEL POWER PIVOT

BUSINESS INTELEGENCE USING  POWER QUERY & EXCEL POWER PIVOT

SYLLABUS TRAINING 

DURATIONS : 4 DAYS (ONLINE TRAINING 09.00 – 15.00)

What you'll learn

·         Get up & running with Excel's changing data modeling & business intelligence tools

·         Learn how to use Power Query, Power Pivot & DAX to absolutely revolutionize your workflow in Excel

·         Master unique tips, tools and case studies that you won't find in ANY other course, guaranteed

·         Explore fun, interactive, and highly effective lessons from a best-selling Excel instructor

·         Get LIFETIME access to project files, quizzes and exercises

·         Build pro-quality business intelligence solutions to blend and analyze data from multiple sources

Requirements

·         IMPORTANT: You need a version of Excel that is compatible with Power Pivot (Excel 2013/2016/2019 Standalone, Office 365, Enterprise E3/E5, Office Professional 2016, etc.)

·         This course is designed by Purnama Academy for PC users (Power Pivot is currently NOT available for Mac)

·         Experience with Excel PivotTables and formulas & functions is strongly recommended

COURSE DESCRIPTION:

This course introduces Microsoft Excel's powerful data modeling and business intelligence tools: Power Query, Power Pivot, and Data Analysis Expressions (DAX). If you're looking to become a power Excel user and absolutely supercharge your Excel analytics game, this course is the A-Z guide that you're looking for.

I'll introduce the "Power Excel" landscape, and explore what these Excel tools are all about and why they are changing the world of self-service business intelligence. Together, we'll walk through the Excel BI workflow, and build an entire Excel data model from scratch:

·         First we'll get hands-on with Power Query; a tool to extract, transform, and load data into Excel from flat files, folders, databases, API services and more. We'll practice shaping, blending and exploring our project files in Excel's query editor, and create completely automated loading procedures inside of Excel with only a few clicks.

·         From there we'll dive into Data Modeling , and cover the fundamentals of database design and normalization (including table relationships, cardinality, hierarchies and more). We'll take a tour through the Excel data model interface, introduce some best practices and pro tips, and then create our own relational database to analyze throughout the course.

·         Finally, we'll use Power Pivot and DAX to explore and analyze our Excel data model. Unlike traditional Excel Pivot Tables, Power Pivot allows you to analyze hundreds of millions of rows across multiple data tables (inside of Excel!), and create supercharged calculated fields using a formula language called Data Analysis Expressions (or "DAX" for short). We'll cover basic DAX syntax, then introduce some of the most powerful and commonly-used functions -- CALCULATE, FILTER, SUMX and more.

Who this course is for:

·         Excel users who want to learn advanced data modeling & business intelligence tools

·         Students looking for a comprehensive, engaging, and highly interactive approach to Excel training

·         Anyone looking to become an Excel POWER USER and supercharge their analytics skillset

·         Students looking to pursue a career in data analysis or business intelligence

Course content

Intro to “Power Excel"

·         Understanding the “Power Excel” Workflow

·         Power Query + Power Pivot "Best Thing to Happen to Excel in  Years"

·         When to use Power Query & Power Pivot

Connecting & Transforming Data with Power Query in Excel

·         Introduction

·         Getting to Know Power Query in Excel

·         Exploring Excel's Power Query Editor Preview

·         Power Query Data Loading Options

·         IMPORTANT Updating Locale Settings

·         Applying Basic Table Transformations with Power Query

·         Power Query Demo Text Tools

·         Power Query Demo Number & Value Tools

·         Power Query Demo Date & Time Tools

·         PRO TIP Creating a Rolling Calendar with Power Query

·         Power Query Demo Generating Index & Conditional Columns

·         Power Query Demo Grouping & Aggregating Records

·         Modifying Excel Workbook Queries

·         Merging Queries with Power Query

·         Appending Queries with Power Query

·         Power Query Demo Connecting to a Folder of Files

·         Excel Power Query Best Practices

Building Table Relationships with Excel's Data Model

·         Introduction

·         Meet Excel's "Data Model"

·         The Data Model Data vs. Diagram View

·         Principles of Database Normalization

·         Understanding Data Tables vs. Lookup Tables

·         Benefits of Relationships vs. Merged Tables

·         Creating Table Relationships in Excel's Data Model

·         Modifying Data Model Table Relationships

·         Managing Active vs. Inactive Table Relationships

·         Understanding Relationship Cardinality

·         Connecting Multiple Data Tables in the Data Model

·         Understanding Filter Flow

·         Hiding Fields from Excel Client Tools

·         Defining Hierarchies in a Data Model

·         Excel Data Model Best Practices

Analyzing Data with Power Pivot & DAX

·         Creating a "Power" Pivot Table

·         Power Pivots vs. “Normal” Pivots in Excel

Preview

Introducing Data Analysis Expressions (DAX)

·         Understanding DAX Calculated Columns

·         Understanding DAX Measures

·         Creating Implicit DAX Measures

·         Creating Explicit DAX Measures with AutoSum

·         Creating Explicit DAX Measures with Power Pivot

·         Understanding DAX Filter Context

·         Step-by-Step DAX Measure Calculation

·         RECAP Calculated Columns vs. DAX Measures

·         Excel Power Pivot & DAX Best Practices

Common DAX Formulas & Functions

·         Understanding DAX Formula Syntax & Operators

·         Common DAX Function Categories

·         DAX Demo Basic Math & Stats Functions

·         DAX Demo COUNT, COUNTA, DISTINCTCOUNT & COUNTROWS

·         HOMEWORK Math & Stats Functions

·         DAX Demo Logical Functions (IF/AND/OR)

·         DAX Demo SWITCH & SWITCH(TRUE)

·         DAX Demo Common Text Functions

·         Logical & Text Functions

·         DAX Demo CALCULATE

·         DAX Demo Adding Filter Context with FILTER (Part )

·         DAX Demo Adding Filter Context with FILTER (Part )

·         DAX Demo Removing Filter Context with ALL

·         DAX Demo Joining Data with RELATED

·         DAX Demo Iterating with SUMX

·         DAX Demo Iterating with RANKX

·         Practice Iterator ("X") Functions

·         DAX Demo Basic Date & Time Functions

DAX Demo Time Intelligence Formulas

Practice Time Intelligence

·         DAX Speed & Performance Considerations

·         DAX Best Practices

Wrapping Up

·         Data Visualization Options in Excel

·         Sneak Peek Microsoft Power BI

Categories: bandung, EXCEL POWER PIVOT, jadwal training, jakarta, kelas, online, POWER QUERY TRAINING, Silabus, training