Pivot Table To Power Bi

The Eleven Commandments of Good Data Preview 06:32

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. 

How to clean up cross-tab data Preview 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. 

How to clean up multiple cross-tab data Preview 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!

Combine Multiple cross tabs in Power Query

Prevent loading the unwanted three tables from loading.

Combining Data from multiple Sheets or Files Preview 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. 

Import data from PDF Preview 07:04

Often we get data as PDF. Copy pasting data from PDF to Excel, the formatting goes haywire and we waste a lot of time repairing it. Fortunately, Power BI can now import data directly from PDF. This is available in latest version of Excel as well (Office Pro Plus). Use the file "PDF Import - Sample.pdf" to practice this import process.

Power BI Data

Eliminating VLOOKUP using Relationships Preview 03:31

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. 

Adding Calculated Columns Preview 03:36

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. 

Working with Dates Overview Preview 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. 

Creating a custom Date Table Preview 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. 

Creating reports Preview 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. 

Filtering Reports Preview 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.

Publishing Reports to Power BI Portal Preview 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. 

Keeping the Reports updated Preview 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? 

Viewing the Data behind the Visualization Preview 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

Calculated Columns Preview 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.

Text Grouping and RELATEDTABLE Preview 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.

Bin Analysis Preview 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.

New Measure or New Column - Which one to use when? Preview 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.

Time Intelligence - Comparing current data with the past Preview 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

Comparing Budget vs Actuals Preview 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. 

Use AI to understand rise and fall of business parameters Preview 06:44

Revenue, stock levels, transactions, all business parameters are going to go up or down over time. We can see these fluctuations over time. If something went down or up, what could be the reasons? We analyze that and create reports manually. But do we try to explain in all possible ways? Using all available factors (fields in data)? The answer is NO.

That is where this amazing AI based feature comes in. Learn it and impress everyone around you. More importantly, learn more about your own data and take better decisions and manage your business more intelligently.

Facebook Analytics Preview 04:08

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

Warning: Please check if Facebook is available as a data source in your Power BI Desktop. If you cannot find it, skip this video.

=====================================================================================================
Facebook as a data source is currently discontinued by Microsoft due to some technical (and probably legal) reasons. FB API policies have changed which prevented it being used as a data source - not just for Power BI but most data analytical environments. I am keeping this video in the hope that FB as a source will return sometime soon.

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. 

Natural language Q&A: Just type a question and create a report Preview 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.

Row Level Security - Controlling who can see what in Power BI Preview 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.

Power BI Process Quiz

Excel and Power BI Integration Preview 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. 

Putting it all together Preview 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!