From Excel Pivot Table to Power BI Desktop

Be THE Power BI guru! Learn a new way of handling data and creating interactive dashboards with Microsoft Power BI tools
4.6 (38 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.
210 students enrolled
$19
$70
73% off
Take This Course
  • Lectures 30
  • Length 2.5 hours
  • Skill Level Intermediate Level
  • Languages English
  • Includes Lifetime access
    30 day money back guarantee!
    Available on iOS and Android
Wishlisted Wishlist

How taking a course works

Discover

Find online courses made by experts from around the world.

Learn

Take your courses with you and learn anywhere, anytime.

Master

Learn and practice real-world skills and achieve your goals.

About This Course

Published 7/2016 English

Course Description

Objectives

Learn a new way of gathering data and creating interactive dashboards using the new, free tool - Power BI Desktop. It is independent of Excel and therefore easier to use. Microsoft is enhancing this tool actively. This is the future of analytics and visualization. Start from scratch and then learn the entire lifecycle with Dr. Nitin Paranjape, who has been an Office MVP for 12 years and Microsoft Regional Director since 2015. 

Preparation

You will need to create a subscription for Power BI free version and download the latest version of Power BI Desktop. Instructions will be provided.  In this course, I am using a version available in Feb 2016.

Who should take this course

Anyone who currently works with data and generates reports will benefit from this course. The course is designed to be jargon free and easy to grasp. Even if you are an expert, you will be surprised at the simplicity and effectiveness of Dr. Nitin's approach. No knowledge of Excel or any other programming language or database is necessary. There is a module which introduces you to all the technical concepts you need to know in order to benefit from the course.

Difficulty Level

200

Topics covered:

- Power BI - the concepts

-Preparing data for Analysis in Power BI

-Creating a cohesive database

-Working with dates

-Creating reports

-Working with DAX

-Natural language Q&A

-Facebook Analytics 

-Integration with Excel

 

What are the requirements?

  • You will need to create a subscription for Power BI free version and download the latest version of Power BI Desktop. Don't worry, we will cover it in Lecture 3

What am I going to get from this course?

  • Understand the core concepts of Big Data Analytics
  • Create a cohesive database
  • Prepare data for analysis in Power BI Desktop
  • Generate reports and dashboards using Power BI Desktop
  • Keep the reports updated with scheduled or manual refresh
  • Know when and when NOT to use Power BI Desktop
  • Use DAX expressions to perform complex calculations such as Calculated Columns, Text Grouping, , Bin Analysis and Time Intelligence

What is the target audience?

  • Anyone who currently works with data and generates reports will benefit from this course. The course is designed to be jargon free and easy to grasp.
  • No knowledge of Excel or any other programming language or database is necessary. There is a module which introduces you to all the technical concepts you need to know in order to benefit from the course

What you get with this course?

Not for you? No problem.
30 day money back guarantee.

Forever yours.
Lifetime access.

Learn on the go.
Desktop, iOS and Android.

Get rewarded.
Certificate of completion.

Curriculum

Section 1: Introduction to Power BI Desktop
06:18

In this lecture, we learn about the core concepts behind Power BI. These concepts, explained in a simple language, without use of any jargon, will help you master Power BI quickly and easily. 

We will discuss how the data is gathered, cleaned up, enhanced and visualized. We will learn about each component of Power BI and understand its role. 

Each of this component and process will be discussed in detail in further lectures in this course.

05:09

Pivot Table has been the main analytical tool in Excel for decades. Everyone uses it on a daily basis. So why do we need another analytical tool? Two reasons. 

Power BI removes the limitations of Pivot Table and Excel (one-million row limit, slow performance, large file sizes, difficulty in sharing data). It enhances the capabilities of Pivot Table multifold. Now we can manage hundreds of millions of rows of data, use the cloud based and extremely powerful Microsoft servers to crunch data and create interactive reports in seconds. 

In addition, Power BI provides new capabilities like working with dates, geographic visualization, creating new insights using machine learning and so on. Before you say - "Oh, I don't need all these things", just have a look. Only then can you appreciate what you are missing.

The data which you use on a daily basis may not be different. However, using Power BI, you will be able to unearth more and more useful information from the SAME data. This information was being lost till now. Nobody was looking for it and you did not have the tool to do it. That is a like losing business opportunities everyday!

Now that you have the tool, you can find all possible useful information about your data and your business. Once you know something, you are smart enough to act on it. And that way you will manage your data, your work and your business better than before.

In short, Power BI can help you grow faster in your career, role or business. 

02:15

You cannot learn a Power BI by just viewing the lectures. You need to try it out yourself. The first step is to get a Power BI account, which is available for free. This lecture will guide you step-by-step and at the end you will have a free Power BI account as well as the Power BI Desktop tool. We will use both these components in all the further lectures. 

Power BI subscription requires a business email id. Public email ids like Outlook.com, Gmail.com, etc. are not supported. In case you do not have such an ID, you can create an Office 365 trial account (for free) for this purpose. Detailed instructions are included in this lecture. 

06:34

In this lecture, we will learn how to import various types of data into Power BI Desktop, such as CSV files, external web pages, or even Excel files.  We will also explore how to simplify the data cleanup process by editing data BEFORE loading onto Power BI Desktop. 

04:11

Power BI Desktop tool is usually used to import data. This data, along with the reports is initially stored in a local file. Once published the data is stored in the Power BI portal (on the cloud). 

In this lecture, we learn the details of data storage and related best practices. 

Section 2: Cleaning up the data
06:34

You may not have control over the format in which data is received. Unfortunately, the format of the incoming data is usually not suitable for analysis. It may have unwanted columns or rows, it may contain empty rows, unwanted calculations, spelling mistakes, wrong data types and so on. The list is endless.

I am sure you face the same problem in Excel based analytics. We often spend too much time in clean up of data. That leaves us with less time to actually analyze the data. 

This lecture shows you common problems with data format and quality. It also shows you what is the right way to clean up the data. There are 11 rules you need to follow to ensure that your data is in a format which is ready for quick analysis. 

The sample file illustrates the bad and good formats with plenty of examples. This lecture is relevant to you even if you do not use Power BI. Anybody who does data clean up needs to learn these 11 rules. 

04:49

One of the most common "bad" data types is a cross-tab. Power BI provides a very good method of converting the cross-tab data into tabular data. This method of cleanup is called "UnPivot". 

It can save you hours of manual work in Excel. Try out the sample file yourself and start using it immediately. If you have Excel with Power Query add-in installed (works with Excel 2010 or Excel 2013) or you have Excel 2016 (where Power Query is built-in), you can use this unpivot feature with Excel as well. 

06:51

In the earlier lecture, we learnt how to convert cross-tab data to tabular format using Unpivot functionality. 

Now let us learn how to manage if you get multiple cross-tab data blocks. This happens when you receive data about multiple products, from different regions, for different years or from different companies. 

Power BI not only helps you convert individual cross-tab to tabular data, it also helps you combine various pieces into a single, cohesive table. What's more, if data changes, we can refresh the entire data in just one click!

02:09

Often, we get data in tabular (good) format. Unfortunately, you may receive multiple pieces of good data. These could be in multiple Excel files, CSV files or any other format. Now we need to combine each piece into a master data table. 

In this lecture we cover combining multiple sheets or data as well as combining multiple CSVs. The same approach can be applied to any other data source as well.

In order to practice combining multiple CSV files, you need to save the Aug, Jul and Sept CSV files into a separate folder on your PC. 

Power BI Data
3 questions
Section 3: Creating a Cohesive Database
03:30

One of the limitations of Pivot Table is that it can work only with ONE block of data (table). Often, we receive data which is coded. It contains product codes, location codes, customer IDs and so on. 

While creating reports, we want to show the product name, location or customer name. In order to decode these codes, we usually have a separate file which contains the list of codes and equivalent names or descriptions. 

Unfortunately, Pivot Table cannot incorporate both these pieces of data. Therefore, we have to add a column in the raw data and use Vlookup to fetch the related name or description from the other file (usually called the master file). 

Vlookup increases the file size and slows down the performance. Even though we can copy paste the VLOOKUP functions to values, it is a cumbersome workaround rather than an elegant solution.

Power BI solves this problem. Power BI is basically a database. It can import both the tables and create a relationship based upon the code column. This eliminates the need for VLOOKUP and simplifies report creation. 

04:49

Incoming raw data may need to be enhanced by adding calculations to it. Some calculations require VLOOKUP of related data. Some calculations depend upon external constants and so on. Every time the data is refreshed, we need to extend the calculations manually. 

Power BI allows you to create new columns based upon a formula. When more data comes in the formulas are automatically extended. In this lecture we learn the Add Column feature. A more detailed coverage of this functionality will be done in the DAX section later. 

Section 4: Working with Dates
03:27

Usually we will have at least one date column in our data. Dates need to be summarized (or rolled up) by weeks, months, quarters and years. In Excel, all this is usually done using manually added formulas. Of course, there is a built-in feature in Pivot Table to perform very sophisticated date grouping. Unfortunately, most people are not aware of this Pivot Table feature even though it exists for 18 years!

Anyway, Power BI has an automatic capability of grouping dates. We will explore this feature in detail in this lecture. 

07:14

Power BI automatically groups dates by months, quarters and years. This is useful to look at data at a higher level of granularity. However, most data analysis performed to analyze performance needs to match the financial year. Power BI assumes that your financial year starts with January. But that is not always true. Different countries and companies have their own definition of Financial (Fiscal) year. 

Power BI provides you with full flexibility to define your own fiscal years, quarters and anything else you may need to customize analytics. 

As there are too many possible variations, it requires you to create your own custom Date Table (also called the Calendar Table). Using this Date table, you get unimaginable amount of power while working with dates and time periods. 

In this lecture we will see how to create a custom date table and incorporate it into your data. 

Section 5: Creating Reports in Power BI Desktop
07:54

Once the data is cleaned up, relationships created, date tables mapped and custom columns added, it is time to create reports (finally!). 

This lecture shows you how easy it is to create reports. We have lots of visuals to choose from. If you still want to show your report like a pivot table, no problem! That is also possible. 

The most important difference is - you can create multiple reports (equivalent of multiple pivot tables or pivot charts) on the same surface or work area.

If you have never seen this before, it is miraculous: All the reports are automatically correlated to each other. Whenever you click on any visual, others get filtered accordingly. This is a game changing experience. 

It will provide you with infinitely more useful information than before - with minimal effort. 

06:38

Analysis is all about understand what is happening. You look at the report and think: "Ok, this is the overall picture. Now I want to see what happened by year or by product or region and so on".

The questions arising in your mind need to be answered visually. That is done using filtering. We have been using filters in Pivot Tables. But these had their own limitations. Pivot filters affect only one pivot table. If you have a dashboard containing multiple pivot tables, you need to add multiple filters for each pivot table. That is very cumbersome. 

Recently (since 2010) Excel introduced slicers which allow you to filter multiple pivot tables simultaneously. Even then, the amount of interaction is limited. 

Power BI is designed with interaction as the key objective. It offers multiple types of filtering. In this lecture we learn about visual filters, slicers and Drill Down functionality.

04:00

So far we have been creating reports using the Power BI desktop tool. However, we cannot send the PBIX files to each other to share the reports. That would lead to the same issues like sending Excel reports by email to each other. Too many copies, repetitive mails being sent, large files cannot be sent by mails and so on. 

To solve all these issues once and for all, Power BI portal was created. Once you create a report using the desktop tool, you publish it to the portal and just share the LINK with your team. That way, the actual report crunching is happening on the powerful and secure Power BI portal servers rather than your local PCs (which are invariably slow performing). 

In this lecture, we will learn how to publish reports to Power BI Portal. Important visualizations from one or more reports can then be added to a dashboard - a collection of related reports. Dashboards are available only on the portal. 

04:29

Data is always increasing and changing. That forces us to keep updating our reports as well. 

In Excel, entire teams of people were required to get incremental data, manually append it to existing data and refresh all the reports. Now, using Power BI, this refresh activity can be automated. 

You can refresh data either manually (on-demand) or automatically. Depending upon the business need and the speed with which data changes, you can schedule periodic refresh as well. 

We will learn about various data refresh options in this lecture. Even a simple CSV file sitting on your local PC can be refreshed automatically - did you think that was possible? 

01:49

Now your dashboards are shared with your colleagues and superiors. Reports provide summarized information. However, if the report accuracy is disputed or if someone wants to cross-check the validity of the report, we need to show them the underlying data. 

In this lecture we will see how to view the data which contributes to each visualization in the report. Technically this is called "Data Lineage"

Quiz Time
2 questions
Section 6: Working with DAX
05:21

We learn how to add calculated columns using DAX functions. This is a quick introduction to DAX usage. 

The sample file contains the same Facebook data which we have used earlier. The file will work as it is. But if you want to refresh the data, you will need to login using your Facebook ID.

06:36

Text Grouping in Pivot Table is fairly simple - it can be performed within the Pivot iteself. In Power BI, we need a separate mapping table to be created. In addition, we need to establish a relationship between the two tables and use them to create additional grouping or classification column. 

This is practically useful in categorizing, classifying or scoring data based upon existing columns. For example, from Products list, you can create Product categories. Or from a list of diseases, you can classify them into Acute or Chronic types. 

The sample file contains the same Facebook data which we have used earlier. The file will work as it is. But if you want to refresh the data, you will need to login using your Facebook ID.

04:15

Bin or Bucket analysis is a common business need. You may want to classify transactions as small, medium or large. It can also be used for performing ageing analysis for finance data or age group analysis with demographic data. The bins need to be defined in a separate table. We need to add a new column with a DAX formula to look at each value and fit it into available bins. Conceptually it is very similar to Range based VLOOKUP in Excel.

The sample file contains the same Facebook data which we have used earlier. The file will work as it is. But if you want to refresh the data, you will need to login using your Facebook ID.

07:30

Measures are like a dynamically calculated formula. Adding a column is like using the same formula for each row of the raw data table. Both use DAX functions. But when the function actually gets calculated is very different in case of measures. 

The sample file contains the same Facebook data which we have used earlier. The file will work as it is. But if you want to refresh the data, you will need to login using your Facebook ID.

09:41

How are we doing compared to the last month, year or quarter? Is there growth or decline? These are important indicators of business progress. DAX has very powerful functions to help us in such scenarios. These functions eliminate hours of manual work in Excel. The flexibility of analysis increases and the chances of errors or inaccuracies is eliminated. Hard-coding of cell ranges is completely eliminated. If you maintain large Excel files which need manual updates every month, you must learn this topic.

The sample file contains the same Facebook data which we have used earlier. The file will work as it is. But if you want to refresh the data, you will need to login using your Facebook ID.

Dax Quiz
3 questions
Section 7: Practical Scenarios
06:34

Any business is created with growth in mind. Declining or stagnating business soon die unless corrective action is taken. Therefore, assessing growth over time is an integral part of data analysis. 

This kind of comparison can not rely on VLOOKUP. We create hard-coded formulas, refer to data area in Pivot tables manually, add extra columns and edit formulas to find YTD, MTD growth and so on. It continues to be a nightmare. Millions of people worldwide are just getting paid salaries to maintain such complex and error prone reports which analyze current performance with past performance. 

In this lecture, we will learn how Power BI allows you to use relationships to solves this cumbersome problem. 

Section 8: Powerful Features of Power BI
04:08

Facebook is popular. But if you use FB for business promotion, you also need to analyze this data. Although FB does give you some analytics, you will invariably want to incorporate that analytics into your own reports and correlate it with your business data. 

Power BI makes it extremely easy to import data from Facebook and analyze it instantly. It can also refresh the data either on demand or periodically to ensure that your analysis is never outdated. In this lecture we learn the entire FB analytics process - data import, cleanup and visualization. 

The sample file contains a list of FB posts from Microsoft Power BI Facebook page. You can use the file as it is. If you want to refresh the data, you will need to login using your own Facebook ID. 

03:09

Power BI Portal offers an amazing new way of creating reports. You can just type a question like "show total amount by country and by year". Power BI now analyzes the raw data and creates a visual report instantly. No drag drop required. It needs to be seen to be believed. 

You need to publish the sample PBIX file to the Power BI portal to use this feature. Instructions are included in the file itself.

02:46

Power BI helps you control the visibility of data. Suppose you have data for multiple products. The report shows all products. However, you have different product managers for each product and you do not want them to see each other's data. How do we do that? The answer is Row Level Security. We define each product manager as a role and restrict the visibility of data. 

The sample file already has a role defined. However, you will need to change the user associated with that role to test it out as a real demo. Publish the file. Edit the Role called New Zealand and change the email id of the associated user to a valid email id in the context of your Power BI subscription.

02:49

Power BI is also available within Excel. It is a combination of Power Query, Power Pivot data model, and Power View. Nowadays, it is highly recommended that you move from Excel based Power BI to independent Power BI - which is what this course is all about. 

But what if you have already invested time and effort in creating data models and dashboards using Excel based Power View? No problem. These can be imported (one-time) into Power BI Desktop and then incorporated into the Power BI portal. Data connections, relationships, DAX formulas and Power View Dashboards will be converted and imported. 

If you have the right version of Excel with all Power BI components (basically 2016 version or older versions with relevant add-ins), open the sample file in Excel and have a look at the contents. Otherwise, just import it using Power BI desktop. 

Power BI Process Quiz
4 questions
Section 9: Putting it all together
10:48

Excel will continue to be a common data source for Power BI. What's more Excel dashboards created using Power View can also be consumed within Power BI. This lecture covers various integration points between Excel and Power BI. A word of caution. Apart from Excel being a data source for Power BI, all other integration points have limitations. Therefore, in the long run, I strongly recommend that you start using Power BI in preference to Excel for data analytics requirements. 

Reports and MIS which requires very specific Excel functions, complex formulas and models will still require Excel. 

For this lecture, there are no sample files. You can use any of the relevant sample files listed above to explore this integration. 

05:30

We have come to the end of this course. That, by no means, is the end of your work. In fact, your work is just starting now. 

In this lecture we summarize and crystallize all that we have learnt: Concepts, processes, syntax and best practices. More importantly, I will show you how to take this knowledge further and use Power BI in your day to day work.

Most probably, your enthusiasm about Power BI will NOT be shared by your colleagues (and most definitely, by your bosses). That is not because they are stupid. But because they just do not know what they are missing. Therefore, I have also given you a recommended method of introducing Power BI to your colleagues and superiors in a palatable and empowering (rather than a confrontational) manner. 

My best wishes to your in your future analytical endeavors. If used correctly, any analytical tool must lead to your growth. It will also drive organizational growth if everyone uses analytics prudently.

Analyze more, act better, grow faster. 

Cheers!

Students Who Viewed This Course Also Viewed

  • Loading
  • Loading
  • Loading

Instructor Biography

Dr Nitin Paranjape, Office System MVP, Microsoft Regional Director

- One of the 28 Microsoft’s Most Valuable Professionals for Office System

- Microsoft Regional Director (honorary title – 2015, 2016)

-  Written 2 books and 1100+ articles and blogs on Microsoft Office Platform 

- Coached 223,000 professionals across 1100 organizations globally

Ready to start learning?
Take This Course