Mastering Data Analysis: Excel & Power BI Edition

What is Excel?

Understanding the Interface (Cells, Rows, Columns)

Creating and Saving a Workbook

Typing in Cells

copying cutting and pasting

Basic Formatting (Font, Color, Borders)

Sorting and Fitering

Filtering data

Using Tables

Module 4_FORMULAS AND FUNCTIONS (PART 1)

Addiction, Subtraction, Multiplication and subtraction

Using the sum & Average function

Basic Function Syntax

IF Statements

Nested If Statemnet

COUNT, COUNTA, COUNTIF, SUMIF

Introduction to Cell Referencing

Creating Basic Charts (Bar, Line, Pie)

Formatting Charts

What Is a PivotTable

creating and using a basic pivot table

Using a PivotChart (VISUALIZATION)

Introduction to VLOOKUP

Introduction to HLOOKUP

Conditional Formatting

QUIZ

Introduction

Part 1 Dataset link

PowerBi download and installation

power bi interface

Data transformation with power query

connecting to some data source

combining data with append queries

combining data with Merge queries

Transposing data

Pivot columns

Unpivot columns

Group By

Other transformations

Errors

New columns from Text, Number and Date Columns

Duplicate and index columns

Adding conditional column

Custom columns

columns from example

Unclean data 1

Unclean data 2

Unclean data 3

Unclean data 4

Part 2 Dataset link

Introduction to Data Viz

Importing and cleaning data

Data modeling

Categorize Data

Change and Create Additional visual

Chart Formatting

Visual field buckets

Legends and small multiples

Tooltips and drill down

Working with shapes and text boxes

Displaying trends with line charts

Visualizing rank changes with Ribbon Chart

Using Tables and Matrix tables

Using Slicers

Creating Top N using the filter panes

Creating Bottom N using the filter panes

Using maps

Publish and export to PDF

Introduction to DAX

Calculated column

Calculated column

Introduction to Measures

introduction to Row level Security

Using IF functions

Using SWITCH functions

Using RELATED funtions to fetch columns from other tables

ALL and ALL EXCEPT functions

Introduction to CALCULATE

Row Context and Filter context

CALCULATE in measures

Calculate with multiple column filters

Using the IN and OR operator

Time Intelligence in DAX

CALENDARAUTO function

YEAR or FORMAT function

Creating and sorting month column with FORMAT and MONTH function

Calculating previous month values with PREVIOUS MONTH function

Calculating year to date values with TOTAL YTD

Calculating values for the same period last year

QUIZ