3819 : Powerful Data Analysis with Power Query & Power Pivot in Excel
This course introduces Microsoft Excel’s Powerful Data analysis and Data modeling with the Power Analysis Tools, Power Query & Power Pivot
How Power Query and Power Pivot work together
Power Query and Power Pivot complement each other. Using Power Pivot and Power Query together can help you create reporting models that are easy to manage and maintain. Power Query (Get & Transform) provides fast, easy data gathering and shaping capabilities. Enables you to connect, combine, and refine data sources to meet your analysis needs. When you load your data in the Excel Data Model, you can continue enhancing it for analytics in Power Pivot.
In Power Pivot you can use the Data Model and a collection of tables to create relationships stored as part of the workbook. The Data Model integrates deeply with other features in Excel, such as tables and Pivot Tables, to provide a seamless analytics experience.
Use both tools to mold your data in Excel so you can explore and visualize it with Power Map, Power View, PivotTables, and Pivot Charts, and then interact with the resulting workbook in SharePoint, on Power BI sites in Office 365, and in the Power BI Microsoft Store app.
Duration :
Classroom |
Live Online |
|
Audience Profile
· Excel power users
· Finance and data professionals
· Data analysts
· Junior accountants
· Finance managers
Course Features
· Practice Workbooks
· Real Life Case Studies
· Certificate of Completion
· 3 Months Email Support
Excel Power Tools – The Future of Excel
· Top features of Power Pivot for Excel
· Tasks in Power Pivot or in Excel
· Power Query and Power Pivot work together
· M language vs Dax formula Language
· PivotTables vs Power PivotTables
Get & Transform Data with Power Query in Excel
· Power Query Introduction
· Import Data into Power Query
· Edit Queries and Refresh Data
· Data Transformation
· Conditional Column Τransformation
· Generate Index & Custom Columns
· Group & Aggregate Data
· Unpivot & Pivot Data
· Process Flat Data Using Modulo
· Merge and Append Data
· Advanced Editor M Lanquage
· Create Data Types
Powerful Data Analysis and Data Modeling in Excel
· Introduction to Data Models
· Add Tables to a Data Model with Power Query
· Introduction to Power Pivot
· Create Relationships between Tables in Power Pivot
· Relationship Rules
· Add existing, unrelated Data to a Data Model
· Use a Data Model to create a PivotTable or PivotChart
· Hierarchies in a Data Model
Calculations in Power Pivot
· Introduction to Data Analysis Expressions (DAX)
· DAX Calculated Columns
· DAX Measures
· DAX Functions & Formulas
· Combine Measures
· Time Intelligence Functions
· DAX Error Handling
Functionality in Power Pivot
· Power Pivot Calendar Table
· Named Sets and Perspectives
· KPI in Power Pivot for Dashboard and Report
· Publish to Power BI
· Solutions to Common Problems
No certification.
▪ MS Excel 2016/2019 /Office 365
▪ Experience with Excel Pivot Tables and simple functions is strongly recommended
| Location | Date(s) | Time (UTC+2) | Delivery Format | Language |
|---|---|---|---|---|
| Live Online* | 2,3,5-Δεκ | Δε, Τρ, Πε 17:30-20:45 | Instructor Led | Greek |
* Σύγχρονη εξ αποστάσεως εκπαίδευση με εισηγητή – Virtual Class
Last update 27/12/2023
