Date Venue Category/City Fees (Inclusive Taxes)
24/10/2024 2:00 PM - 5:00 PM Online 5015
Posted on by

Introduction

Perform powerful data analysis and create sophisticated data models with Excel Power Pivot.

Power Query is a user-friendly business intelligence tool that does not require the user to learn any specific code.

Participants can mash up large volumes of data from various sources, perform information analysis rapidly, and share insights easily.

Course Content

Power Pivot

  • Import data into the Power Pivot Data Model
  • Import and edit data from Microsoft Access and Microsoft Excel
  • Build Relationships between Data Tables
  • Create Hierarchies for end-user filtering convenience
  • Shape the Data Model with Calculated Columns & Measures
  • Create DAX Expressions including: SUM(), RELATED(), RELATEDTABLE(), COUNT(), DISTINCTCOUNT(), MONTH(), DATE(), CALCULATE(), ALL()
  • Build custom DAX Expressions to drop into any pivot
  • Create Dashboard worthy reports with Slicers and Presentation tips
  • Download the step-by-step user guide with practice exercises and solutions
  • Import existing Excel data and Link Excel data into the Data Model
  • Create Calculated Columns and Measures in the Data Model
  • Create Measures in Excel
  • Create Calculated Columns with RELATED() & RELATEDTABLE() DAX Functions
  • Create Measures using the COUNT() & DISTINCTCOUNT DAX Functions
  • Create Measures using the CALCULATE() DAX Function to modify the Filter Context of Pivot Tables
  • Create static values for Ratio creation with CALCULATE() & ALL() DAX Functions
  • Create a Date Table (Calendar) in Excel and Relate to the Data Model for Time Intelligence functionality

Power Query

  • Data Transformation
    • Column Transformation
      • What are the different types of column transformation that can be performed in Power Query using Ribbon interface are explained here viz. Unpivot columns using Power Query, Transpose Columns, Merge or Split Text in columns, etc.
    • Row Transformation
      • Row transformation like promoting first row as header, remove duplicate rows, grouping rows which are equivalent to sumifs in excel are explained in detail in this Power Query Training video.
  • In built transformation in Power Query
    • Text Transformation
    • Numbers transformation
    • Date transformation
    • Conditional Column transformation
  • 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
    • Changing file path for source data
  • 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
  • Building blocks
    • Text functions in Power Query
    • Date functions in Power Query
    • Conditional Functions in Power Query