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.
Data Analytics Tricks in Power BI – Guidebook
You too can analyze data like a Data Scientist. No coding needed. No statistics needed.
Analyze & Visualize data using Power BI. (23 tricks in one book)
Top 51 Power Query Tips and Tricks
1. Connect to Data in Power Query
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.
2. Keep Duplicate Records
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.
3. Rename a Column
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.
4. Merge Columns
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
5. Append Queries
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.
6. Format Columns
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.
Data Analytics Tricks in Power BI – Guidebook
You too can analyze data like a Data Scientist. No coding needed. No statistics needed.
Analyze & Visualize data using Power BI. (23 tricks in one book)
7. Extract Age Using Date
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.
8. Split Column by Number of Characters
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.
9. Duplicate Columns
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.
10. Use First Row as Headers
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
11. Remove Columns
Another feature of Power Query is Remove Columns. This feature allows you to remove the unwanted columns easily.
12. Remove Duplicates
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.
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.
14. Create Index Column
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.
Data Analytics Tricks in Power BI – Guidebook
You too can analyze data like a Data Scientist. No coding needed. No statistics needed.
Analyze & Visualize data using Power BI. (23 tricks in one book)
15. Remove Rows with Errors
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
16. Create Pivot Columns
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.
17. Group Rows and Get Counts
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
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.
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
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.
21. Split First & the Last Name
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.
Data Analytics Tricks in Power BI – Guidebook
You too can analyze data like a Data Scientist. No coding needed. No statistics needed.
Analyze & Visualize data using Power BI. (23 tricks in one book)
22. Consolidate Multiple Excel Workbooks
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.
23. Consolidate Multiple Excel Sheets
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.
24. Unpivot Data using Excel Power Query
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.
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
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
27. Import Data From XML
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!
28. Import Data from CSV
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!
29. Import Data from Text
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
30. Add Conditional Column
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.
Data Analytics Tricks in Power BI – Guidebook
You too can analyze data like a Data Scientist. No coding needed. No statistics needed.
Analyze & Visualize data using Power BI. (23 tricks in one book)
31. Add Column From Examples
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
32. Concatenate Data using M
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.
33. Replicating Excel’s Find function with 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.
34. Get the length of Strings using M
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
35. Use Excel’s Right function with M to Extract Right Side Data
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
36. Use Excel’s Right function with M to Extract Left Side Data
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.
37. Datatype Conversion with M
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.
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
39. Delete Steps until End
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
40. Display a Monospaced Font
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.
41. Adding Comments in M
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
42. Comments in Query Steps
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.
43. Creating a list Query from a Table Column
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
44. Manage Parameters
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
Data Analytics Tricks in Power BI – Guidebook
You too can analyze data like a Data Scientist. No coding needed. No statistics needed.
Analyze & Visualize data using Power BI. (23 tricks in one book)
45. 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. 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.
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
47. Split queries in Power 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.
48. Navigate Query Editor
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
= Source{[Item=”Sheet1″,Kind=”Sheet”]}[Data]
49. Disable Relationship Detection
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.
50. Create a Sequential list
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-
{lowest_integer..highest_integer}
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.
Data Analytics Tricks in Power BI – Guidebook
You too can analyze data like a Data Scientist. No coding needed. No statistics needed.
Analyze & Visualize data using Power BI. (23 tricks in one book)