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

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
Super User

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
Super User

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
Super User

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
Super User

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
Super User

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
Super User

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
Super User

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
Super User

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
Super User

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
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through June10th!

Power BI Dev Camp Session 22 without aka link and time 768x460.jpg

Check it Out!

Watch Session 22 Ted's Dev Camp along with past sessions!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!