Learn Power Query Tips and Tricks. 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.
Top 51 Power Query Tips and Tricks
Power Query is the Microsoft Data Connectivity and Data Preparation technology that allows the users to flawlessly access data stored in hundreds of data sources and reshape it to fit their needs. You can read more on How to Make Power Query Connection Only.
Power Query in Power BI allows you to perform a series of steps to transform your data. One of the steps amongst this is to how to find duplicate values in Power Query. We generally take out duplicate lines but if we need to keep and check what the duplicates are, Power Query allows us to do that. Read More on how to find duplicate values in Power Query.
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. You can get more information on Rename Column Name in Power Query.
Power Query Tips and Tricks 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:
Select the Column in Power Query > Merge Columns > Add Separator to the Merged Column
Get more information on how to merge columns in Power Query.
You can even append queries in Power Query. This feature is very useful if you want to combine two data tables together. For Example, we can combine any two data tables together by using this feature. For additional information click here on How to Append data in Power Query.
Another important feature of Power Query is Formatting Columns. There are various formatting options available in Power Query for columns like Trim, Clean, Add Prefix, Uppercase etc. You can get additional information on how to format columns in Power Query.
This is an amazing feature as it allows you to extract the age out of the date provided. Power Query Tips and Tricks Let us elaborate on this feature using an example. Here, we have a data table containing employee information which includes their Date of the Birth column. If you want to calculate the age of each employee it will consume a lot of time. To overcome this, Power Query has a solution which can solve this problem within a fraction of second. To know in details about it click here how do I calculate age from a specific date in power query?
Power Query helps you to perform a series of steps to modify your data. One of the steps is to split the column by the number of characters easily. You can read more on how to split one column into multiple columns in power query.
You can also duplicate the columns easily using the Power Query Editor in Power BI. This can be helpful when you have columns that you want to duplicate & make some temporary/permanent alterations to it in the Power Query Editor but not to your source data. For more details click here on duplicate columns power query.
One of the features of the Power Query is that it allows you to promote the first row as column headers. You can use this feature easily by following the below steps:
Load the data table in power query editor > Use the first row as headers
For additional information check here how do I make the first row in power query a header.
11. Remove Columns
Another feature of Power Query is Remove Columns. This feature allows you to remove the unwanted columns easily. You can get additional information on power query to remove columns.
Power Query cannot only keep duplicate records but also can remove the duplicate data. Power Query Tips and Tricks The advantage of this feature is that it removes the human error whenever you try to delete your duplicate data manually. Read More on how to remove duplicates in Power Query.
13. Fill Down Values
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. Get additional information on how to fill blank cells with value above in power query.
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. You can read more on create table with an index.
Do you know that Power Query can clean your data and remove rows that have inaccurate data? You can use this feature easily by following below steps:
Load the data in Power Query Editor > Remove Errors
For more information click here power query remove rows with the specific value.
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. You can know more here how to create a pivot column in Power query.
Power Query allows you to group rows and get the counts of each group very easily. Use this feature easily by following below steps:
Select the column in power query > Grouping the rows
To know more click here select count of groups.
18. Reverse Rows
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. For more details click here now inverted row benefits.
19. Transpose Data
Transposing a data set is ultimately rotating data from rows to columns, or from columns to rows. Let’s go through the steps in detail for transposing data.
Open the data in Power Query > Transpose the data table > Use first rows as headers
You can read more on the transpose power query.
20. Replace Values
There is a cool feature of Power Query which is known as the ‘Replace Values’ feature. As the name suggests, it can replace any value with the new value you provide into your data table. For more details click here on how to replace values in power query.
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. With Power Query, you can split the full name i.e. the First name in one column and Surname in the second column within a few clicks. Read more here split first & the last name using power query.
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. For more details click here now how can I consolidate multiple excel workbooks in power query.
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. You can read more on power query consolidate multiple excel sheets.
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. One of the best features of the Power Query is to Unpivot the Columns. Unpivot Columns feature transforms columns with similar characteristics and puts them in a tabular format, which then helps you to do further analysis. For more details click here now how to unpivot data using excel power query.
25. Filter Records
Filter records is a feature of Power Query that allows you to filter the data from your data table. Let’s go through the steps in detail to filter records. Power Query Tips and Tricks.
Load Data in Power Query > Filter data > Filter Text
You can read more on how do I filter record in power query
26. Import Web Data
The good thing with Power Query is that there are multiple ways to pull in data. One of the ways is to get data from the internet by using the importing web data feature. Let’s go through the steps in detail to import web data.
Get Data from Web > Enter URL to connect Web page > Select data from Navigator >
Remove Top Rows > Remove Selected Column > Use First Row as Header
For more details click here now Import web data power query.
You can easily load the data files like Excel spreadsheets in Power Query. But what if your data source is not in an Excel spreadsheet? 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 XML file, it’s very easy to import data into the Power Query! You can read more on how to import data from XML in power query.
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 into Power Query! For more details click here now Import Data from CSV Using Power Query.
As you can import the data from CSV, the same way you can import the data from the text file. Power Query Tips and Tricks The reason is that Power BI provides the same option to import the CSV file as well as the text file. Let’s go through the steps in detail to import data from text.
Get data from Text > Link the Text data into Power Query
For more details click here now power query import data from text.
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. You can read more on how to add conditional column using power query.
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. You can use this feature easily by following below steps:
Select the column on which you want to apply changes > Add Column > Make changes
You can read more on how to add column in power query.
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. Initially, we will do a simple example of merging the first name and the last name into a new column. This can be done with the help of the CONCATENATE formula. For more details click here now power query concatenate data using M.
In the above example, you learned how to concatenate the columns using the M language. Power Query Tips and Tricks, Unfortunately, not all of the Excel’s formulas can be used in M query. For instance, if we want to use the Excel’s FIND function to find a specific character in the text, it is not supported in M. You can read more here how to replicate excel’s find function in power query.
If we want to use the Excel LEN function to get the length of strings, it is not supported in M. You can use this feature easily by following below steps:
Select the Name Column > Add Custom Column > Get the Length of String using M Language
You can read more on getting the length of the string in power query.
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! Let’s go through the steps in detail to import data from text.
Select the Column for Extracting Right Side Data > Add Custom Column > Extract Right Side Data
For more details click here how to use excel’s right function with m to extract right side data in power query.
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. But there is an alternate way for using the LEFT Function in M which is explained below. You can read more on how to use excel’s right function with m to extract left side data in 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. One of the unique functionality of M is that it takes data types very seriously. If we try to concatenate a text with a number, it will result in an error. Using Power Query M you can overcome this issue. To know more in detail click here data type conversion with m in power query.
38. Advanced Editor
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? Let’s go through the steps in detail for the advanced editor :
Load the data into Power Query Editor > Add Index Column > Rearrange the Index Column > Change Header Type > View the steps applied in the Applied Steps Window > Open Advanced Editor
You can know more here power query advanced editor.
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. Power Query Tips and Tricks You can use this feature easily by following below steps:
Load the data and add Index Column > Delete Steps until End > Delete Steps
You can read more on power query delete steps until the end.
Normally data is viewed in the Unicode Font for any kind of data. But do you know you can also change this font to a monospaced font? Power Query has a solution for this also. If you want to use another font, other than Unicode font, then follow the below steps. To know more in detail click here how to display a monospaced font in power query.
You can add comments to your code either with single-line comments that start with ‘//’ or multi-line comments that start with ‘/*’ and end with ‘*/’. Let’s go through the steps in detail to add comments in M. Let’s go through the steps in detail to add comments in M.
Load Data in Power Query > Open Advanced Editor > Add Comments in M
You can read more on power query add comments in M.
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 as descriptions in the Applied Steps pane. To know more in detail click here how to do comments in query steps.
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. You can use this feature easily by following below steps:
Select the Column for creating List Query > Create a List Query
You can read more on creating a list query from a table column in power query.
Power Query can create a query parameter and link to the List Query. This is one of the most unknown features of Power Query. You can use this feature easily by following below steps:
Create a List Query to Manage Parameters > Select Manage Parameters option
To know more in detail click here how to manage parameters 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. But we want to keep the same serial number for the product each time it appears. This user requirement can be fulfilled by Power Query very easily. You can read more on create grouped serial numbers using power query.
46. Editing a Query
Power Query also allows you to edit a query that you have already generated. Let’s go through the steps in detail to edit a query.
Select the ‘Experience’ column > Check your data after applying Conditional Column >
Refresh Data after updating Data Table > Editing the Query
To know more in detail click here how can I edit a query.
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. The user will be able to mark a set of consequent steps, starting at the first one, and split the query so the marked steps become a new query and the rest become a query referencing the first one as a source. You can read more on power query split queries.
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?
The code generated by this step is
To know more in detail click here how to navigate query editor 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. Thus, it will take extra processing time and will slow down your queries. You can read more on power query relationship detection disable.
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-
For more details click here how to create a sequential list in power query.
These are the top 51 amazing Power Query Tips & Tricks that you can use for day to day life Data Cleaning and Data Transformation Techniques. If you wish to learn more about the Power Query Functions and their practical applications, then Check Our premium Power Query Course with lifetime access and 24×7 support.