AulaGEO > Office Productivity > Microsoft > Excel Power Query, Power Pivot, Power Map & DAX Masterclass
4.53 out of 5
4.53
974 reviews on Udemy

Excel Power Query, Power Pivot, Power Map & DAX Masterclass

Learn Excel Power Query, PowerPivot, DAX, Power Map & Power BI Tools. Build Excel Data Analyst Models with Excel 2019
Instructor:
Andreas Exadaktylos
6,192 students enrolled
English [Auto]
Learn in depth Power Query, PowerPivot, Power View and Power Map tools
Create business intelligence reports and dashboards
Quickly turn mass quantities of data into meaningful information using Excel PowerPivot add-in
Make amazing interactive sales maps using Excel Power View powerful add-in
Analyze huge amounts of data fast and accurate
Improve general Excel knowledge
Follow along with included Excel project files
Learn the most current version of Microsoft Excel
Course updated regularly
Learn from an instructor with over 16 years of experience, teaching thousands of students in his own computer learning school
An instructor ready to answer your questions in less than 24 hours
Watch high-quality video lectures with lifetime access
Certificate of completion

COMPLETELY REDONE ON MAY 2020 WITH NEW VIDEOS!

Learn Power Query, PowerPivot, DAX, Power Map and Power Business Intelligence Tools that are rarely found on Excel users and transform to an Excel Star!

These days almost any job requires data analysis & presentation of results. While anyone can put a list of values in Excel & sum them up, not everyone can do advanced analysis, create interactive charts, summarize data intelligently using PowerPivot and PowerBI tools, and finally present output in stunning Dashboards.

Content and Overview
This course is a guide with simple step by step instructions to walk you through creating business intelligence reports and dashboards in Excel, quickly and efficiently. The course has been created with the version of Microsoft Excel 2019 but it’s compatible with older versions of Excel.

Excel Dashboards are powerful if you want to present and report data. However building a Dashboard is a complex process. In this course I’ll walk you through the most effective ways and tools to build Excel Dashboards that will wow management both visually and substantively. We’ll use Microsoft Excel free Power Query, Power Pivot, Power View tools and DAX Formula Language to analyze huge amounts of business data fast and transform data into bottom-line results – no programming required!

You’ll have lifetime access to watch the videos whenever you like. If you’re not 100% satisfied there is a 30 day money back guarantee!

Plus you will get fast and responsive support within 24 hours.

Free downloadable Excel working files are included to allow you to follow along using the same material I use throughout the lectures. You can download all of them.

What are you waiting for? Enroll now and make your Excel Dashboards look amazing!

What you’ll learn:

  • Understand and install the Excel Power Query, Power Pivot, Power Map and Power View add-ins

  • Import data from spreadsheets, Access, SQL, text files and other sources

  • Add calculated fields and columns with Data Analysis Expressions (DAX) formula language

  • Create complex reporting systems

  • Learn from start to finish the powerful features of Excel Power Map

  • Design your Excel Dashboard with link buttons

  • What are KPI’s and add key metrics

  • Create mind blowing Excel Pivot Charts from PowerPivot

  • Create powerful reports using Slicers and Timeline

  • Make an amazing interactive sales map using Excel Power View powerful add-in

  • Summarize data with Matrices, Cards and Tiles

  • Perform simple-to-sophisticated calculations like Month to date and Year to date

  • Analyze multiple tables together without the need to ever write VLOOKUP formulas again

Enroll now and master the creation of interactive Excel Dashboards!

100% risk free – 30 day money back guarantee! – Lifetime access

Who is this course for?

This course assumes that you have a basic knowledge of Excel . You don’t need to be a master of Excel. Only basic prior experience in Excel or Spreadsheets is required to get the most out of this Excel course.

This course is also ideal for data analysts, MIS professionals, business analysts, managers, dashboard makers, business intelligence professionals and students.

At the end of this Excel class you will be given a Certificate of Completion.

STUDENT REVIEWS:

“I highly recommend this course. It was exactly what I needed to jump start my career. I learned far more than I expected. The instructor is excellent !!!!!!!!” – Troy Newman

===================================================================

“A thorough tutorial on using charts, pivot tables, etc to create dashboard reporting. Very well presented and explain complex issues in a simple way. It is clear and easy to understand and has helped me develop reporting dashboards. Excellent!” – Albert Loomer

===================================================================

“Knowing how to create charts and how to create charts USEFULLY are different skills. Andreas helps the viewer use Excel skills to produce truly useful dashboards. Highly recommend for those looking to learn data presentation.” – David Anderson

===================================================================

Enrol now and enjoy!

Introduction

1
Introduction
2
DOWNLOAD: Project Files & Course Study Tracker & Important Notes for New Student
3
Versions, Compatibility and Install Power Pivot
4
HOMEWORK-1

GET STARTED WITH POWER QUERY

1
What is PowerBI?
2
Install Power Query for Excel
3
Getting to Know Power Query and Power Query Editor
4
Exploring Power Query Editor
5
Power Query Editor: Cleaning and Flattening the Data Table
6
Powerful Data Table Transformations
7
DEMO: Transform APPLE stock data with Power Query
8
Append, Duplicate and Merge Queries
9
Grouping, Aggregating and inserting Calculated Columns

GET STARTED WITH POWER PIVOT

1
The Excel Data Model
2
The Powerpivot Interface
3
Making a User-Friendly Model
4
Importing Excel data into power pivot

Importing relational data through the Power Pivot add-in is often a faster and more efficient alternative to importing in Excel.

5
Cleanup the Data Model
6
Importing Access Data & Create Pivot Table
Let’s learn how to create a pivot table from Microsoft Access Data.
7
Creating Table Relations and Hierarchies
8
Example of Table Relations

Add more power to your data analysis by creating relationships between data in different tables. A relationship is a connection between two tables of data, based on one column in each.

Download the Excel workbook used in the video tutorial and try the lesson yourself.

9
HOMEWORK:3 - Loading Data into Power Pivot

DAX FUNCTIONS

1
Introduction to DAX Formula Language
2
DAX Basic Calculations
3
Using DAX RELATED() Function
4
Using DAX Text Functions
5
Using DAX IF & Date Functions

With calculated columns, you can add new data to a table in your Power Pivot Data Model. But instead of pasting or importing values into the column, you create a Data Analysis Expressions (DAX)formula that defines the column values.

6
Using DAX COUNTX & FILTER Functions

Filtering is especially useful if you have a large list and you want to work with only a subset of the records in the list.

Download the Excel workbook used in the video tutorial and try the lesson yourself.

7
Using Dax CALCULATE() Function

In this video tutorial, we are going to learn how to use CALCULATE DAX Function, one of the most useful and favorite functions in Data Analysis Expressions

8
DEMO: Using DAX COUNTROWS Function, Slicers & Timeline
9
HOMEWORK:4 - Creating Calculated Columns into Power Pivot

CREATING MEASURES WITH DAX (or CALCULATED FIELDS)

1
Introduction to DAX Measures
2
Basic Measures with DAX
3
DEMO: Create Measures (Calculated fields) with DAX

Calculated fields, also known as measures in Excel 2010 version of Power Pivot and in Analysis Services Tabular models, are calculations used in data analysis. Examples commonly found in business reports include sums, averages, minimum or maximum values, counts, or more advanced calculations that you create using a Data Analysis Expressions (DAX) formula. 

4
Using DAX TOTALMTD() and TOTALYTD()

The Data Analysis Expressions (DAX) language is a new PowerPivot formula language that allows users to define custom calculations in PowerPivot tables (calculated columns) and in Excel PivotTables (measures). DAX includes some of the functions that are used in Excel formulas, and additional functions that are designed to work with relational data and perform dynamic aggregation.

5
Using DAX ALL() Function
6
What are KPI’s and how to create them?

Key Performance Indicators, also known as KPI or Key Success Indicators (KSI), help an organization define and measure progress toward organizational goals.

Learn how to create them in this lecture.

Download the Excel workbook used in the video tutorial and try the lesson yourself.

GET STARTED WITH POWER MAP

1
Power Map Basics
2
Power Map Format
3
Power Map Category
4
Power Map Scene
5
Power Map Annotation & Text Box
6
Power Map 2D Chart
7
Power Map Time Feature
8
Power Map Capture Screen & Video

DESIGN YOUR DASHBOARD

1
Design your Dashboard with link buttons

Start designing your Dashboard with link buttons.

Download the Excel workbook used in the video tutorial and try the lesson yourself.

2
Add key metrics and create our first Powerpivot pivot table

Enrich the Dashboard with key metrics and Pivot Tables.

Download the Excel workbook used in the video tutorial and try the lesson yourself.

You can also download the Character Map
3
Create pivot charts

Learn how to create Pivot Charts.

Download the Excel workbook used in the video tutorial and try the lesson yourself.

4
Complete the dashboard using slicers and timeline

Slicers provide buttons that you can click to filter PivotTable data. In addition to quick filtering, slicers also indicate the current filtering state, which makes it easy to understand what exactly is shown in a filtered PivotTable report.

A timeline is a type of chart which visually shows a series of events in chronological order over a linear timescale. The power of a timeline is that it is graphical, which makes it easy to understand critical milestones, such as the progress of a project schedule. Timelines are particularly powerful for project scheduling or project management.

Download the Excel workbook used in the video tutorial and try the lesson yourself.


GET STARTED WITH POWER VIEW & PIVOT CHARTS

1
Preconditions and features of Power View
Excel now provides you with a flexible, powerful and complete business analysis solution that lets you analyze data and discover insights in your own way. Find and connect to data, shape, model and analyze data, and design rich reports. Download the Excel workbook used in the video tutorial and try the lesson yourself.
2
Using Matrices, Cards and Tiles to visualize Power View Data
In this lecture you'll learn in detail all the tools of Power View feature, like Matrices, Cards and Tiles. Download the Excel workbook used in the video tutorial and try the lesson yourself.
3
How to create an amazing sales map and other useful charts
Maps in Power View display your data in the context of geography. Maps in Power View use Bing map tiles, so you can zoom and pan as you would with any other Bing map. To make maps work, Power View has to send the data to Bing through a secured web connection for geocoding, so it asks you to enable content. Download the Excel workbook used in the video tutorial and try the lesson yourself.
4
Completing Dashboards with Pivot Charts
Complete your awesome Dashboard with more tips & tricks. Download the Excel workbook used in the video tutorial and try the lesson yourself.
5
CHEAT SHEET ~ Create an amazing sales map with Powerview

BONUS SECTION

1
Excel 2019 Tutorial for Beginners
2
Top 20 Excel Tips and Tricks

Table of Contents

----------------------------------

1. SORTING COLUMNS WITH BLANK CELLS 0:56

2. MAKING THE ROWS COLUMNS AND THE COLUMNS ROWS 3:20

3. HIDE A WORKSHEET - VERY HIDDEN PROPERTY 4:20

4. BLOCKING A USER TO INTERACT OUTSIDE A SPECIFIC RANGE 6:00

5. HOW TO FREEZE TITLES AND SPLIT SCREENS 6:56

6. HOW TO ADD TEXT TO A NUMBER 9:00

7. CREATE A CONDITIONAL FORMAT BASED ON A VALUE 10:00

8. FORMULA ERROR CHECKING - TRACE ERROR 11:45

9. LOCKING AND HIDING FORMULA CELLS 13:04

10. GET RID OF UNWANTED SPACES 15:01

11. USE AUTOCORRECT TO ENTER DATA FAST 16:30

12. THE PASTE SPECIAL FEATURE 17:50

13. USE FILL HANDLE TO ENTER SEQUENCES OF VALUES 19:21

14. HOW TO GET RID OF DUPLICATE RECORDS 20:56

15. HIGHLIGHT FORMULA CELLS USING CONDITIONAL FORMATTING 22:20

16. ADD COMMENTS INSIDE FORMULAS 24:15

17. HOW TO USE FLASHFILL TO FILL VALUES IN A RANGE RAPIDLY 25:10

18. CHANGING VALUES WITHOUT FORMULAS 26:26

19. HIDING SCROLLBARS IN A WORKSHEET 27:46

20. HOW TO PROTECT WORKBOOKS, WORKSHEETS & CELLS 28:55

3
Excel 2019 Useful Keyboard Shortcuts and Tips
4
My Final Thoughts & Thank you!
5
Amazing discount for my other courses!
You can view and review the lecture materials indefinitely, like an on-demand channel.
Definitely! If you have an internet connection, courses on Udemy are available on any device at any time. If you don't have an internet connection, some instructors also let their students download course lectures. That's up to the instructor though, so make sure you get on their good side!
4.5
4.5 out of 5
974 Ratings

Detailed Rating

Stars 5
554
Stars 4
254
Stars 3
132
Stars 2
20
Stars 1
12
e669ff9e435866736dde6b0baf744d91
Get on Udemy
30-Day Money-Back Guarantee

Includes

5 hours on-demand video
5 articles
Full lifetime access
Access on mobile and TV
Certificate of Completion