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
- 6 learning resources
- Course completion certificate
Course Content
Data Analysis Versus Data Science
Business Intelligence (BI) Story
Understanding the “Power Excel” Workflow || Install Power Pivot for Excel
Power Pivot and Power Query: Best Thing to Happen to Excel in 20 Years || Introduction to Power Query in Excel
Power Query User Interface
Data Analysis Versus Data Science
Business Intelligence (BI) Story
Understanding the “Power Excel” Workflow || Install Power Pivot for Excel
Power Pivot and Power Query: Best Thing to Happen to Excel in 20 Years || Introduction to Power Query in Excel
Power Query User Interface
Power Query: Import Data from Web || Fill Down || Replace Values || Data Type
Power Query: Live Connection from Website || Manage Columns || Reduce Rows
Power Query: Import Data from Text File || Split Columns || Grouping (The Magic of Power Query)
Power Query: Pivot Data
Power Query: Import Excel Table || Unpivot Data
Power Query: Load Connection Only || Pivot Table
Power Query: Import Data from Folder || Transform Data || File Information
Power Query: Join and Merge Tables || Left Outer Join Kind
Power Query: Thinking About How to Clean Complicated Data || Custom Column || Data Consolidation
Power Query: IF Function Including Nested IF
Power Query: Locale & Regional Settings to Fix Date Errors
Power Query: Using Column from Examples to Fix Date Errors
Power Query: Import Data and Manage 7 Million Record from Text Files
Introduction to Power Pivot
Database Concept Theory for Power Pivot
What is the DAX Function?
What is the Data Model?
Build Database from Scratch Using MS Access
Introduction to Relationships Between Tables
Relationships In-Depth Using MS Access
Understanding Queries
Data ("Fact") Tables Vs. Lookup ("Dimension") Tables || Build and Modify Data Model Table Relationships
Introducing Data Analysis Expressions (DAX) || DAX Function: Related (Joining Data) || Build DAX Calculated Column
DAX Function: SUM || Implicit Vs. Explicit Formulas || Build DAX Measure (Calculated Field)
Manage Data from Folder into Power Query and Power Pivot
Import More Tables from Other Source || Diagram View || Countrows
Build Reports from Data Model Using Pivot Table
Import and Manage Five Million Record from Folder
Import Dimension Tables to Fact Table into Power Query and Power Pivot
Full Project: Build More DAX Calculated Column and DAX Measure (Calculated Field): Part 1
Full Project: Build More DAX Calculated Column and DAX Measure (Calculated Field): Part 2
Full Project: Build Reports from Data Model Using Pivot Table with Group Dates: Part 3
Full Project: Refresh Reports When Source Data Updated: Part 4
Create Key Performance Indicators (KPIs) By Using New Measure in Power Pivot
Create Key Performance Indicators (KPIs) By Using Absolute Value
Course Wrap up
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.
Course requirements and prerequisites
This course requires the installation of Microsoft Excel. Prior experience with Microsoft Excel is preferable.
Completing the Business Data Analysis using Excel course, or having equivalent knowledge, is preferable.
Mentor
Ashraf Elsheikh
Founder & CEO Of Elite for Training and Web Designs
22,667 Learners
4 Courses