Power Query is one of the most powerful features in Power BI and the easiest to learn. It is used for cleaning & transforming the data downloaded from ERP or accounting systems and display it in a report for Power BI to work with. You can learn more tips & tricks in our Power Query...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

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

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

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

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

Do you know that Power Query can clean your data and remove rows that have inaccurate data? If you are not aware of this feature then go through the below steps. Remove Rows With Errors By Power Query Let’s take an example. As shown in below picture there is some revenue that has invalid values....Read More

As explained in the above example, we have generated a conditional column for the given query. If we want to split this query into two, Power Query will help us in doing so. How to Split Queries In Power Query The user will be able to mark a set of consequent steps, starting at the...Read More

There is one more interesting feature of Power Query where it lets you add an index column that serves as a row counter to your data. Power Query Helps To Create Index Column Let’s go through the steps in detail with an example. Our sample data consists of Months and revenue data as shown in the...Read More

Power Query cannot only keep duplicate records but also can remove the duplicate data. The advantage of this feature is that it removes the human error whenever you try to delete your duplicate data manually. Remove Duplicates by Power Query Suppose you have this set of data. As you can see the marked ones are...Read More

Power Query helps you to perform a series of steps to transform your data. Among which there is a step that allows you to fill data down easily. This feature is specially used to fill the missing data where predictions are available for the missing data. Power Query Helps To Fill Down Values Let’s suppose...Read More

Another feature of Power Query is Remove Columns. This feature allows you to remove the unwanted columns easily. Learn Steps to Remove Columns By Power Query This is helpful when you have columns that you want to eradicate and do not need in your final report – but do want to keep in your source...Read More

One of the features of the Power Query is that it allows you to promote the first row as column headers. Let’s take an example where you have the data set as shown below. Use First Row As Headers By Power Query Here, the headers are not meaningful as it is showing the headers as Column1,...Read More