Its completely depend on the steps you apply, but if you use UI and the tasks took such amount, I recomend you consider the below points:
Minimize Data Load
Filter Early: Apply filters as early as possible in your query to reduce the number of rows processed in subsequent steps.
Select Relevant Columns: Use the "Choose Columns" option to remove unnecessary columns right after importing data.
2. Optimize Query Design
Disable Background Preview: Disable "Enable Background Data Preview" in Power Query options to prevent unnecessary refreshes while editing.
Use Table.Buffer: For sorted or grouped operations, wrap the data in Table.Buffer to avoid re-evaluating steps multiple times.
Avoid Repeated Steps: Combine transformations into fewer steps to reduce dependency chains.
3. Leverage Native Queries
Push Operations to Source: Use SQL queries or other source-side operations (if supported) to filter, group, or transform data before it reaches Power Query.
Direct Query: When connecting to databases, write native SQL queries to retrieve only the required data.
4. Reduce Complexity
Avoid Nested Functions: Minimize the use of nested or custom functions in steps, as they can be slower with large datasets.
Simplify Logic: Break down complex logic into smaller, more efficient operations.
5. Manage Data Types
Set Data Types Once: Apply data type changes in the final steps, as Power Query evaluates this operation each time it's performed.
Remove Duplicates Smartly: Use deduplication only on necessary columns instead of entire rows.
6. Handle Merging and Joining Carefully
Sort Before Merge: Sort tables before merging to improve performance.
Reduce Table Size for Joins: Pre-filter data to include only rows that are necessary for merging.
7. Aggregate Efficiently
Group By Wisely: Avoid grouping by too many columns, and aggregate only the necessary metrics.
8. Use Power Query Settings
Increase Data Cache Memory: Go to Query Options > Data Load and increase cache memory to improve performance for large datasets.
Enable Parallel Loading: Allow parallel loading of tables in the query options.
9. Test with Sample Data
Work with Smaller Data Samples: Use a filtered subset of data while designing queries, then apply the full dataset for final execution.
10. Review Performance Metrics
Use Query Diagnostics: Analyze performance bottlenecks using Power Query's built-in Query Diagnostics feature.
Monitor Data Refresh Times: Identify steps that take the most time and optimize them.
If my answer helped solve your issue, please consider marking it as the accepted solution. It helps others in the community find answers faster—and keeps the community growing stronger!
You can also check out my YouTube channel for tutorials, tips, and real-world solutions in Power Query with the following link
https://youtube.com/@omidbi?si=96Bo-ZsSwOx0Z36h
