8 Power Query Tricks That Will Save You Hours of Work

Power Query is one of the most innovative and useful tools that offer data connection, transformation, summarization, and so on. It automates repetitive tasks and manages data efficiently. In this article, we will share 8 Power Query tricks that will save you hours of work.

1. Combine Multiple Files from a Folder

You can combine multiple files from folders to consolidate them into a single dataset. You may have monthly sales reports in separate Excel files. You can create a year report by combining the sales of the months in a single dataset.

Steps:

• Go to the Data tab >> from Get Data >> select From File >> select From Folder.

• Select the Folder from the stored location >> click Open.

• Power Query will list all the files. Click Combine & Load to merge them.

• You can use Transform Data to clean the combined dataset (if necessary).

All your sales files are merged into one table, now you can use it for further analysis.

2. Split Columns by Delimiter

Power Query has the direct feature to split columns by delimiter. Suppose you have a column with full names and want to split it into First Name and Last Name. You can do it with one click.

Steps:

• Select the column with names.
• Go to the Home tab >> select Split by Column >> select By Delimiter.

• Select or enter delimiter Space >> click OK to split at each occurrence.

You will get two new columns one for First Name and another for Last Name.

3. Fill Down the Magic Trick for Blank Cells

In a large dataset, it is common to have some blank cells for repetitive data. You can fill those cells using the Fill Down feature instead of manually filling these values.

• Select the column with blank cells.
• Right-click on the column >> select Fill >> select Down.

This automatically copies values down to empty cells below them. It’s especially useful for hierarchy data.

4. Quick Clean-up with Transform Menu

Transform offers commands to clean and format the dataset. Instead of creating custom formulas, you can use these one-click transformations.

To remove extra spaces:

• Go to the Transform >> from Format >> select Trim.

To fix the letter case:

• Go to the Transform tab >> from Format >> select UPPERCASE/lowercase/Capitalize Each Word.

To remove special characters:

• Go to the Transform tab >> from Format >> select Clean.

To remove rows:

• Go to the Transform tab >> from Remove Rows >> select Remove Duplicates.

These simple transformations can clean incorrect messy data in seconds rather than minutes of manual work.

5. Group By Shortcut for Quick Summaries

You can group by data in Power Query instead of creating pivot tables for quick summaries:

• Select the column.
• Go to the Home tab >> select Group By.
• In the Group By dialog box;
  • Type New column name >> Choose what Operation you want (sum, average, count, etc.) >> select Column.

This creates summary tables in seconds, perfect for quick reports and analysis.

6. Filter Rows Dynamically

Power Query’s filter shows a preview before filtering data. It shows you how many rows match your filter before you apply it. This preview helps you catch mistakes before they happen

• Click the filter icon on any column.
• Select filter conditions and watch the row count at the bottom.
• If the numbers look wrong, adjust your filter before applying.

This prevents the need to undo and redo filters multiple times.

Let’s filter sales data show only rows with sales greater than or equal to 3000.

Steps:

• Select the Filter Icon of the Profit column.
• Choose Number Filters >> select Greater Than Or Equal To >> click OK.

• Enter 3000 as the filter condition and apply.

Only rows with sales above 3000 will remain visible.

7. Fix Country Specific Formatting

You can easily use country-specific formatting. Different countries use different formatting, especially for dates and numbers.

Dates can be another tricky situation. In the US they use a mm/dd/yyyy format while the rest of the world uses the dd/mm/yyyy or yyyy-mm-dd format.

Steps:

• Select the Date or Number column.
• Click on the column icon >> select Locale.

• In the Change Type with Locale dialog box;
  • Choose Data Type >> select Locale >> click OK.

You will get the Date in German format.

8. Create Custom Columns with M Code

You can add a column with calculations by using M code. Let’s calculate a 10% tax on profit.

Steps:

• Go to the Add Column tab >> select Custom Column.
• In the Custom Column dialog box >> type New column name >> enter Custom column formula:

=[#"Profit "]*0.01

• Name the column Tax and click OK.

A new column shows tax values based on the profit.

Additional Tips:

You can use Choose Columns Instead of Remove Columns.

• Go to the Home tab >> select Columns >> uncheck Select All Columns.
• Only check the columns you need.

Conclusion

Power Query has plenty of features and commands, but these 8 Power Query tricks will help you to automate data cleaning, transformations, and calculations. You can try these tricks one at a time on your actual work data. These tricks will help you to work smartly to streamline your workflow.