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

[Nisha_Goyal]

Hi Team,

I'm facing issue while merging queries in Power BI. I have applied multiple appeend queries and merge Queries. Data source is two different i.e Azure Databricks and Sharepoint Folder.

What can we prefer solution in this case?

Thanks,
Nisha

[v-saisrao-msft]

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.

[v-saisrao-msft · Accepted solution]

Hi @Nisha_Goyal,

Please try using a Power BI Dataflow in the service to perform your merges and appends this can help offload the heavy processing from Power BI Desktop and may resolve the memory allocation issue.

Memory error: Memory Allocation failure. Try simpl... - Microsoft Fabric Community

Solved: Memory error: Memory Allocation failure on Power b... - Microsoft Fabric Community

Thank you.

[v-saisrao-msft]

Hi @Nisha_Goyal,

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

Thank you.

[v-saisrao-msft]

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.

[Nisha_Goyal]

Issue still there i tried given options

[Cookistador]

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

[MattiaFratello]

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 👍

[Ritaf1983]

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