Power BI for Excel Users - Work Smarter, Grow Faster

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. 

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.  https://efficiency365.com

We get different types of dates while importing data. In this video we will learn how to use Power Query to clean-up different date formats - that are not understood as the DATE data type by default.

Remember that all these features are available in Excel - Data - Get and Transform menu as well.

Sample file is available for download and practice.

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!

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. 

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.

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. 

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. 

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. 

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. 

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. 

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.

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. 

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? 

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"

Learn how to add summary information like grand totals, counts to a report using the Card Visual. Microsoft added this new visual in November 2025.

Learn how to create maps from your data. Just names of places, country, city, postal code is enough. Geocoding is automatically done. Azure Map is the best option available. Always visualize geographical data as a map to learn more about your data and take informed decisions.

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 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 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.

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.

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.

Learn about new, fast calculations within a visual. Similar to Show Values As in Pivot Tables. % of Grand Total, Running Total, Previous or Next comparison, and more ...

Create interactive reports and minimize space wastage using Field Parameters. Combine multiple fields into a slicer and let the viewer select what they want to see. A new and powerful way to create intuitive and responsive reports.

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. 

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.

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.
Note: This visual will not be available after December 2026. Microsoft is replacing it with Copilot (which requires a paid Fabric license).

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.

Excel and Power BI integrate both ways. We can export data from Power BI in various ways. OR we can import data from Excel as well. Usually, we connect to Excel as a data source. But if you already have a data model created in Excel, Power BI can import that directly.

Once a Power BI report is published, the same data (dataset) can be used in Excel. No copy paste. No export import. This is a very powerful way of reusing data. In fact, data model created by you can also be used by others in your team.

In this video, we learn various types of data integration between Excel and Power BI.

Power BI makes it easy to show live reports right within PowerPoint. In this video, we learn the step-by-step process. Copy a link from Power BI report, paste it inside PowerPoint - Power BI Add-in and you are done!

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!