Excel Power Pivot & Power Query Training in Mumbai
Duration: Power Pivot & DAX ( 3 Days ) Power Query ( 3 Days )
How Do We Conduct Online Corporate Training ?
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.
What is Power Pivot & Power Query in Excel ?
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.
Why should you invest in DAX ?
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.
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.
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
I did online training for Power Query & DAX from Compufield. Our trainer Mr. Faiyaz is a thorough professional with complete knowledge of the subject, and he makes sure everyone has understood the concept before starting any new topic.
Owais Qureshi
We had a training in HRIPL for power BI by Mr. Faiyaz Compufield. It was a great learning in power query, DAX and now we know how to talk to data. The trainer Faiyaz has a very good convincing and training skills and use examples as per your level. He understand the issues and give personal focus to each students. thanks for such a wonderful training.. will have more..
Vivek Singh
Attended Corporate Training for Power Bi & DAX by Mr. Faiyyaz. Excellent session, comprehensively delivered from the very basic concepts to the most advanced ones passionately while clearing all queries on the fly. Would highly recommend the session.
Haroon Ahmed
Amazing training. I was able to prepare excel dashboards for my organization with their teachings and methods. Even my doubts were solved. Special thanks to Sabiha ma'am and Shaziya ma'am for their training.
Harsh Malhotra
I am a student of Xavier Institute of Communications and I undertook a course on Microsoft Excel by Faiyaz Khairaz sir and it was super interactive and fun! The content is very relevant and the teaching style made it easy to comprehend. Would recommend to anyone looking to grab similar skills!
Sonaali Malhotra
have done the online learning from Faiyaz on powerbi and power queries and I found his training extremely useful. Faiyaz explains the topic really well and he follows the problem solving method when teaching. This method is extremely effective when learning, remembering and applying the technique in the real world working environment. This has already made a lot of my routine tasks automated.
Amit Thakrar
