Various errors when refreshing in the service that broke all visuals

[Anonymous]

Hi all,

I'm struggling with a refresh problem in the service. I saw many topics concerning this issue but none of the solutions has helped in my case.

Troubles description :

After refreshing my dataset in the Service every visual gets broken with various detail errors depending on which visual I click on :

• The query referenced column 'MonthlyData'[engDate] which depends on another column, relationship or measure that is not in a valid state. Additional information: ''.
• The query referenced column 'LocalDateTable_6267c1a5-a47b-4228-b5f8-9f8e10ca73f2'[Année] which depends on another column, relationship or measure that is not in a valid state. Additional information: ''.
• The query referenced calculated table 'MonthlyData' which does not hold any data because evaluation of one of the rows caused an error.

[engDate] is a DAX calculated Text column sorted by a date column that is not calculated.

'MonthlyData' is a calculated table that uses UNION function to merge 3 tables.

In the desktop I checked the date column type and the columns used in the relationships for every table, all types seems to be okay.

And everything is working fine in the Desktop, 0 errors. The problem happens when refreshing in the Service.

If someone has an idea I'd be glad to read it and try !

Thanks in advance,

Cado

[v-kkf-msft]

Hi @Anonymous ，

Could you please share your PBIX file without sensitive data? That will help us solve the problem faster.

Best Regards,
Winniz

[Anonymous]

Hi @v-kkf-msft 

How can I share the file ? I don't see the button to attach a file.

And I'm afraid that if I remove sensitive data there will be nothing left in the dataset. Sorry..

Cado

[v-kkf-msft]

Hi @Anonymous ,

The error message mentioned a LocaDateTable, it might be the date type or "out of bound" issue. Please refer to similar threads below:

Solved: Date Hierarchy - couldn't load the data for this visual 

Solved: On calculated field, using the date hierarchy causes an error. 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

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

[Anonymous]

@v-kkf-msft 

Hi Winniz,

After some tests it appears that the issue comes from the two Excel files appended with the web API request using UNION function.

But I still don't get the reason :

Every column from the 3 tables have exactly the same date format and type.

There is no out of bound problem because every dates are between 1999 and 2021.

I also tried converting the date to date/time but it makes no difference.

The expected result is to be able to schedule the refresh including the excel files that causes troubles. 

Do you think it could come from the UNION function or from the [engDate] calculated column that translate french month names from the date column in english ? 

engDate = CONCATENATE(SWITCH(Month(MonthlyData[mois]),

1,"January",2,"February",3,"March",4,"April",5,"May",6,"June",

7,"July",8,"August",9,"September",10,"October",11,"November",12,"December",BLANK()),FORMAT(MonthlyData[mois]," YYYY"))

Best regards,

Cado

[v-kkf-msft]

Hi @Anonymous ,

I think there is no problem with your calculated column formula. Is the format of this calculated column text? Could you please share the formula of the calculation table?

In addition, I suggest that you should use the latest version of gateway to see if it can connect the three tables normally.

Or you can try to append the three tables in Power Query Editor instead of using the UNION function.

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

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

[Anonymous]

@v-kkf-msft 

Hi Winizz

Yes the format of the calculated column is Text. 

The formula of the calculated table is UNION('Excel file 1', 'Excel file 2', 'API web query')

I just tried to append in the query editor but it gives me the following error : Query 'QueryName' (step 'StepName') references other queries or steps and so may not directly access a data source. Please rebuild this data combination.”

I followed Ken Puls's article to solve it (https://www.excelguru.ca/blog/2015/03/11/power-query-errors-please-rebuild-this-data-combination/) but the error is still here..

I think it could work if I import the excel files and do the web API query in the SAME query but I don't know how can I append final step with previous steps (excel files source).

An idea for that ?

Thanks again,

Cado

[v-kkf-msft]

Hi @Anonymous ,

This looks like a data source privacy issue, please refer to these documents to see if it helps.

Credentials, Data Privacy Settings And Data Sources In Power Query/Power BI 

https://blog.crossjoin.co.uk/2014/03/26/working-with-web-services-in-power-query/ 

Best Regards,
Winniz

[Anonymous]

@v-kkf-msft 

Hi Winniz,

sorry for the wait, I wrote you a reply earlier but it seems I forgot to send it ! 😅

So, I didn't understand everything in the links you sent. But I think you're right concerning the credential issue, we can see it mentioned in the error log : 

However, I don't understand the reason. My three data sources (1 API web and 2 excel files) have a credential setting set to organizational in both desktop and service. The gateway connection test is succesful.

Do you know what I should check more ? I already read a lot of topics concerning this issue and none of the solution accepted has helped me with this case..

Thanks again for you help,

Best regards,

Cado

[v-kkf-msft]

Hi @Anonymous ,

Try to clear and re-enter all data source credentials in the Data source settings. Set the privacy level at the root level of the web api. For example, the web api is: https://data.gov.uk/api/3/action/package_show?id=20685d28-c11c-4212-b271-4ded5c8c9486 , then set at https://data.gov.uk/.  At the same time set the same privacy level for excel files.

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

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

[Anonymous]

@v-kkf-msft 

Hi Winniz,

I followed your recommandations : delete permissions for the web API and excel files and then apply it to the root url and set again Organizational Privacy everywhere.

Everything works fine in the desktop but once published in the service, the refresh still leads to the same error.

Do you have an other idea ?

kind regards,

Cado