BI Reporting - Power BI, Power Pivot and Power Query with MS-SQL in Microsoft Excel.

Course for Advanced Excel users, Analysts & Data Professionals

  • (745)

It is a 25 hour program, to super charge your model building, analytical and advanced reporting skills. This course will teach you how to work with huge data sets by querying them from Ms-Access & Ms-SQL, Transform your data using Power Query, Create Advanced Calculations using DAX Functions, and publish reports over the web with Power BI or build data models using Power Pivot.

Talk to the Trainer

Know your current Excel skills through a quick pre­assessment, discuss your requirements (if any) with the Trainer and, feel your comfort level before reserving a spot.

Course Fee

$699
  • Course Duration :
  • 25 Hours (8 Sessions, each of 3 Hours)
  • Pre-requisite :
  • Extreme comfort on Pivot Tables, Sum-if & Look-ups
  • Mode of Training:
  • Virtual Classroom (Instructor-Led)

What is Power BI?

  1. It's a business analytics service available as desktop and Cloud-based solutions
  2. Enables anyone to visualize and analyze data with greater speed and efficiency
  3. Offers easy­to­use dashboards, interactive reports and captivating visualizations
  4. Allows natural language querying of data models
  5. A cloud­based site where users can upload and share the created content with others
  6. It is available as a freemium service

What is PowerPivot?

  1. It’s a free Add­in available in Excel 2010 and Office Professional Pro Plus versions
  2. A way to handle massive volumes of data in Excel
  3. A way to add any formulas to pivot, and it adjusts!
  4. A way to turn workbooks into self­maintaining web applications
  5. A much more agile and cost effective approach to Business Intelligence
  6. Something that turns workbooks into industrial strength Apps

How is Power BI, Power Pivot & Power Query related?

Power Query and Power Pivot complement each other. Power Query is the recommended experience for discovering, connecting to, and importing data. Power Pivot is great for modeling the data you’ve imported. Use both to mold your data in Excel so you can explore and visualize it with Power BI, Power View, PivotTables, and Pivot Charts, and then use Power BI to build cloud-based BI Reports.

What is Power Query?

  1. One of the Excel Add-ins offered as part of Microsoft Power BI self-service solution
  2. Used for data discovery, reshaping and combining the data from different sources
  3. Has an intuitive and interactive user interface
  4. Supports query on new data sources like Facebook
  5. Allows you to pull data into power pivot as refreshable connections
  6. Performs data cleansing operations and create custom views over dat

Few Real­world Use Cases of Power BI Reporting

Get a full picture across data sources in seconds. Click to drill down

Course Content - Overview

Introduction to PowerBI Building Blocks of Power BI
  1. Visualizations
  2. Datasets
  3. Reports
  4. Dashboards
  5. Tiles
Getting Comfortable With Power BI Environment
  1. Connect to Data Sources in Power BI Desktop
  2. Cleaning Irregularly Formatted Date and Transform Your Data With the Query Editor
  3. Create a report in Power BI Desktop
  4. Publish the report in the Power BI service
Data Modelling with Power BI
  1. Fundamentals of Modelling
  2. How to Manage Your Data Relationships
  3. Create Calculated Columns
  4. Optimizing Data Models for Better Visuals
  5. Create measures and work with time-based functions
  6. Create Calculated Tables
  7. Explore Time-Based Data
Visualizations
  1. Create and Customize Simple Visualizations
  2. How to Use Combination Charts
  3. Using slicers
  4. Creating Map Visualizations
  5. Creating Tables and Matrixes
  6. Creating Scatter Charts
  7. Creating Waterfall and Funnel Charts
  8. Using Gauges and Single Number Cards
  9. Charting Options including Formatting with Colors, Shapes, Text Boxes, Images, etc.
  10. Creating Interactions Between Visualizations
  11. Visual hierarchies and drill-down behaviour
Exploring Data
  1. Exploring Data
  2. Use Quick Insights in the Power BI service
  3. Create and Configure A Dashboard
  4. Ask Questions of Your Data With Natural Language
  5. Share Dashboards with your organization
  6. Display Visuals and Tiles Full-Screen
Power BI and Excel
  1. Power BI and Excel
  2. Import an Excel table into Power BI
  3. Import Excel files with data models and Power View sheets
  4. Connect OneDrive for Business to Power BI
  5. Excel data in Power BI summary
Publishing and Sharing
  1. Publishing and Sharing
  2. Publish Reports from Power BI Desktop to the Power BI Service
  3. Print and export Power BI dashboards
  4. Manually Republish and Refresh Your Data
  5. Introducing Power BI Mobile
  6. Create a group in Power BI
  7. Create a content pack in Power BI
  8. Connect to a content pack from a group in Power BI
  9. Edit a content pack in Power BI
  10. Integrate OneDrive for Business with Power BI
  11. Publish a report to the web from Power BI
Introduction to PowerPivot
  1. Limitation of Excel Functions
  2. Limitation of Excel PivotTable
  3. Why PowerPivot?
  4. PowerPivot Features - Overview
PowerPivot Environment
  1. Opening PowerPivot Environment
  2. Understanding External Data Section
  3. Understanding Formatting
  4. PowerPivot Views
  5. Understanding Measures
Taking Data into PowerPivot
  1. From Excel
  2. From MS Access
  3. From Ms-SQL
  4. From Custom Query
  5. From Text Files
  6. From Other Sources
Calculated Columns
  1. Calculated Columns
  2. Entering Formulas
  3. Using AutoComplete Feature
  4. Renaming Columns
  5. Understanding Tables
Creating Measures
  1. Creating Simple DAX Measures
  2. Explicit Measure Vs Implicit Measure
  3. Referencing Measures in Other Measures
  4. Formatting Measures
Manage Data Relationships Working with Multiple Tables Disconnected Tables Creating custom calendars using ADVANCED FILTER() Advanced calculated columns
  1. Understanding Relationship Concept
  2. JOIN TABLES
  3. LEFT JOIN TABLES
  4. RIGHT JOIN TABLES
  5. Using UNION QUERIES
Data visualization Using
  1. Power View
  2. Charts, Score Cards and Dashboards
  3. Slicers
  4. Map Visualizations
  5. Data Binding and Formatting
Creating Advanced Dashboards with PowerPivot
Introduction to Microsoft Power Query for Excel Import data from external data sources Import with Standard Connectors
  1. Connect to a Text or CSV file
  2. Connect to a Web page
  3. Connect to an Excel Table/Range
Import Data from a File
  1. Connect to an Excel workbook
  2. Connect to a Text or CSV file
  3. Connect to an XML file
  4. Connect to a JSON file
Import Data from a Database
  1. Connect to a SQL Server database
  2. Connect to an Access database
  3. Connect to a MySQL database
Import Data from Online Services
  1. Connect to Salesforce Objects
  2. Connect to a Sharepoint Online List
  3. Connect to Facebook
Import Data from Other Sources
  1. Connect to an Excel Table or Range
  2. Connect to a Web page
Shape data from Multiple Data Sources
  1. Shape or transform a query
  2. Refresh a query
  3. Combine data from multiple data sources
  4. Filter a table
  5. Sort a table
  6. Group rows in a table
  7. Expand a column containing an associated table
  8. Aggregate data from a column
  9. Insert a custom column into a table
  10. Combine multiple queries
  11. Merge columns
  12. Remove columns
  13. Remove rows with errors
  14. Promote a row to column headers
  15. Split a column of text
  16. Insert a query to the worksheet
Performing Calculations using DAX Functions
  1. Table-Valued Functions
  2. Date and Time Functions
  3. Filter Functions
  4. Information Functions
  5. Logical Functions
  6. Mathematical and Trigonometric Functions
  7. Statistical Functions
  8. Text Functions
  9. Time Intelligence Functions
Creating Advanced DAX Measures with Advanced DAX Functions
  1. Calculate()
  2. All()
  3. Filter()
  4. IF()
  5. Switch()
  6. SumX()
Introduction to MS-SQL
  1. Creating a Database
  2. Understanding Tables and Creating Tables
  3. Inserting, Updating and Deleting Data
  4. Querying Data
  5. Filtering Data
  6. Grouping Data
  7. Ordering Data
  8. Column Aliases
  9. Table Aliases
UNDERSTANDING TRANSACT-SQL DATA TYPES
  1. Dates
  2. Strings
  3. Numeric
  4. BLOBs
  5. Bits
  6. UNIQUEIDENTIFIER
  7. Cursor Variables
  8. Timestamps
DDL INSIGHTS
  1. CREATE TABLE
  2. Dropping Objects
  3. CREATE INDEX
  4. TEMPORARY OBJECTS
  5. Object Naming and Dependencies
DML INSIGHTS
  1. INSERT
  2. UPDATE
  3. DELETE
  4. Detecting DML Errors
SELECT STATEMENTS
  1. Simple SELECTs
  2. Calculated and Derived Fields
  3. SELECT TOP / BOTTOM Records
  4. Derived Tables
  5. Joins
  6. Predicates
  7. Subqueries
  8. Aggregate Functions
  9. GROUP BY and HAVING
  10. UNION
  11. ORDER BY
WORKING WITH VIEWS
  1. ANSI SQL Schema VIEWs
  2. Getting a VIEW's Source Code
  3. Updatable VIEWs
  4. Derived Tables
  5. Dynamic VIEWs
  6. Partitioning Data Using Views
  7. View Restrictions
STORED PROCEDURES
  1. Stored Procedure Advantages
  2. Internals
  3. Creating Stored Procedures, Executing Stored Procedures
  4. Environmental Concerns
  5. Parameters for Procedures
  6. Important Automatic Variables
  7. Flow Control Language
  8. Errors
  9. Nesting
  10. Autostart Procedures
TRIGGERS
  1. AFTER Triggers
  2. The inserted and deleted Special Tables
  3. Identifying the Number of Affected Rows
  4. Identifying the Type of Trigger
  5. Not Firing Triggers for Specific Statements 152
  6. Nesting and Recursion
  7. UPDATE and COLUMNS_UPDATED
  8. INSTEAD OF Triggers
  9. Per-Row Triggers
  10. Used with Views
  11. Automatic Handling of Sequences
  12. Database-Level Triggers
  13. Server-Level Triggers
USER-DEFINED FUNCTIONS
  1. Scalar UDFs
  2. Table-Valued UDFs
  3. Per-Row UDFs
STATISTICAL FUNCTIONS
  1. CASE
  2. Efficiency Concerns
  3. Variance and Standard Deviation
  4. Medians
  5. Returning the Top n Rows
  6. Rankings
  7. Modes
  8. Cumulative and Sliding Aggregates
WORKING WITH DATE AND TIME
  1. Date and Time Data Types
  2. Date and Time Manipulation
  3. Date and Time Functions
  4. Literals
  5. Identifying Weekday
  6. Handling Date-only or Time-only Data Prior to SQL Server 2008
  7. Examples of Date and Time Calculations
  8. Rounding Issues
  9. Date- and Time-Related Querying Problems
  10. Age Problems
  11. Overlaps
  12. Grouping by the Week
  13. Working Days
  14. Generating a Series of Dates
ARRAYS
  1. Arrays as Big Strings
  2. Arrays as Tables
SETS
  1. Unions
  2. Differences
  3. Intersections
  4. Subsets
HIERARCHIES
  1. Simple Hierarchies
  2. Multilevel Hierarchies
  3. Indented lists
TRANSACTIONS
  1. Transactions Defined
  2. How SQL Server Transactions Work
  3. Types of Transactions
  4. Avoiding Transactions Altogether
  5. Automatic Transaction Management
  6. Transaction Isolation Levels
  7. Transaction Commands and Syntax
  8. Debugging Transactions
  9. Optimizing Transactional Code

Why with MS-SQL?

1. It complements Power Pivots and Power BI better than any other DB.

2. When business scenarios exceed the capabilities of Power BI and Power Pivots, data shaping can be done at DB (MS-SQL) level enabling you to surpass any complex reporting situations.

3. High performance due to its (MS-SQL) inherent capabilities of performing row-wise and cross-row business calculations in fraction of seconds, which, otherwise shall take, minutes together when executed only with Power Pivots or Power BI.

Training Schedule

OR

About the Trainer

Mr. Sami - MCT, MOS, MEE

Mr. Sami, Microsoft Certified Trainer, with his qualifications in Finance and IT brings in 14 years of Industry experience. He has successfully trained 5000+ professionals by now, and the counting is still on.

He has undertaken the renowned assignments with IRS, The World Bank, Tata Chemicals, Buckman Laboratories, Standard Chartered, ING Barings and much more. His nature of going that Extra Mile has got him the startling popularity amongst the Excelgoodies prominent clients.

Contact Us

Excelgoodies, Inc.

5205, Prospect Road, San Jose, 95129,

Mr. Paul Melton

Tel: (210) 591 8309

inquiry_chicago@excelgoodies.com

Connect us on

Learner stories around the world!