Blog Posts on MS Excel
6 Joins in Power Query Joins in Power Query can help us with some of the most laborious and tedious tasks in Data Analysis. i.e. Comparing and Combining data sets. There are 6 Joins in Power Query which help us to Compare and Merge tables containing data. Those Joins are… Left Outer Right Outer Full … Read more
Group By feature in Power Query help us to group or summarize data from different Rows on the basis of a condition or conditions. Following is a sample Sales data from which I want to create a summary report. The data contains the sales amount for 3 products Snickers, Bounty and Galaxy for 4 different … Read more
We can use either the TRANSPOSE function or the Paste Special Transpose option to transpose data in Excel. Here is one more method to transpose data, which is using the Power Query in Excel. Following is the data which is I want to Transpose. To transpose this data using Power Query, Select a cell in … Read more
I have used at least 7 methods to AutoNumber rows in Excel. But if you ask me, which one is the Easiest and Dynamic method, the answer is… The method using Power Query! Using Power Query we can add an Index column to data sets and also create Serial Numbers of our choice. Let’s see … Read more
I this blog post, I will explain the Fill Up and Fill Down Transformation steps in Power Query of Excel with a simple example. Following is the collection report of Infinity Saga which comprises of 23 Marvel movies that were released in 3 Phases. I want to create two reports from this data. 1. Phase-wise … Read more
Whenever, I had to reverse text in Excel, I used to create an user defined function using STRREVERSE function in VBA. But after when I started using Power Query, I found an alternative to the VBA method. Let’s see how to reverse text strings in Excel using Power Query. Following is the list of Names … Read more
Why Unpivot or Normalize Data? Data in a Report format may give you the insight of a particular aspect, but that report need not be good for further analysis or generating another report or reports. Following is a sample data in Report format. It is always easy to Filter, Sort, Slice or Dice data, when … Read more
Excel Formula to Insert Sheet Name into a Cell
XLOOKUP Function against VLOOKUP and INDEX+MATCH
Highlight Cells according to the Entry made in that Cell