Power bi dekstop

Hi  Ayushman1997 ,

Here’s a breakdown of what usually works best in real-world scenarios:

1. Always filter and trim before unpivoting or merging. Don’t wait until after unpivot to remove unnecessary rows/columns do it as the very first step. If you can, filter at the SQL/source level before Power BI even touches the data.

2. Unpivot only the columns you actually need. The fewer columns you unpivot, the less Power Query has to process and the less memory is used.

3. When merging, always buffer the smaller table. Use Table.Buffer() on the smaller table before the merge. Don’t buffer massive tables, it can actually make things worse.

4. Remove duplicates as early as possible. If you need to deduplicate, do it before merging. If your source supports it (SQL, Python, etc.), do the deduplication outside of Power BI.

5. Use DirectQuery or Dataflows for really large data. If your source is a SQL DB, see if DirectQuery is an option (it avoids loading everything into memory). Or use a Power BI Dataflow if your org allows, it’s more scalable than desktop Power Query for big crunches.

6. Disable load for intermediate queries in Power Query. Right-click on queries that are just steps, and select “Enable Load” OFF this can save a ton of RAM.

7. Try splitting your transformation into stages. If possible, pre-process your data into smaller, “staged” files or tables. Sometimes breaking one huge import into smaller parts solves the problem.

8. Consider incremental refresh or partitioning (Premium/Fabric only). If your report will keep growing, look at incremental refresh to load in chunks.

Example M code (pattern):

// Filter at the very start
Filtered = Table.SelectRows(Source, each [Col1] <> null and [Col2] <> null),
// Unpivot only needed columns
Unpivoted = Table.UnpivotOtherColumns(Filtered, {"Col1", "Col2"}, "Attribute", "Value"),
// Buffer only the small table before merge
SmallTableBuffered = Table.Buffer(OtherSmallTable),
// Merge
Merged = Table.NestedJoin(Unpivoted, "Key", SmallTableBuffered, "Key", "NewData", JoinKind.LeftOuter),
// Expand & deduplicate
Expanded = Table.ExpandTableColumn(Merged, "NewData", {"NewCol"}),
Deduped = Table.Distinct(Expanded)
in
Deduped

Bonus Tips: Close all other heavy apps (Chrome, Teams, etc.) when you’re applying steps. If you have access to a machine with more RAM, try running Power BI Desktop there. Sometimes, it’s honestly faster to do some “pre-crunching” in Python, R, or even Excel, and import the cleaned file into Power BI.