Power bi dekstop

Hi Ayushman1997,
After thoroughly reviewing the details you provided, I have identified alternative workarounds that may help resolve the issue. Please follow these steps:

• Unpivoting can significantly increase the row count. To optimize, try the following before unpivoting: filter out unnecessary columns early to retain only essential ones, and if unavoidable, perform unpivoting in SQL or Power Query. If your data source is SQL Server or Azure SQL, conduct the unpivot operation in SQL before importing into Power BI.
• Deduplication at the Power Query level can be time-consuming. It is better to deduplicate data in SQL prior to loading it into Power BI. If you must use Power Query, use deduplication as early as possible to reduce data processing in later steps.
• Alternatively, use the Group By approach rather than the Remove Duplicates function. By partitioning data by columns like Col1 to Col7 and then using an aggregation function like Min or Max, you can deduplicate while optimizing performance.
• Instead of processing the whole 246M rows, please generate aggregated tables at different levels of granularity. Use summary tables for reporting and keep detailed data in a different dataset.
• Ensure both tables are sorted before merging. Merging unsorted large tables is expensive. Instead of merging, use relationships in the data model to connect tables.
• If possible, use a data warehouse to improve performance and manage large datasets efficiently. For Azure SQL Database scenarios, consider moving data to Azure Synapse Analytics and using Direct Query from there. This approach can improve query performance and reduce the load on Power BI by taking advantage of Synapse's scalable processing.

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Best Regards.