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

[Anonymous]

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.

[lbendlin]

start with the table that seemingly fails to load  (I don't think it does, but we'll see).  Let's take one step after the other - no worries.

[Anonymous]

Ok, I'll try to do my best to paste all the formulas that use "calculate" from this table here. There are quite a few, as I've sort of been learning as I go, so some of these are obsolete, and I haven't done a great job of cleaning up.

I'm also using generic names like "table1" in place of more business specific terms (I believe that's what you mean by sanitize).

You've picqued my curiosity in that you don't think it fails to load. Do you have a working theory about what's actually happening?

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]))

Column2 = 
Calculate(
    Min(Table1[Date]),
    Filter(Table1,Table1[Onboarding_Project__c]=Project[Id]),
    Filter(Table1, Table1[Date] >= Project[StartDate]))

Column3 = 
Calculate(
    Sum(Table1[RevenueAtTransition]),
    Filter(Table1,Table1[Onboarding_Project__c]=Project[Id]))
+
Calculate(
    Sum(Table2[Revenue]),
    Filter(Table2, Table2[ProjectID] = Project[Id]),
    Filter(Table2, Table2[MonthSpent] = Date(Year(Project[Closed_Date__c]), Month(Project[Closed_Date__c]), 1)))

Column4 = 
IF(
    (Calculate(
        Sum(Table1[RevenueAtTransition]),
        Filter(Table1,Table1[Onboarding_Project__c]=Project[Id]))
        +
    Calculate(
        Sum(Table2[Revenue]),
        Filter(Table2,Table2[ProjectID]=Project[Id]),
        Filter(Table2, Table2[MonthSpent] = Date(Year(Project[Closed_Date__c]), Month(Project[Closed_Date__c]), 1))))  
            > Project[Value], Project[Value],
    Calculate(
        Sum(Table1[RevenueAtTransition]),
        Filter(Table1,Table1[Onboarding_Project__c]=Project[Id]))
    +
    Calculate(
        Sum(Table2[Revenue]),
        Filter(Table2,Table2[ProjectID]=Project[Id]),
        Filter(Table2, Table2[MonthSpent] = Date(Year(Project[Closed_Date__c]), Month(Project[Closed_Date__c]), 1))))

Column5= 
Calculate(
    Min(Table1[Date]),
    Filter(Table1,Table1[Account__c]=Project[Account__c]))

Column6 = IF(OR(Project[Date]=blank(),Project[Value]=blank()),blank(),
            Calculate(
            SUM(Table1[Revenue]),
            Filter(Table1,Table1[ProjectID]=Project[Id]),
            Filter(Table1,Table1[MonthSpent]=Dateadd(Project[StartDate],14,MONTH))
            ))

Column7= 
CALCULATE(
    CountA(Table1[Id]),
    Filter(Table1, Table1[Project__c] = Project[Id]))

Column8= 
IF(
    CALCULATE(
        COUNTA(Table1[Id]),
        Filter(Table1, Table1[First] = TRUE),
        Filter(Table1, Table1[Project__c] = Project[Id]),
        Filter(Table1, Table1[EndTime] <> blank())) 
        >= 1, 1, 0)

Column9 = 
IF(Project[Closed] = 0, Blank(),
DATEDIFF(
    Project[StartDate], 
    Calculate(
        FIRSTNONBLANK(Table1[EndTime], 1),
        Filter(Table1, Table1[Project__c] = Project[Id]),
        Filter(Table1, Table1[First] = TRUE),
        Filter(Table1, Table1[EndTime] <> blank())),
    DAY))

[lbendlin]

can't help much without seeing the formulas (or equivalents)

[Anonymous]

I'm happy to paste the formulas, I just don't know which to paste. Will they be in the table failing to load or could they be in a calculated table created off the table that won't load? I just don't know where to start.

[lbendlin]

should be easy - it's the formula that contains "CALCULATE"  🙂 

[Anonymous]

This is the full error when i'm refreshing the table:

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. A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed. '.

[Anonymous]

Right. I have several of those in this particular table. Any way to identify which is one giving me the error?

[lbendlin]

can you show sanitized versions of the formulas?  For the calculated columns I assume, unless the error appears after rendering has started.

[Anonymous]

I think it's occurring after. It says "5,638 rows loaded" then it give the error, so it feels like there's a formula in the file dependent on that table that can't calculate properly. I just don't know how to locate it and fix it.

[lbendlin]

change that part of the formula slightly to have a 1/0 result instead of True/False.

[Anonymous]

Thanks. My issue is I'm not sure what formula is giving me that error. It doesn't specify the field that's producing the error. It's just preventing the table from refreshing. I may be explaining poorly. 

[Icey]

Hi @Anonymous ,

Could my suggestions solve your problem?

If it could, please accept my reply as solution to your question so that people who may have the same question can get the solution directly.

If not, please let me know.

Best Regards,

Icey

[Anonymous]

Hi @Icey 

It's not in my advanced editor code, so I'm getting an error despite that. I've also copied the code from advanced editor into another Power BI file, and it works fine. It's this one file that it's not working for. 

What is considered sensitive information, and I can post the code from advanced editor?

[Anonymous]

@Icey 

My issue has changed. I found a measure that was unknowingly using that field, and I've changed it, but now I'm getting a new error when trying to update that table: "A function 'CALCULATE' has been used in a True/False expression". It's saying the rows are loaded, then instead of loading them, it gives me that error.

[Icey]

Hi @Anonymous ,

Please check if column "Sales_Rep__c" is in the code in Advance Editor.

If it is, please delete the corresponding part and try it again.

If not, could you share us your code, removing sensitive information?

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

I hope you mean Object when you say Table. Please show your Power Query Code  (make sure to sanitize the URL)