POWER QUERY TRAINER, EXCEL BUSINESS INTELEGENCE JAKARTA BANDUNG

inhousetrainer.net :

BUSINESS INTELEGENCE USING  POWER QUERY & EXCEL POWER PIVOT

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 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 QueryPower 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 shapingblending 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 — CALCULATEFILTERSUMX 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
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
Practice
Time Intelligence
·        
DAX Speed & Performance
Considerations
·        
DAX Best Practices
Wrapping Up
·        
Data Visualization Options in Excel
·        
Sneak Peek Microsoft Power BI
Preview

via Blogger https://ift.tt/3bblVyj
POWER QUERY TRAINER, EXCEL BUSINESS INTELEGENCE JAKARTA BANDUNG
Mr. Hery Purnama – Trainer – 081223344506