If we want to use the 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 Right Side Data Let’s see how to...Read More

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 The Length Of Strings Using M Step 1: Select the Name Column First, load the data...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 Right Function With ‘M’ Using Power Query For instance, if we want to use the Excel’s FIND Function to find a specific character in the...Read More

There are times when we want to perform such tasks that are not built in the user interface i.e. features that are not available on the ribbon. But still, this can be possible with Power Query’s programming language which is called M language. Concatenate Datausing ‘M’ With Power Query Initially, we will do a simple...Read More

Sometimes we need to transform the text from a huge data table which is not directly possible. But, with Power Query, you can easily do it using Add Column from Examples feature. Add Column From Examples By Power Query Step 1– Select the column on which you want to apply changes Suppose you have a...Read More

In Power Query, there is a feature to add a new column called Conditional Column feature. You can define IF-THEN-ELSE conditions to add the conditional column. When the conditions are fulfilled, the conditional column will automatically demonstrate the values that you have specified using IF-THEN-ELSE conditions. Add Conditional Column Using Power Query Let’s take an...Read More

As you can import the data from CSV, the same way you can import the data from the text file. The reason is that Power BI provides the same option to import the CSV file as well as a text file. Let us understand this with the help of below steps. Import Data From Text...Read More

Now you know how to import data from an XML file in Power Query. But what if your data source is in the form of CSV file? There is no need to worry if you have your data inside a CSV file (Comma Separated Values). This is because it’s very easy to import data from CSV...Read More

You can easily load the data files like Excel spreadsheets in Power Query. But what if your data source is not in Excel spreadsheet? Import Data From XML Using Power Query It’s very usual nowadays to get data from a company’s accounting or sales system in the XML format. If the data is inside an...Read More

The good thing with Power Query is that there are multiple ways to pull in data. Import Web Data Using Power Query One of the way is to get data from the internet by using the importing web data feature.  Let us see how we can parse a webpage and use it for Power Query....Read More

Filter records is a feature of Power Query that allows you to filter the data from your data table. Let’s see how to filter the records in Power Query with an example. Filter Records By Power Query Step 1: Load Data in Power Query Sometimes the data table is too huge and you do not want...Read More

Power Query allows you to extract data from any source, clean and transform the data and then load it to another sheet within the Power BI Designer canvas. Unpivot Data With Excel Power Query One of the best features of the Power Query is to Unpivot Columns. Unpivot Columns feature transforms columns with similar characteristics...Read More

If you have multiple Excel worksheets that are in the same format and their underlying differences are their values and dates (e.g. January Sales List, February Sales List, March Sales List etc), then we can easily consolidate all the worksheets into one. Consolidate Multiple Excel Sheets By Power Query Let us understand this step by...Read More

The limitation of Excel of consolidating multiple Excel workbooks into one has overcome by Power Query.  Power Query can do this consolidation task in a couple of minutes. This is explained below within a few easy steps. Consolidate Multiple Excel Workbooks By Power Query Step 1: Open Excel Workbook from Folder Suppose you have an Excel...Read More

Sometimes, when you have a data table which includes full names of candidates in one column and you want to separate the full name into first name and surname in different columns. Split First & Last Name With Power Query This is a common task, but most people may use complex formulas to perform this...Read More

There is a cool feature of Power Query which is known as ‘Replace Values’ feature. As the name suggests, it can replace any value with the new value you provide into your data table. Power Query Helps In Replacing Values To understand this feature in detail, let’s take an example. We have a data set...Read More

Transposing a data set is ultimately rotating data from rows to columns, or from columns to rows. To understand this concept, an example with the picture is given below. Transposing Data By Power Query Let’s go through the steps in detail for transposing data. Step 1: Open the data in Power Query Open the data...Read More

Sometimes you need to show the data in a reverse form for some of the other reasons. It’s not easy to reverse rows without Power Query in Power BI. Power Query can do it in a fraction of second without losing any valuable information. Reverse Rows By Power Query Let’s take an example where you...Read More

Power Query allows you to group rows and get the counts of each group very easily. Group Rows And Get Counts By Power Query Let’s go through the steps in detail. Step 1: Select the column in Power Query To group rows, we first need to select the column from which we want to group...Read More

To simplify your data and aggregate them together we can Pivot the huge data table using Power Query Editor in Power BI. This amazing feature of Power Query has hundreds of benefits. We can use it for a data source that has too many numbers of columns and rows. Create Pivot Columns Using Power Query...Read More