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

Course for Advanced Excel users, Analysts & Data Professionals

  • (754)

It is a 20 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 :
  • 20 Hours(8 Sessions, each of 2.5 Hours)
  • Pre-requisite :
  • Extreme comfort on Pivot Tables, Sum-if & Look-ups
  • Mode of Training:
  • Virtual Classroom (Instructor-Led)
Training 5 or more people?

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 Powerful data analysis and data modeling tool available in Excel as part of Office 365 Subscription.
  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

Power BI

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

Power Pivot

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

Power Query

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

DAX Functions

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()

MS-SQL

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.
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

System Requirements for Power BI Training

  1. Power BI Desktop (Free & Downloadable at https://powerbi.microsoft.com/en-us/get-started/
  2. PowerPivot - Available with Office 365 subscriptions that include desktop versions of Excel for Windows. Also available with certain onetime office purchases. Refer https://support.office.com/en-us/article/where-is-power-pivot-aa64e217-4b6e-410b-8337-20b87e1c2a4b for more information.
  3. Ms-Access
  4. Ms-SQL Server Express Edition (Free & Downloadable at https://www.microsoft.com/en-us/sql-server/sql-server-editions-express If you have it already installed, kindly ensure you have rights to create and modify new databases.
  1. Note: We can provide you 60 days access to Office 365 with Excel Powerpivot and Ms-Access for the purpose of your training. However, you should have administrative rights and permissions to install the same on your computer

Training Schedule

Loading Schedule..

OR

About the Trainer

Mr. Sami - MCT, MCSA, 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 8000+ 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 Consulting, Inc.

739 Bryant Street, San Francisco, CA 94107

Mr. Paul Melton

Tel: (210) 209-9222

inquiry_illinois@excelgoodies.com

Connect us on

Learner stories around the world!

Scott A. Reifsynder

Chief Financial Officer

Nexterus

This class was extremely helpful in my knowledge and growth in these database, analysis and reporting tools. I really enjoyed the hands-on approach and got lots of “goodies” out of all the classes. Your approach, repetitiveness and patience is much appreciated and sincerely helped ...

Read More >>