Training Details
Discover Business Intelligence in Excel!
In-Person / Remote?
Both In-Person and Remote options available
Level
Advanced
Prerequisites
A knowledge of Excel PivotTables.
Target Audience
Anyone looking to analyze and visualize data from multiple sources.
Objectives
By the end of this hands-on training, participants will be able to:
- Import data from multiple sources
- Transform data into actionable tables
- Create a report in a Power BI environment
- Filter data effectively
- Create a dashboard
- Share reports with others
Detailed Program
- Introduction to Power BI
- Overview of Power BI
- Power BI modules
- Getting Data
- Connecting to data from Power BI Desktop
- Cleaning and transforming data
- Handling irregularly formatted data
- Managing Queries
- Importing data from various sources
- Data transformation (removing duplicates, deleting records, filtering, splitting columns, etc.)
- Merging multiple files
- Modeling
- Data modeling and relationships
- Creating calculated columns
- Optimizing the data model
- Creating measures
- Dealing with time-based data
- Creating calculated tables
- Report Creation
- Building a simple visualization
- Combined charts
- Using slicers
- Utilizing maps
- Tables and matrices
- Scatter plots
- Advanced charts
- Gauges
- Color management
- Improving formatting
- Creating interactions between visualizations
- Handling missing data
- Managing visual hierarchies
- R visuals in Power BI
- Exploring Data
- Data exploration
- Creating and configuring a dashboard
- Asking questions in natural language (CORTANA)
- Creating custom Q&A (CORTANA)
- Sharing elements
- Publishing and Sharing
- Publishing and sharing
- Publishing reports from Power BI Desktop to Power BI service
- Printing and exporting dashboards
- Publishing and refreshing data
- Introduction to DAX
- Types of calculations
- DAX functions
- Variables in DAX
- Table relationships and DAX
- DAX tables and filtering
- Tips and Tricks
Duration
2 days
Quality Training’s Power Pivot Course
Offering various office-related courses that allow companies to explore new facets of the most comprehensive software on the market, Quality Training is here to assist you in your journey with Microsoft Excel. We’re here to help you navigate through the interface filled with features and options of all kinds, making you more productive than ever in your daily tasks. Our Excel courses are conducted in small groups and can range from a few hours to several days, depending on the topic you want to cover. Since Excel is such a comprehensive software, we can address various topics in separate courses, such as pivot table design, chart creation, basic formula and function assimilation, and much more.
What is PowerPivot Used For?
Among the many features and options present in Microsoft Excel, this training will focus on discovering PowerPivot. This module allows for powerful data analysis to be conceptualized into an intuitive dashboard. Primarily, this feature is for anyone wishing to better understand the origins of various sources and/or voluminous databases. Through this theoretical and practical training, participants can present technically detailed data to create creative dashboards for concrete and relevant data analysis. Note that some knowledge of Excel, especially pivot tables, is required to grasp this somewhat technical session that might be challenging for beginners. Aimed at a professional audience, you can take this training remotely, in person, or through e-learning.
Topics Covered in the Training
Spread over two full days, this PowerPivot-based Excel training will cover various areas for comprehensive learning. After a quick presentation of the module and its interface, you will learn to create a dashboard and import data using the PowerQuery add-in. Practical workshops throughout the training will help you master the tool, making data analysis easier by filtering using an SQL query assistant and creating pivot tables and charts based on the module. Let’s explore the main axes developed in your PowerPivot-based Excel training.
Connecting and Analyzing Data
The primary function of PowerPivot is to connect different datasets and analyze them. Over these two days of training, you will learn how to leverage different data sources (from Excel, the web, or other file sources like CSV or TXT), import them using the PowerQuery add-in, add calculated columns, and utilize the DAX formula language along with its key functions. This list is not exhaustive, as we are here to help you fully master the tool and address any potential questions.
Preparing, Sorting, and Filtering Data
Handling large volumes of data from both Excel and external sources, the module lets you import data into a single source workbook without creating multiple sheets. This facilitates manipulation, analysis, sorting, and visualization of data through pivot tables and/or charts.
Tables and Charts
A perfect complement to Microsoft Excel, PowerPivot allows you to prepare and merge various datasets, even from vastly different sources. The goal is to study these datasets together and intelligently link them, whether through a pivot table or a chart generated by the module. You can define relationships and hierarchies, using slicers in pivot tables.
SQL Query Assistant
When importing data from an external source into PowerPivot, you’ll need to select columns from each table. The SQL query assistant helps you filter the imported data, limiting the number of rows in your data model. This tool aids and guides you through the crucial filtering step.
DAX Language
DAX (Data Analysis Expressions) is a set of functions, operators, and constants that can be used in formulas or expressions. It calculates one or more values, helping you create new information from existing data. In PowerPivot, DAX is used when analyzing more complex data. For instance, when dealing with inventory data spread across multiple tables and data sources.
Functions
The functions and formulas in DAX are somewhat similar to those we know from Excel. Like in the spreadsheet software, they start with an equal sign followed by the function name or a specific expression. There are notable differences, though. DAX lets you use an associated value for context-based calculations or compare results across parallel periods using date ranges. In essence, DAX offers many possibilities that we can explore together.
Building and Sharing a Dashboard
One of the main features of PowerPivot is creating and sharing dynamic dashboards that display tables, pivot tables, and charts. This helps you manage analysis by tracking developments through various indicators. In these two days of training, you will learn to create a perspective that eases the manipulation of imported data while visualizing performance levels using KPIs.
Becoming an Excel Expert with Quality Training
Since its inception in 2008, Quality Training has offered genuine learning experiences to companies, training their teams in new office technologies. We cover a range of technical software that’s essential for your daily tasks, whether you’re a business leader or an employee. Among our various courses, the Microsoft Excel course is one of the most pertinent and important. The reason is simple. This software is used worldwide, across all industries, and its possibilities are limitless. As complex as it is fascinating to use, Excel is packed with features and modules we can explore together. Take the leap and join us for an Excel training, selecting the topic you want to delve into deeply! Data analysis, table and chart design, and exploration of customized modules – we are here to assist you!
