Excel for Finance

Excel for Finance

0%

1. Introduction to Power Query

Prepare to discover the fundamentals of Power Query, including navigating the Get Data tab, establishing connections with databases and Excel workbooks, and effectively merging queries to streamline your data processing tasks.

Course Introduction

Understanding Power Query in Excel

Connecting to the Excel Workbook

Connecting to the Google Sheets

2. Advanced Data Manipulation with Power Query

You will dive deeper into Power Query's capabilities by connecting and integrating data from CSV files and folders. This section will guide you through adding custom columns, merging data sources, handling errors gracefully, and efficiently grouping and unpivoting columns to prepare data for advanced analysis.

Seamless CSV Integration

Connecting to a Folder

Connecting Database

Connecting to the Web

Error Handling in Power Query

3. Power Pivot Handling

The section covers advanced data preparation, relationship building, and calculation methods using Power Pivot.

Loading Data to the Data Model

Creating Relationships

Calculating Measures

Additional DAX Calculations

4. Dashboard Automation

Delve into creating a highly automated and interactive dashboard from start to finish. We explore advanced features such as pivot charts, KPI integration, and slicers to enhance functionality and user interaction.

Getting Started with the Dashboard

Dashboard Development

Integrating KPIs

Integrating Slicers

5. Developing an Interactive Report

Discover advanced functions to create an interactive and automated report for calculating key financial metrics on a monthly basis.

Finalizing Report

Power BI Overview

Course Content

Excel for Finance

Excel for Finance

1. Introduction to Power Query

Course Introduction Understanding Power Query in Excel Get Data Connecting to the Excel Workbook Connecting to the Google Sheets

2. Advanced Data Manipulation with Power Query

Seamless CSV Integration Connecting to a Folder Connecting Database Connecting to the Web Error Handling in Power Query

3. Power Pivot Handling

Loading Data to the Data Model Creating Relationships Calculating Measures Additional DAX Calculations

4. Dashboard Automation

Getting Started with the Dashboard Dashboard Development Integrating KPIs Integrating Slicers

5. Developing an Interactive Report

Dropdown List Excel Formulas Finalizing Report Power BI Overview

Calculating Measures

Hello, everyone! 🎉 Today, we're diving into the world of DAX, a specialized language for Power Pivot that operates on whole columns instead of individual cells, as in Excel Workbooks. Thanks to the relationships we established in the Data Model in our previous session, we can now perform calculations across columns from different tables.

Measures are calculations used in data analysis that are computed using aggregate functions (SUM, COUNT, AVERAGE, MIN, MAX) or with DAX language for more advanced computations. Measures are calculated in the special 'Calculation Area' of the Power Pivot window.

Measures can be used in Pivot Tables and Pivot Charts just like columns, which we will apply in our dashboard.

Here's a step-by-step guide.

In this section, we learned how to add data to Data Models and create relationships between fact and dimension tables. We also tried creating a calculated column and explored measures using basic DAX formulas.

1. What is Power Pivot primarily used for in Excel?

2. Which of the following is NOT a feature of Power Pivot?

Everything was clear?

Thanks for your feedback!

Section 3. Chapter 3

some-alt