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
Are you an Excel Expert looking for a freelance job? Apply for a Got It Excel Expert job using the following link and start earning money right away! Become an Excel Expert at Excelchat Main Attractions You can work from anywhere in the world Payment process is automatic and you will be paid once in … 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