Data flow refresh error because of Merged tables (same source)

[NasreenE]

Hi

My data flow refresh has suddenly failed.

I have checked all the usual things like credentials and cache etc, only to discover that it is failing because of merged tables. The merged tables are from the same source ie both SharePoint.

I created a copy of that data flow updated the credentials and it still failed.

I then deleted the merged table from this copy and the refresh was a success.

I do need the merged table, but am unable to figure out how to fix my refresh issue. 

I really hope someone can help me.

Thanks 

Nasreen 

[christinepayton · Accepted solution]

When you do merges, it helps to "merge as new query" instead of merging into the existing query. I don't know if this is a bug or a feature, but that is the fix even if your source is the same (I have this happen occasionally too). Also run a "refresh all" in the query editor. 

[v-kpoloju-msft · Accepted solution]

Hi @NasreenE,

Thanks for reaching out to the Microsoft fabric community forum. Thank you @christinepayton, and @Akash_Varuna, for your inputs on this issue.

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

Thank you for your insightful observation regarding query folding issues during merges with SharePoint data sources. You are correct that SharePoint lists support query folding; however, certain transformations, such as complex merges, can disrupt this process, resulting in refresh failures.

To address this issue, you might want to simplify your merge operations or execute them within a Power BI Datamart. This platform offers server-side processing and can manage complex transformations more efficiently.

When merging large datasets, it is advisable to use a "Left Join" instead of a "Full Join" to reduce the number of records processed. Enhancing performance can also be achieved by reducing the number of columns in each table before merging, keeping only the necessary fields.

Furthermore, rather than using a Merge Query, consider using an Intermediate Table by loading both tables as separate entities in the dataflow and performing the merge in Power BI or a downstream process.

If the SharePoint list columns have been renamed or removed, the dataflow may fail during merging. Please manually refresh the preview in Power Query to detect any schema changes. If there are changes in column names, update them in the Merge Queries step.

Kindly refer the below following link for better understanding:
Understanding query evaluation and query folding in Power Query - Power Query | Microsoft Learn
Introduction to DataMart’s - Power BI | Microsoft Learn

Merge queries overview - Power Query | Microsoft Learn

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.

Thank you.

[v-kpoloju-msft]

Hi @NasreenE,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-kpoloju-msft]

Hi @NasreenE,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-kpoloju-msft]

Hi @NasreenE,

I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.

Thank you.

[christinepayton · Accepted solution]

When you do merges, it helps to "merge as new query" instead of merging into the existing query. I don't know if this is a bug or a feature, but that is the fix even if your source is the same (I have this happen occasionally too). Also run a "refresh all" in the query editor. 

[Akash_Varuna]

Hi @NasreenE It might be due to query folding breaking during the merge and since your source is SharePoint query folding would not be an option since you need that merged table could you do that in Datamart or try reducing the complexity of merge but if you could take a look at Datamart

[cecilie_sneber1]

I have the same problem. It started this night when the planned updates failed. The dataflows have been stable and functioning for a while, no reason that they should suddenly start failing. The only common denominator is merged queries. Even very small and simple merges fail. The data updates when in the power query editor for the dataflow, but the general update fails. The error message says its due to missing credentials, but we checked that. Removing the merges made the update function again, but that is not a viable solution. Is this a known issue starting today?