cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tony_jenkins
Helper I
Helper I

Table from Salesforce won't refresh due to a missing column on the object

I'm trying to update a table from Salesforce, however, each time I try, the refresh window gets to the point that it says "3,638 rows loaded", then it cancels and throws this error:

 

"Column 'Sales_Rep__c' in table 'Project' cannot be found or may not be used in this expression."

 

I'm not sure why this column is relevant to my query, as I'm not trying to pull it into my table, and I've never referenced it in my query. I assume the field was removed from this object in Salesforce, but I'm not sure why that would obstruct my query from refreshing.

 

Any help would be awesome. This is the most important table in my file at the moment. 

 

Thanks.

 

36 REPLIES 36
lbendlin
Super User III
Super User III

not loading at all, or still breaking ?  After what time? After how many rows?

Says they're "loaded" (I think it was 3,715 rows), but the load doesn't actually complete. Before it completes, I get this error:

 

"Failed to save modifications to the server. Error returned: 'There's not enough memory to complete this operation. Please try again later when there may be more memory available. '."

 

I got the PPU license and confirmed it's live for my account. 

lbendlin
Super User III
Super User III

Power Query now supports some rudimentary query folding for SFDC Object queries. You can limit the returned columns and apply datetime filters.  Full blown custom SOQL this is not, but it's a start.

lbendlin
Super User III
Super User III

Yes it will work but you will have to pay for that. Separately.

 

Premium SKUs have 25, 50 and 100 GB dedicated (P1,P2,P3)

 

How many items do you have in the Project__c object?

Coming back to this. I was able to get the PPU license, but the table still won't load. Any idea why?

I'm bringing in 49 attributes for that object from SFDC, I then have 2 conditional columns added in Power Query, then I have 74 other columns where I'm using formulas. Many of these can probably be deleted, as their obsolete. 

lbendlin
Super User III
Super User III

Frankly I have no idea how shared capacity works - I only know premium.

 

SFDC Objects don't have columns, they have attributes.  Which object are you connecting to?

 

Have a look at the recently announced "Premium Per User"  liense offering.

I may be over-simplifying this, since I'm not an expert in memory, etc., but if I go to premium per user, and my model size limit goes from 1GB to 100GB, it feels  like that is a solution to this particular error. 

I mean columns in terms of the table in Power BI. I have several calculated columns in the table I'm having trouble with. 

 

We have a Project__c object in SFDC, that's the one I can't get to load.

 

I'll take a look at that. Does Premium increase memory for these kinds of operations? And you mentioned SKU, is there a preferred one?

lbendlin
Super User III
Super User III

That's too bad.  You are on shared capacity, and there's no way to "add more memory".  You need to reduce the amount of data you pull.

This table seems to be the only failing to refresh, do I just need to reduce the amount of data on this one only? Also, 3,600 or so rows seems small, is it the amount of columns that mostly needs the reduction?

 

Last question - can an individual in a company upgrade to premium or does it have to be a company wide decision?

lbendlin
Super User III
Super User III

What capacity are you on, shared or Premium? If Premium, what SKU?

I don't believe it's premium. I went to the admin portal, and it asks if I want to upgrade to premium so pretty sure it isn't that. I think it's Pro. 

lbendlin
Super User III
Super User III

You said the error occurred after "3,638 rows loaded"  .  Do you know how many rows you are actually expecting?

@lbendlin 

 

I found the measure that was causing the issues, but now when I refresh, I get this error:

 

"Failed to save modifications to the server. Error returned: 'There's not enough memory to complete this operation. Please try again later when there may be more memory available. '."

 

Any idea what may be happening now? This seems like a pretty generic error, so I have no idea how to troubleshoot.

I don't know the specific number, but that number sounds right. The last time I loaded without error, there were 3,628. Power BI says they're "loaded", but the table doesn't actually refresh with the new rows.

lbendlin
Super User III
Super User III

Certainly possible.  Check for constructs like 

 

IF (CALCULATE(...))

I'll look for that specifically. Column4 has that construct for instance. 

 

You said earlier you didn't think it was failing to load. Why do you believe that?

lbendlin
Super User III
Super User III

On first glance this looks relatively harmless.  If your CALCULATE filters come from the same table then you can simplify your formulas

 

Column1 = 
Calculate(
    Sum(Table1[Revenue]),
    Filter(Table1,Table1[MonthSpent]=[CMREV]),
    Filter(Table1,Table1[ProjectID]=Project[Id]))
+
Calculate(
    Sum(Table2[Revenue]),
    Filter(Table2,Table2[MonthSpent]=[CMREV]),
    Filter(Table2,Table2[ProjectID]=Project[Id]))

can become 

Column1 = 
Calculate(Sum(Table1[Revenue]),Table1[MonthSpent]=[CMREV],Table1[ProjectID]=Project[Id])
+
Calculate(Sum(Table2[Revenue]),Table2[MonthSpent]=[CMREV],Table2[ProjectID]=Project[Id])

 

I would comment out/remove Column8 first - that looks most suspicious.

Removed Column8 and got the same result. Could the issue be coming from calculated tables that are dependent on this table?

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.