Data Load Issue using Multiple tables append and Multiple left merge queries

Hi Nisha_Goyal 

Power BI’s ETL engine (Power Query) isn’t optimized for heavy transformations or large multi-source merges. It loads all intermediate results into memory, which can easily cause memory allocation failures when combining tables from different sources such as Azure Databricks and SharePoint.

Recommended approach:

Perform all heavy joins, merges, and appends before Power BI — directly in Databricks, SQL Server, or another database.

 

Keep Power BI for lightweight transformations only (renaming, filtering, formatting).

If pre-processing isn’t possible, try breaking queries into smaller parts, disabling query load for intermediate steps, and using staging tables or Dataflows.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

Memory allocation failure appears cause there is too much data.

You should try to import less (less columns, less rows).

You can try to run Performance Analyzer in PBI Desktop to see if you have any bottlenecks.

You can also try to split big merges/appends in multiple steps. 

Another idea could be incremental refresh -> https://learn.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-overview

Are you using PBI Pro or Premium Per User or more?

 

If this helps feel free to mark it as a solution and give a kudos 👍

Here, the issue is related to the memory of your Power BI desktop

 

My advice would be to load a smaller dataset for the reporting purpose with a paramter, then you published your report and you modify the parameter to get the whole dataset

 

To achieve that, this is the steps I followed:

 

• In the Power Query Editor, go to the Home tab.

• Click Manage Parameters > New Parameter.

• Create a new Decimal parameter, the idea if this value = 0, Power query will load the whole dataset 
  So for a first test, set this value to 1000

Then 

 

• Open the Advanced Editor (Home tab > Advanced Editor).

• Find the last step of your query before the final in statement.

• Add a new step after it to apply the conditional limit.

 

 

ConditionalLimit = if Amount = 0 then
                           #"Changed Type" 
                       else
                           Table.FirstN(#"Changed Type", Amount)

in
    ConditionalLimit

Note: in my case, I called my variable Amount and the step juste before the last step was #'Change Type', in your case, you have to replace it by the name of your previous step

 

After that you published your report and in Power BI services, you modify the parameter to 0

 

Once the whole dataset is punlished, you can also use an incremental refresh to get better performance

 

Hi Nisha_Goyal,

Have you had a chance to review the solution we shared by Ritaf1983 MattiaFratello Cookistador ? If the issue persists, feel free to reply so we can help further.

 

Thank you.

Hi Nisha_Goyal,

Checking in to see if your issue has been resolved. let us know if you still need any assistance.

 

Thank you.

Hi Nisha_Goyal,

Have you had a chance to review the solution we shared earlier? If the issue persists, feel free to reply so we can help further.

 

Thank you.