Power bi dekstop

Hi Ayushman1997,

Thank you for reaching out to the Microsoft Fabric Community Forum.

After thoroughly reviewing the details you provided, here are a few workarounds that may help resolve the issue. Please follow these steps:

• Please ensure that only the necessary columns are included in your dataset and apply filters to minimize the data being loaded. Instead of importing all data into Power BI, use Direct Query mode to query data directly from the source, which can help manage large datasets more efficiently.
  Direct Query model guidance in Power BI Desktop - Power BI | Microsoft Learn
• Increase the maximum memory allowed for Power BI Desktop by navigating to File > Options and Settings > Options > Data Load and increasing the cache memory.
• Utilize Power BI’s Data Limit feature to set a maximum threshold for data loaded into a report. This helps prevent performance issues and focuses on the most relevant data. Implement incremental refresh to load only new or changed data, rather than refreshing the entire dataset.
• Write efficient DAX queries and avoid row-by-row calculations to enhance performance. Create aggregated tables to reduce the data processed at runtime. Consider splitting the large dataset into smaller, more manageable datasets.

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.

Hi there!

- Instead of unpivoting first, try to filter unnecessary columns and aggregate before unpivoting, you can use Remove Columns in Power Query for this. 

- You could also try using incremental refresh. You can enable Incremental Refresh in Power BI Service and then filter data dynamically in Power Query using this formula: Table.SelectRows(Source, each [Date] >= Date.AddMonths(Today(), -12)). Don't forget to set the partitioning so that Power BI loads data in chunks instead of one large load.

Hope this helps!

😁😁

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.