[vc_row][vc_column][vc_tta_tabs style=”modern” color=”blue” spacing=”4″ active_section=”1″][vc_tta_section title=”Excel BI” tab_id=”1474870578842-9ce51984-c1c8″][vc_column_text]

What Is Excel BI?

Excel BI is a powerful, flexible tool for every analytics activity. It gives you broad data analytics and visualization capabilities.

Excel BI Consist Of:

  • Power Query
  • Power Pivot
  • DAX

Note: Available in Excel 2010 & above.

Power Pivot

Power Pivot is an Excel add-in which can used to perform powerful data analysis and create sophisticated data models. It can handle large volumes of data (millions of rows) from various sources and all of this within a single Excel file.

Power Pivot 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 PivotCharts, and further analyze the data so that you can make timely business decisions without requiring IT assistance.

 

Note: You DO NOT need any programming, Excel VBA coding or SQL knowledge to start using Power Query.

Power Query

Power Query is a tool that can be used for data discovery, reshaping the data and combining data ( Merge / Blend / Consolidate, etc.) coming from different sources.

Power Query can connect directly to Excel, Access, Text, CSV, Hadoop, Sales Force, Azure and many other different sources of data.

Power Query :

  • A tool that can extract / connect to data from almost any source.
  • Merge and shape data sourced to match your data analysis requirements.
  • Prepare your data for further analysis and modelling by tools like Power Pivot, etc.
  • Keeps sequential record of all the steps performed from source till final output.

You can review your automation steps later and easily modify it.

DAX

DAX stands for Data Analysis Expressions. DAX is a formula language and is a collection of functions, operators, and constants that can be used in a formula or expression to calculate and return one or more values. DAX is the formula language associated with the Data Model of Excel Power Pivot.

It is not a programming language, but is a formula language that allows the users to define custom calculations in calculated columns and calculated fields (also known as measures). DAX helps you create new information from the data that is already present in your Data Model. DAX formulas enable you to perform data modeling, data analysis, and use the results for reporting and decision making.

Audience:

  • People performing Data Analytics in Excel.
  • Business Intelligence Developers.
  • Specialists using Excel.
  • Business Analysts
  • Data Analysts
  • Financial Analysts using Excel
  • If you or your team needs to fetch data from different sources and transform it so that it can be used in Excel for further analysis, then this course will help you master Power Query features from scratch.
  • IT specialists who performs Extract Transform & Load (ETL) activities for business

Pre requisite:

Participants attending this training should be familiar with the Advanced Excel topics such as VlookUP, Pivot table, Match & Index, Power functions like CountIF, SUMIF, IF condition,etc.

If the mentioned topics are not familiar, we recommend a 2 Day’s Advance Excel Workshop.[/vc_column_text][/vc_tta_section][vc_tta_section title=”Objectives” tab_id=”1474870578992-554210bb-d230″]

Key Features: 

  • By the end of the course, you will master Power Pivot for Excel.
  • You would be able to perform various complex data cleansing activities using Power Query
  • You would be able to automate various task which would have taken long hours of work.
  • You will learn how to create models in Power Pivot, loading data from different data sources.
  • You will learn how to write DAX expressions performing calculations such as Ratio-to-Parent, Year-to-Date, Year-Over-Year and so on.
  • You will learn how to use advanced DAX functions and to create complex data models in Power Pivot.
  • You will learn how to solve typical business issues by applying predefined patterns in DAX.
[/vc_tta_section][vc_tta_section title=”Course Outline” tab_id=”1474873368711-c009fec8-a023″]

Excel BI Course Module 1: Introduction to Self-Service Business Intelligence

  • Self-Service BI
  • Power Pivot
  • Power Query

 

Excel BI Course Module 2: Loading Data

  • Loading Data in Power Pivot
  • Loading from SQL Tables
  • Loading from SQL Queries
  • Using Linked Tables
  • Loading from Excel
  • Loading from Text Files
  • Loading from the Clipboard
  • Loading from Reports

 

Excel BI Course Module 3: Data Modelling 

  • Introduction to Data Models
  • Normalization and Denormalization
  • Star Schemas:
  • Over Denormalization

 

Excel BI Course Module 4: DAX Language  

  • Introduction to DAX
  • Calculated Columns and Calculated Fields (Measures) 
  • DAX Error Handling
  • Overview of DAX Functions
  • RELATED and RELATEDTABLE functions
  • FILTER and ALL functions
  • CALCULATE function
  • Visual Totals and ALLSELECTED

 

Excel BI Course Module 5: Basic Time Intelligence

  • What is Time Intelligence:
  • Calendar Table:
  • Counting Working Days
  • Multiple Calendar Tables
  • Aggregations Over Time

 

Excel BI Course Module 6: Creating Reports and KPI

  • Using KPI in Power Pivot
  • Advanced Excel Features for Power Pivot

 

Excel BI Course Module 7: Advanced DAX Concepts

  • Important DAX Concepts
  • Filter Context
  • CALCULATE Row Context
  • Evaluation Contexts and Relationships 
  • Context Transition ALLSELECTED

 

Excel BI Course Module 8: Advanced DAX Functions

  • Advanced DAX Functions and Patterns
  • RANKX
  • KEEPFILTERS
  • Semi Additive Measures
  • Event in Progress 

 

Excel BI Course Module 9: Introduction to Power Query

  • Power Query Introduction

 

Excel BI Course Module 10: Import Data into Power Query

  • Import Data from Excel File
  • Import data from other files / folder

 

Excel BI Course Module 11: Loading and Refresh

  • Load data and refresh query when source data changes

 

Excel BI Course Module 12: Data Transformation

  • Column Transformation
  • Row Transformation

 

Excel BI Course Module 13: In built transformation in Power Query

  • Text Transformation
  • Numbers transformation
  • Date transformation
  • Conditional Column transformation

 

Excel BI Course Module 14: Consolidate and Append Data

  • Append data from Excel tables within same Excel file
  • Append / Duplicate / Reference multiple Queries
  • Import data from files in a folder
  • Import data from multiple excel files in a folder
  • List of files in folder
  • Changing file path for source data

 

Excel BI Course Module 15: Merge Queries / Multiple Joins in Power Query

  • Multiple JOINS in power query (Merge Queries)
  • Different Joins in Power Query
  • Cross Joins / Cartesian Product of Two tables

 

Excel BI Course Module 16: Building blocks for M language

  • Introduction to building blocks in M
  • Text functions in Power Query
  • Date functions in Power Query
  • Conditional Functions in Power Query

 

Excel BI Course Module 17: Case Studies

  • Self-Service BI
  • Power Pivot
  • Power Query

 

Excel BI Course Module 18: Power Query Objects

  • List as object and List functions in Power Query
  • Record as object and Record functions in Power Query
  • Table & other objects and Table functions in Power Query
  • Power Query Objects
[/vc_tta_section][vc_tta_section title=”Enquire About The Program” tab_id=”1475583015363-5f7aef3b-e07d”][vc_column_text]

Talk to the Trainer or Enquire about the Program

[contact-form-7 id=”35884″ title=”Corporate Enquiry”][/vc_column_text][/vc_tta_section][vc_tta_section title=”Reviews” tab_id=”1478675435062-952669b1-fdf6″][vc_column_text][google-reviews-pro place_name=’Corporate Training in Advanced Excel Courses & VBA Macros Courses in Mumbai’ place_id=ChIJBdnh4EbJ5zsRIKZnfZExwAo auto_load=true rating_snippet=true min_filter=5 write_review=true view_mode=slider nofollow_link=true lazy_load_img=true][/vc_column_text][/vc_tta_section][/vc_tta_tabs][/vc_column][/vc_row][vc_row][vc_column][vc_raw_html]JTNDJTIxLS0lMjBHbG9iYWwlMjBzaXRlJTIwdGFnJTIwJTI4Z3RhZy5qcyUyOSUyMC0lMjBHb29nbGUlMjBBZHMlM0ElMjA5NjI3OTQzMTglMjAtLSUzRSUwQSUzQ3NjcmlwdCUyMGFzeW5jJTIwc3JjJTNEJTIyaHR0cHMlM0ElMkYlMkZ3d3cuZ29vZ2xldGFnbWFuYWdlci5jb20lMkZndGFnJTJGanMlM0ZpZCUzREFXLTk2Mjc5NDMxOCUyMiUzRSUzQyUyRnNjcmlwdCUzRSUwQSUzQ3NjcmlwdCUzRSUwQSUyMCUyMHdpbmRvdy5kYXRhTGF5ZXIlMjAlM0QlMjB3aW5kb3cuZGF0YUxheWVyJTIwJTdDJTdDJTIwJTVCJTVEJTNCJTBBJTIwJTIwZnVuY3Rpb24lMjBndGFnJTI4JTI5JTdCZGF0YUxheWVyLnB1c2glMjhhcmd1bWVudHMlMjklM0IlN0QlMEElMjAlMjBndGFnJTI4JTI3anMlMjclMkMlMjBuZXclMjBEYXRlJTI4JTI5JTI5JTNCJTBBJTBBJTIwJTIwZ3RhZyUyOCUyN2NvbmZpZyUyNyUyQyUyMCUyN0FXLTk2Mjc5NDMxOCUyNyUyOSUzQiUwQSUzQyUyRnNjcmlwdCUzRQ==[/vc_raw_html][/vc_column][/vc_row]