Data Science Using Microsoft Excel Business Intelligence (BI)

categories: Technology, Science & Productivity

MS Excel

Data Analysis

SDG 9: Industry, Innovation and Infrastructure

By the end of this course, you will be able to

What the difference between data analysis and data science is, what the workflow of both PowerPivot and Power Query is, and how to skillfully use each of them.
How to clean dirty data, duplicate columns, format dates, import data from a folder, transform data, join and merge tables, and fix date errors on Power Query.
What the DAX function is, what the data model is, how to build a database from scratch using MS Access, and how to create KPIs by using Absolute Value.

Course details

3 h 29 m/41 lessons
Last updated: 3/3/2021
5 learning resources
Course completion certificate

Course Content

Free lessons

Data Analysis Versus Data Science

3 Minutes

Business Intelligence (BI) Story

2 Minutes

Understanding the “Power Excel” Workflow || Install Power Pivot for Excel

1 Minutes

Power Pivot and Power Query: Best Thing to Happen to Excel in 20 Years || Introduction to Power Query in Excel

2 Minutes

learning resources

Power Query User Interface

1 Minutes

1. Data Visualizations and Dashboards Using Microsoft Power BI

Data Analysis Versus Data Science

3 Minutes

Business Intelligence (BI) Story

2 Minutes

Understanding the “Power Excel” Workflow || Install Power Pivot for Excel

1 Minutes

Power Pivot and Power Query: Best Thing to Happen to Excel in 20 Years || Introduction to Power Query in Excel

2 Minutes

learning resources

Power Query User Interface

1 Minutes

Power Query: Import Data from Web || Fill Down || Replace Values || Data Type

6 Minutes

learning resources

Power Query: Live Connection from Website || Manage Columns || Reduce Rows

5 Minutes

learning resources

Power Query: Import Data from Text File || Split Columns || Grouping (The Magic of Power Query)

10 Minutes

Power Query: Pivot Data

4 Minutes

10.

Power Query: Import Excel Table || Unpivot Data

5 Minutes

11.

Power Query: Load Connection Only || Pivot Table

5 Minutes

12.

Power Query: Import Data from Folder || Transform Data || File Information

3 Minutes

13.

Power Query: Join and Merge Tables || Left Outer Join Kind

6 Minutes

14.

Power Query: Thinking About How to Clean Complicated Data || Custom Column || Data Consolidation

6 Minutes

15.

Power Query: IF Function Including Nested IF

4 Minutes

16.

Power Query: Locale & Regional Settings to Fix Date Errors

3 Minutes

17.

Power Query: Using Column from Examples to Fix Date Errors

3 Minutes

18.

Power Query: Import Data and Manage 7 Million Record from Text Files

6 Minutes

19.

Introduction to Power Pivot

2 Minutes

20.

Database Concept Theory for Power Pivot

4 Minutes

21.

What is the DAX Function?

1 Minutes

22.

What is the Data Model?

2 Minutes

23.

Build Database from Scratch Using MS Access

9 Minutes

24.

Introduction to Relationships Between Tables

5 Minutes

25.

Relationships In-Depth Using MS Access

6 Minutes

26.

Understanding Queries

3 Minutes

27.

Data ("Fact") Tables Vs. Lookup ("Dimension") Tables || Build and Modify Data Model Table Relationships

8 Minutes

28.

Introducing Data Analysis Expressions (DAX) || DAX Function: Related (Joining Data) || Build DAX Calculated Column

5 Minutes

29.

DAX Function: SUM || Implicit Vs. Explicit Formulas || Build DAX Measure (Calculated Field)

4 Minutes

30.

Manage Data from Folder into Power Query and Power Pivot

6 Minutes

31.

Import More Tables from Other Source || Diagram View || Countrows

9 Minutes

32.

Build Reports from Data Model Using Pivot Table

6 Minutes

33.

Import and Manage Five Million Record from Folder

6 Minutes

34.

Import Dimension Tables to Fact Table into Power Query and Power Pivot

6 Minutes

35.

Full Project: Build More DAX Calculated Column and DAX Measure (Calculated Field): Part 1

7 Minutes

36.

Full Project: Build More DAX Calculated Column and DAX Measure (Calculated Field): Part 2

4 Minutes

37.

Full Project: Build Reports from Data Model Using Pivot Table with Group Dates: Part 3

4 Minutes

38.

Full Project: Refresh Reports When Source Data Updated: Part 4

5 Minutes

39.

Create Key Performance Indicators (KPIs) By Using New Measure in Power Pivot

5 Minutes

learning resources

40.

Create Key Performance Indicators (KPIs) By Using Absolute Value

3 Minutes

learning resources

41.

Course Wrap up

2 Minutes

About this course

We are now living in the age of big data. Data is constantly being collected, and an overwhelming amount of data exists. There is hence a growing need for people who can analyze these large amounts of data quickly. Fortunately, Excel has recently become a powerful tool for data science as it provides two extremely important programs that will help you analyze large amounts of data: PowerPivot and Power Query. In this course, you will understand how to enhance your data analysis capabilities by learning how to utilize all of these programs' features.