Financial Modeling

Day 1: Power BI Foundations and Data Preparation

Introduction to Power Query

Common Data Import Sources

• Working with CSV; Text; Excel Files
• Importing multiple files
• Importing data from google sheet

Working with Folders and Multiple Files

• Combining Data from Multiple Files

Working with Columns

• Name; Move; Split; Merge

Filtering and Sorting

• Using Auto-Filter
• Using Number, Text and Date Filters
• Removing Duplicate Values
• Filtering out Rows with Errors
• Sorting
• Grouping rows

Changing Values in a Table

• Replacing Values
• Transformations: – Text; Number; Date/Time
• Replacing Missing Values

Table Transformations

• Un-pivoting Columns to Rows
• Transposing a Table
• Creating Custom Columns

Loading Data

• Loading Data into a Worksheet
• Loading Data into the Excel Data Model
• Power Query and Table Relationships

Introduction of BI Desktop

• A First Power BI Desktop Dashboard
• The Data Load Process
• The Power BI Desktop Window
• Your First Visualizations
• Interactivity in Dashboards

Loading Data into Power BI Desktop

• The Power BI Desktop Query Editor
• Data from the Web
• Modifying Connections
• Refreshing Data from Databases

Dealing with Datasets

• Power BI Desktop Queries
• Query or Load
• Removing/Sorting/Filtering and Groupin Records

Data Transformation

• Power BI Desktop Query Editor
• Using the First Row As Headers
• Changing Data Type
• Replacing Values
• Transforming Column Contents
• Filling Down Empty Cells
• Managing Column and its Contents

Creating a Data Model

• The Power BI Desktop Data View Ribbons
• Managing Power BI Desktop Data
• Preparing Data for Dashboards
• Creating & Managing Relationships Between Tables

Extending the Data Model with Calculated Columns

• Types of Calculations
• Adding New Columns and Using Functions
• Concatenating Column Contents
• Simple Logic: The IF( ) Function

Adding Measures to the Data Model Using DAX

• Basic Aggregations in Measures
• Using Multiple Measures
• Calculating Percentages of Totals

Analyzing Data over Time

• Simple Date Calculations
• Adding Time Intelligence to a Data Model
• Creating and Applying a Date Table
• Calculating the Difference Between Two Dates
• Applying Time Intelligence
• Comparisons with Previous Time Periods
• Comparison with a Parallel Period in Time