Power Query and Power View - Excel Power Tools Master Class
4.6 (26 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
679 students enrolled
Wishlisted Wishlist

Please confirm that you want to add Power Query and Power View - Excel Power Tools Master Class to your Wishlist.

Add to Wishlist

Power Query and Power View - Excel Power Tools Master Class

Learn How to Get and Transform Data Using Excel's Power Query and then Visualize That Data with Excel's Power View
4.6 (26 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
679 students enrolled
Created by Paula Guilfoyle
Last updated 1/2017
English
Curiosity Sale
Current price: $10 Original price: $150 Discount: 93% off
30-Day Money-Back Guarantee
Includes:
  • 2.5 hours on-demand video
  • 16 Articles
  • 2 Supplemental Resources
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
What Will I Learn?
  • Use Power Query to Get data from multiple sources
  • Use Power Query to transform data into a usable format
  • User Queries in Excels Power Query to automate tasks
  • User Power Query to merge data sources
  • User Power View to Visualize data in Column charts
  • User Power View to Visualize data in Pie charts
  • User Power View to Visualize data and spot trends in Line charts
  • User Power View to Visualize data on Maps and in Scatter charts
  • Create Interactive Dashboards using Power View for Excel
  • Add images to dashboards for more personalized visulaisations
View Curriculum
Requirements
  • Power View is available in Excel 2013 and 2016. It is also available in SharePoint server 2010.
  • Power Query is available for Excel 2010, 2013 and in 2016 it is known as get and transform data
  • This course is not about Excel formula and Functions
  • Prior knowledge of Excel is required
Description

Power Query enhances self-service business intelligence (BI) for Excel with an intuitive and consistent experience for discovering, combining, and refining data across a wide variety of sources including relational, structured and semi-structured, OData, Web, Hadoop, Azure Marketplace, and more.

Power Query brings the power of Data to the ordinary business and the Excel user.

Power View is an interactive data exploration, visualization, and presentation experience. It provides intuitive ad-hoc reporting for business users such as data analysts, business decision makers, and information workers

COURSE SUMMARY

Power Query is an Excel add-in, available in Excel 2010 Professional Plus and Excel 2013. In Excel 2016 it is known as Get and discover data. Power Query can be described as SQL for Excel users (without the need for knowing SQL). It allows uses find data from multiple sources, combine data, transform data, and make the data usable for reporting and data analytics. 

LEARN HOW SET UP QUERIES TO EXTRACT DATA FROM MULTIPLE SOURCES, TRANSFORM DATA OF DIFFERENT TYPES AND LEARN HOW TO USE POWER QUERY TO AUTOMATE DAILY TASKS. 

MICROSOFT'S EXCEL, WHICH INCLUDES POWER QUERY, IS NOW PART OF A BI SYSTEM. IF YOU WANT TO IMPROVE YOUR EXCEL SKILLS FAR BEYOND THE BASIC LEVEL AND BRING BUSINESS ANALYTICS AND DATA TO YOUR ORGANISATION THEN THIS COURSE IS FOR YOU

Power View is available in Excel 2013 and SharePoint Server 2010. In Power View, you can quickly create a variety of visualizations, from tables and matrices to bubble charts and sets of small multiple charts. For every visualization you want to create, you start with a table, which you can then easily convert to other visualizations, to determine which one best illustrates your data.

LEARN TO USE POWER VIEW CHARTS AND MAPS OF ALL TYPES TO VISUALIZE DATA TO AID BUSINESS DECISIONS AND SET UP HIERARCHY TO MAKE VISUALIZATIONS MORE INTERACTIVE 

MICROSOFT'S EXCEL, WHICH INCLUDES POWER VIEW, IS NOW PART OF A SELF SERVICE BI SYSTEM. IF YOU WANT TO IMPROVE YOUR EXCEL SKILLS FAR BEYOND THE BASIC LEVEL THEN THIS COURSE IS FOR YOU


CONTENTS AND OVERVIEW

In section one of this course we will start at the very basics and learn how to set up Power Query. In this section you will find sample data to download, so you can practice along. You will then work though a case study on how an organisation can use internal data of different file types, such as Text files and Excel files, and then combine this data with External data from the web to accurately calculate the potential market size, the actual market size and the portion of the market held by the company. This case study will give you a solid working knowledge of Power Query.

Next we will look at some more advanced features of Power Query. You will be introduced to the language use, which is known as M, you will learn how to query not just files of different types but also Folders of data. You will also learn how to sort “Bad Data” and user Power Query to automate reoccurring tasks.

You will also lean learn how, and when to visualize data using 

• Column and Bar Charts

• Pie Charts

• Line charts

• Scatter/bubble charts

You will learn how to set up hierarchy and make these charts more interactive to give deeper analysis and how to visualize data on Maps

In addition to this through the sample data, will look at how trends in data are easily spotted using visualization, making business decisions easier and actionable.

Who is the target audience?
  • Excel users, Data analysis's, Accountants, Those responsible for reporting, Bookkeepers and anyone who wants an advanced knowledge of Excels Power Tools
Students Who Viewed This Course Also Viewed
Curriculum For This Course
38 Lectures
02:28:34
+
Power Query
20 Lectures 01:14:48

In this lesson you will learn how to download and install Power Query for Excel



Download and set up Power Query for Excel
06:37

Where to go to download Power Query For Excel and what is supported

Getting Set up
00:11

Download the following zip file so you can work along in Power Query

Power Query files
00:03

Introduction to the case study that we will be working in this this Power Query section of this course Power Query and Power View - Excels Power Tools

Case Study -Scenario
00:59

Learn how to connect Power Query to an excel file and transform the data

Preview 10:37

Carry out the following activity to ensure you can connect to an excel file using Excels add in Power Query

Activity
00:10

Learn how to connect Power Query to a Text file and transform the data

How to connect Power Query to a .TXT file
05:03

Carry out the following activity so you know you can connect Power Query to a .txt file

Activity
00:04

Learn how to get and transform web data using Excels Power Query add in

How to connect Power Query to webdata
14:19

In this activity you will practice connecting to Web data and transforming that data using Excels Power Query

Activity
00:08

In this lesson you will learn how to merge and analyse the data

How to merge and analyse data
09:54

Carry out the following activity to merge and analyse data

Activity
00:15

When you connect to a folder of data using power query in Excel, once you add a new file or update a file in the folder, your data will also update.  In this lesson you will learn how to connect to a folder of Excel files in Power Query ( Get and Transform Data) and you will also be introduced to Power Query language known as M

How to connect Power Query to folders of data
08:54

Learn how to connect to a folder of data using Excels Power Query and how to update the folder and query together

Activity
00:07

Not all data that you connect to will be in a format that you can use, this type of data is know as bad data.  In this lesson you will learn how to can connect to and transform bad data into a usable format

Connecting Power Query to bad data
06:50

Carry out the following activity in Power Query to learn how to connect to and transform bad data

Activity
00:04

Sometimes data is in a form know as pivoted data, which is difficult to analyse.  Learn how to use power query to unpivot data

Unpivot data
06:05

Carry out the following Activity

Activity
00:04

Here are some useful resources for Excels Add in  Power Tool, Power Query

Power Query useful resources
00:07
+
Power View
18 Lectures 01:13:49

Before we move into Excels Power View, lets get a quick overview of this section

Power View Introduction
01:45

In this lesson we will look at turning on Power View in Excel 2013 and we will also look at the Power View Ribbons

Setting up Power View and the Power View Ribbons
03:55

In this lesson we will look at the data set that we will use for this course.  It is a good idea to familiarize your self with the data set so that you have an idea of values and fields that you can plot and visualize 

The Data Set
03:52

Learn how to work with Column charts and interactive column charts in Power View by adding ad hoc hierarchy 

Column Charts
11:24

Carry out this activity in Excels Power View 

Activity
00:07

learn how to work with Pie Charts and add hierarchy to the data model

Pie Charts
13:03

Learn to use Pie charts and add hierarchy in Power View

Activity
00:08

Learn how to create Line charts and use line charts in Power View to spot trends in your data

Line Charts and trends
08:31

Carry out the following activity to analyse data and find trends using Line charts in Excels Power View 

Activity
00:07

Learn how to visualize data on a map and how to make sure you get the correct map locations

Maps
05:39

Carry out the following Activity to create a Map Visualization using Power View in Excel to analyse data

Activity
00:06

Learn how to visualize data using an animated Scatter chart

Moving Scatter Charts
05:48

Carry out the following activity to create a Moving Scatter Charts in Excels Power View to analyse data

Activity
00:05

In this lesson you will learn how to use cards for visualization and tiles to filter the visualizations  

Using Cards and Tiles in Power View
06:11

In this lesson you will learn how to add images to the data source for use in Cards and Tiles and how to update the data connection

Adding Images to the Data Source and updating the data connection
04:52

Carry out the following activity for adding image in Excels Power View

Activity
00:08

In this lesson you will learn how to work with tables of data in Excel with out the need for knowing Powerpivot

Working with Tables of Data within Excel
07:21

Conclusion
00:45
About the Instructor
Paula Guilfoyle
4.3 Average rating
1,447 Reviews
21,579 Students
19 Courses
CPA Accountant, Online Educator & Life Long Learner

Paula is a Qualified CPA with over 15 years' experience in the fields of Accountancy, Business Management,Process improvement, Internal Audit, Group accountant, Operations management and Training. All across a broad range of industries and sectors. Paula has been Key Speaker at many Accounting Events where her talks on Excel are received very positively. Taken from her experiences in Accounting and business fields, Paula also has Udemy courses for those wishing to up skill, especially in the area of Spreadsheets, Bookkeeping and Accounting.

Now an E-learning Educator 5+ years, Paula also has a focus on E-learning and online teaching. Drawn from her online teaching experience,Paula has a number of courses available to online teachers to help bridge the skills gap for those that teach or wish to teach online.