Refresh fails in DESKTOP but works in service, have to manually refresh some tables

[Moehimby]

Hey all, seems I'm like the only person to experience this but I have a big hairy report, it has so many tables and connections in it I've almost lost count.

It refreshes perfectly fine in the online service, takes about 8 minutes to fully refresh each morning. However, if I have to work with it I can't do a full refresh in desktop without it failing, and when it fails it usually sites and "error in step : merge columns in....", however if I open that table and step through it it's perfectly fine, no errors. Also, the error messages are different each time.

The only way for me to refresh in the desktop is to manually refresh tables one-by-one until it just works again and does a full refresh. It's very annoying.

Usually, I need to refresh the big FACT tables manually, and then the rest will work.

Here's a couple of points :

- Semantic model includes SQL tables, sharepoint excel sheets and Analysis Cube connections

- Refreshes perfectly fine online everyday

- I have to manually refresh single tables until it will finally allow a full auto refresh all

- Usually, after I have manually refreshed the FACT tables it will work

- The FACT tables are mostly all Analysis Cube connections (maybe this is why???)

It's really annoying and takes up so much time to have to manually refresh until it allows a full refresh. Help?!

[v-priyankata · Accepted solution]

Hi @Moehimby 
Can you please below points. it may helps you.
Troubleshoot refresh scenarios - Power BI | Microsoft Learn

1.  Turn off parallel loading, Go to File > Options >Data Load >Parallel Loading of Tables >Off. This makes Power BI refresh tables one by one instead of all at once, which helps prevent random errors.
2.  Try to avoid merging cube tables with non-cube tables directly in Power Query. If possible, do these joins in SQL or Excel first, or handle them using DAX.
3. Clear cache and restarting Desktop before a full refresh helps.
4.  For very large FACT tables, incremental refresh can reduce load and make refreshes much faster in Desktop.
   
   

   If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

   Thanks.

[Moehimby]

YYYYYEEEEESSSSS!!!!! Turned off parrallel loading and hit refresh expecting the usual disappointment but it WORKED right away. You can't imagine how much time this is going to save me every day.

How do I set up incremental refresh I'd like to add that too?

[v-priyankata]

Thank you for letting me know that your issue has been resolved. Please find the official document attached for your reference.
Configure incremental refresh and real-time data for Power BI semantic models - Power BI | Microsoft...

[Moehimby]

THANK YOU!

[v-priyankata]

Hi @Moehimby 

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

@samratpbi @danextian @DaleT Thanks for the inputs.

I hope the information provided by users was helpful. If you still have questions, please don't hesitate to reach out to the community.

[Moehimby]

Thanks for the welcome. Unfortunately there hasn't been any tips or info that help me resolve the issue yet.

[danextian]

I have one semantic model that does that. Before refreshing in desktop, I make sure that the preview has finished loading for queries involved.

[samratpbi]

Hi, I also face similar issues many times with large data models with many tables, relationships and reference tables and many Power Query transformations. I think when we try to do full refresh desktop tries to validate everything which consumes quite a few memories. I also noticed, recently this has increased compared to a few years back. Now, either due to my laptop is getting older or in recent Power BI versions, some additional things has been introduced.

[Moehimby]

Interesting I didn't think of this as my laptop is about 10 years old too. Funny thing is tho - I can still pull up web pages or write emails when it does refresh so it doesn't act like the machine is out of memory.

[DaleT]

Hi,

Can you share the full error message? Did you do a lot operations in Power Query? For example, join two or more tables, look up value from another table.

[Moehimby]

Error messages vary but are usually some variation of "Table Sales failed at step : Merge Columns 3", then all the other tables will show "Cannot refresh due to dependency on table Sales".

Yes, there are probably 20 tables in the model, most lookups are done via merging in power query, not dax.

[DaleT]

The interactions among tables could be the cause. I have a small model in which I do a lot of Power Query operations. Usually it fails the first refresh, but it succeeds when I try it again. It's hard to debug because it doesn't fail again after the first refresh. I would suggest pushing all the Power Query operations to the backend like MySQL or SQL Server.