Learn How to do Comments in Query Steps with Power Query. The new feature of Power Query allows you to add descriptions to each step in the Applied Steps pane. The step descriptions become comments in the M code in Power Query. If you write M code in the Advanced Editor window, then your comments appear...Read More

Power Query also allows you to create a list query from a data table available. The column from the given data table can be used to create a list query. This can be explained with the help of an example. Creating A List Query From A Table Column Here, we have a data table containing...Read More

Learn How to Manage Parameters in Power Query Step-bu-Step. Power Query can create a query parameter and link to the List Query. This is one of the most unknown features of Power Query. Let us study this unique feature along with an example. We have considered the same example of list query explained above. Manage...Read More

One of the important and most needed features of Power Query is Renaming Column Headers. You can rename a column in Power Query very easily. Rename A Column With Power Query Let’s observe the below steps to rename column headers- Step 1: Load Data in Power Query When you load the data in Power Query,...Read More

Merging columns is also a feature of Power Query which is very useful for transforming data. You can use this feature easily by following below steps. Merge Columns in Power Query Step 1: Select the Columns in Power Query for Merging If you have a data table as shown below and you want to merge...Read More

You can even append queries in Power Query. This feature is very useful if you want to combine two data tables together. Let us understand this using an example. As shown in the below picture, you have two sales data for the year 2015 and 2016. We want to combine these two data tables together....Read More

Another important feature of Power Query is Format Columns. There are various formatting options available in Power Query for columns like Trim, Clean, Add Prefix, Uppercase, etc. In this example, we are going to see how to capitalize each word from the column using the formatting feature. Format Columns By Power Query Step 1: Select...Read More

Learn How to Extract Age using Date in Power Query. This is an amazing feature as it allows you to extract the age out of the date provided. Let us elaborate on this feature using an example. Steps to Extract Age Using Date With Power Query Here, we have a data table containing employee information...Read More

Steps to Create Grouped Serial Numbers in Power Query. If we have data for products and we want a unique identifier for each product name in the list. But as you can see below each product appears multiple times in the data table. Create Grouped Serial Numbers In Power Query But we want to keep...Read More

The Power Query M language allows you to create a list containing a sequence of numbers or characters very easily. This is possible when you are defining lists by using expressions in the format- Create A Sequential List With Power Query Let us take an example to understand this feature. Step 1: Add Custom Column...Read More

While using Power Query, you might’ve come across a step that reads ‘Navigation’  in Applied Steps pane and it is automatically created by Power Query. But, do you know what it is used for? Navigate Query Editor Using Power Query The code generated by this step is = Source{[Item=”Sheet1″,Kind=”Sheet”]}[Data] This is one of the ways...Read More

Power Query also allows you to edit a query that you have already generated. Let’s take an example. Suppose you have a data table as shown below. Editing A Query By Power Query This is an employee data and you have to create the post for each employee based on their experience given in the...Read More

Disable Relationship Detection for Beginners in Power Query. This is another feature of Power Query that will speed up your queries. When you load your data into the data model from power query, Excel will try to find and build relationships between the new data and other tables in the present in the data model....Read More

If you want to delete some steps, you can do it in one shot. The Power Query Delete Until End feature helps to delete as many steps up to the end in one click. Delete Steps Until End By Power Query Let us understand this feature with the help of an example. Step 1: Load...Read More

Advanced Editor With Power Query can perform a lot of complex steps with any kind of data source. However wouldn’t it be fun unless you understand what is happening under the cover? Advanced Editor With Power Query Let’s go through the steps in detail to understand the magic of Power Query. Step 1: Load the...Read More

Data Type Conversion With ‘M’ with Power Query. Sometimes it happens that the things we want to do are not built in the user interface.  But Power Query’s programming language M has made this possible. Data Type Conversion With ‘M’ Using Power Query One of the unique functionality of M is that it takes data types very...Read More

If you want to use the Excel LEFT Function in Power Query, then you can’t directly go for it. This is because the Excel LEFT Function it is not supported in M. Use Excel’s Right Function With ‘M’ To Extract Left Side Data But there is an alternate way for using the LEFT Function in...Read More

How to Extract Right Side Data form Excel? If we want to use Excel’s RIGHT Function, it is not possible as it is not supported in M. But we can use the RIGHT Function in M in other way using Power Query and the Text. End formula! Use Excel’s Right Function With M To Extract...Read More

Get the Text Length using Power Query M. If we want to use the Excel LEN function to get the length of strings, it is not supported in M. Let us see how to use the LEN Function in M to get the length of strings. Get Text Length Using ‘M’ Power Query Step 1:...Read More

In the above example, you learned how to concatenate the columns using the M language. Unfortunately, not all of the Excel’s formulas can be used in M query. Replicating Excel’s Find Function With ‘M’ Using Power Query For instance, if we want to use Excel’s FIND function to find a specific character in the text,...Read More