## Combine Multiple Worksheets of a Workbook using Power Query in Excel

In this blog post, I am explaining the Power Query Method to combine data from multiple worksheets. If you are a person who deals with data in Multiple Worksheets, You should definitely try this one. Following is the screenshot a workbook which contains 7 worksheets called Violet, Indigo, Green, Blue, Orange, Yellow and Red. These…

## Passing a Parameter in Power Query

When we transform data using Power Query, every Transformation Step is recorded as an M Code. And most of the time, values got hard coded in these codes. The problem with Hard coding is, when the user decides to change the input data, the Source code (Query in this case) also should be modified accordingly.…

## Change Letter Case using Power Query

Like the UPPER, LOWER and PROPER functions we can also use Power Query to convert letters cases. Following is a list of names/words that are written with a combination of upper and lower case letters. To convert these words into UPPER, LOWER or PROPER cases using Power Query, Select a cell in the data set…

## Add an Index Column using Power Query

In this blog post, I am explaining an easy method to add a new column to a data set which contains Automatically updating Serial Numbers. Following is the data set to which I want to add a new column of Serial Numbers. To add a column of Automatically Updating Serial Numbers to this data, Select…

## Remove or Extract Special Characters from a data set using Power Query

In this article, I will explain an easy method to Extract or Remove Special Characters, Alphabets or Numerals from a data set using Power Query in Excel. Following is the data set from which I want to remove the special characters like ‘!’, ‘@’, ‘#’, ‘\$’, ‘%’, ‘&’, ‘*’, ‘(‘ etc. To clean this data,…

## Find the Most Repeated Word in a data set using Power Query

MODE function in Excel will return the most frequently occurring number in a data set. But for finding the most frequently occurring text string in a data set, we don’t have an Excel function for that. In this article, I will show you how to find the most repeated word in a data set using…

## Joins in Power Query

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…

## Group Data using Power Query in Excel

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…

## Transpose Data using Power Query in Excel

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…

## Automatically Number Rows in Excel using Power Query

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…