virtual classroom
Contact
Contact
virtual classroom cart
Show training

Overview of the most important tools in PowerPivot, Power Query & Power Map 2019 Add-ins

training code: EXPBI / ENG DL 3d / EN
training assurance
For more information, please contact the sales department. For more information, please contact the sales department.
2,400.00 PLN 2,952.00 PLN with TAX

Training dedicated to all persons whose task is to develop data from various sources for further analysis and reporting. Thanks to the tools available in Microsoft Excel, it is possible to improve and automate these tasks without the need to learn VBA. We especially recommend it to employees who prepare multidimensional analyses and reports.

Teaches independent creation of business analyses and reports based on Microsoft Excel tools and Power BI add-ons. Explains how to retrieve information into a data model using Power Query, aggregate and combine in the analytical model in Power Pivot, and then show in the form of interactive reports created with Power Map.

  • Knowledge of the Windows environment
  • MS Excel – An overview of advanced tools

Has the form of computer workshops combined with the explanation of the key theoretical issues of working with Big Data on the Microsoft Excel platform.

Participants solve typical tasks met by the people who collect and analyse large amounts of data in order to prepare reports and analyses on their own.

  • Training: English
  • Materials: English

POWER PIVOT

  1. Introduction
    • What is PowerPivot?
    • The main benefits of using PowerPivot to analyse data in Excel
    • PowerPivot terminology and multidimensional analysis
    • Availability of PowerPivot
  2. Creating data models
    • Importing data to a model from multiple sources
      • From the active workbook
      • From other workbooks
      • From txt, csv files
      • From databases
    • Creating relationships
  3. Improving models using DAX (Data Analysis eXpressions language)
    • Creating calculated columns
      • Necessary model adjustments using the RELATED function
      • Creating calculated measures
      • Creating calculated dimension elements and hierarchies
    • Creating calculated fields
      • Classified
      • Transparent
    • Creating key performance indicators (KPIs)
  4. Reporting from PowerPivot models
    • Tables and pivot charts
      • The rules for proper use of the pivot table
      • Differences in functionality of the PivotTable based on PowerPivot compared to the standard PivotTable
      • Slicers
      • Timeline
    • Downloading tables from the PowerPivot model

POWER QUERY

  1. Introduction
    • What is Power Query?
    • Installing the Power Query and discussing its interface
    • Availability of Power Query
  2. Acquisition of data – import from external sources
    • Files (Excel, CSV)
    • Folders – creating incremental data models
    • Relational databases (MS Access)
    • Searches on Wikipedia
    • Web sources (Facebook)
  3. Operations on data in a graphical view
    • List of queries
    • List of operations
    • Data levels – navigator
    • Tools available from the ribbon:
      • Operations on rows / columns
      • Filtering and sorting
      • Changing the data type
      • Automatic filling in empty fields, changing the value of selected fields
      • Separation and connection
      • Records, lists, tables
      • Grouping and aggregation
      • Unpivot
      • Date / time, number and text transformations
      • Combining data (adding records, combining tables)
    • Operations on data using the M language
      • Syntax
      • Basic built-in functions
      • Variables, blocks, user functions
    • Automation of data import
      •     o From WWW
      •     o From website services
      •     o From files

POWER MAP

  1. Introduction
    • What is Power Map?
    • Availability of the Power Map
    • Get acquainted with the Power Map
  2. Preparing the data needed for the presentation in the Power Map
  3. Geocoding in Power Map
  4. Presentation of data using various types of visualization on the map:
    • The use of cumulative – column visualization
    • Column – grouped visualization
    • Creating a bubble visualization
    • Contour (including creating "heat islands")
    • Regional visualization
  5. Work with many layers
  6. Giving dynamics by adding many "scenes" and transition effects
  7. Tuning settings in graphs and layers
  8. Visualizations on dynamic maps – tracking of variable data over time
  9. Exporting the sequence of scenes to a video file