Power Pivot & Power Query Training in Mumbai
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
Note: Available in Excel 2010 & above.
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 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 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.
- 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
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.
- 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.
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
- 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
[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=2 write_review=true view_mode=slider nofollow_link=true lazy_load_img=true]