Goals
- Import and manage source data external to Excel
- Master the use of Power Query / Power Pivot
- Bring a broader perspective of the use of Excel functionality
- Use complex functions, form controls, etc.
Program
The role of Power Query
Complementary tools to Power Query
Excel and csv sources
Importing an Access database
Data transformation
Splitting a column
modifying data types and transposing
Refreshing data relative to the source
Inserting or deleting rows and / or columns
Aggregating data in a column
Using the Filter, sorting and grouping of lines
Create a parameterized function in M language
Merge and add
« Uncross » lists of data
Workshop: importing, transforming and combining data from external sources
Understanding PowerPivot as a Tool
Benefits and Features
Different Versions of PowerPivot
Data to Use with PowerPivot
Description of the PowerPivot Tab in Excel
Using the PowerPivot Manager
Configuring the Import of External Data
Managing the Data Imported from the PowerPivot interface
Creation of pivot tables connected to the PowerPivot
Handling of a pivot table
Use of a pivot table under the table tool
Management of the pivot table options
Using sorting and filtering
Using segments for filters
Using calculations in the PivotTable
Setting up measures (functions, ratio, spreads, progress, charts)
Using grouping and defining value slices
Lab: Generate Pivot Tables with PowerPivot from External Sources
Loading PowerPivot tables from Excel
Access to relational databases: SQL Server, Access, ODBC etc.
Managing connections and updates
Using the Diagram view
Creating relationships between tables
Managing PowerPivot settings on multi tables
Designing the display and creating perspectives
Managing multi table data from PowerPivot
DAX functions, Excel functions and calculation in the pivot table
Design calculated fields
Implement intelligent date management
Formatting data
Comparison of two workbooks
Analysis of a workbook (Structure, formula, cells, ranges, warning)
Display of links between workbooks, worksheets and cells
Clean up excess formatting to decrease file size and increase their execution speed
Password management
Introducing the tool
Converting a table to a graph
Inserting new views
Choosing a theme
Presenting data with mosaics
Creating multi-view reports
Loading data from heterogeneous sources and organizing them among themselves
Presentation of Power Map
Modifying the appearance of the map
Adding a scene
Exploitation of geographic and temporal data
Exporting the Power Map map to a video or to another application
Workshop: Presentation of data geographically
Understanding the different Power BI modules
Acquiring data with Power Query (CSV, Excel, Access)
Using Power Pivot for connecting to data and
Creating relationships
Using Power View for adding visuals
Using PowerMap for a cartographic presentation
Publication and distribution of reports
Duration
5 days
Price
£ 2495
Audience
Seasoned Excel Users, Analysts, Decision Makers
Prerequisites
Have followed the Excel Advanced training or have equivalent concepts
Reference
BUR100247-F
Custom number format
Management of hours and their formats
Custom lists
Grouped histogram combined with stacked histogram
Using the shifted function in a graph
Enriching a graph with control buttons to make it more dynamic
Nesting of functions (index function with
Equiv, Shift function, Indirect …) Matrix formulas (Transpose, Sumprod, If in matrix …)
Complex nesting of date functions
Other examples of nesting of functions for complex needs
Sorting with a specific order
Sorting with conditional formatting
Advanced filter with specific criteria
Cascading drop-down menu
Validation rules with formulas (no duplicate, Uppercase, etc.)
Specific validation options
Customization of icon sets, color shades, etc.
Use of functions under conditions
Target value
Solver
Scenario management
Button, Drop-down menu, checkbox…
Parameters specific to the controls
Binding of controls with calculation formulas
Workshop: setting up a form
Sessions
Contact us for more informations about session date