Problem with PowerBI server refresh: Microsoft SQL: Invalid column name 't0_1'

[Anonymous]

Hello everybody,

since two days the PowerBI dataset on the server which was scheduled to automatically refresh, gives an error:

Refresh failed:

Bronbestand_Medicinfo_data has failed to refresh.
Failure details: The last refresh attempt failed because of an internal service error. This is usually a transient issue. If you try again later and still see this message, contact support.

Microsoft SQL: Invalid column name 't0_1'.. The exception was raised by the IDataReader interface. Please review the error message and provider documentation for further information and corrective action.

Next refresh for Bronbestand_Medicinfo_data is scheduled for April 30, 2020 11:00 UTC.

This is strange, because so far as I know, nothing has changed. When I refresh the same dataset in PowerBI desktop, then I don't get the error. But when I publish the report to the PowerBI server and I refresh, then I get this error.

Unfortunally the error doesn't say which table has the problem and I have many tables.

Can please somebody help me?

Kind regards,

Nina

[Anonymous]

Update from Microsoft:

As discussed, our product team confirmed that it has been identified as a bug in Power BI and they are working towards the fix of it.

This issue will arise when report is built with Import mode , so as of now Product Team suggested to use Direct Query Mode for the report as a work around.

We will again check with Product Team on fix date for this issue.

[Nese_FZ]

Hello everybody

Does anyone know if this bug has already been fixed? I have the same problem in a date table.

Thanks

[Anonymous]

We also logged a support ticket and escalated. The response we got from Microsoft is that the fix for this will be implemented in the June release

[Nese_FZ]

Thank you

So, we can only wait them

[Anonymous]

Hello @Anonymous,

Well I had transformations in my dataset where i made some sort by field and replacement changes,

by removing the sort by step, it fixed the problem in the dataset on the power bi service.
I think it has something to do with the auto creation of virtual columns or column names in the queries when making the transformations. 

I hope this would solve your problem.

best regards,
Aymen

[AndreasScholl]

Hello folks

We are having the same issue, does anybody know if there will be a patch for the report server as well and when it will be available?

Right now we are not able to use the May 20 version due to that bug.

BR

Andreas

[Anonymous]

@Anonymous your solution worked! I removed any sort steps in my queries that were causing issues and they load without issue now. Thank you for the suggestion.

[Anonymous]

@Aymenbtkh your solution worked! Great! Thank you for the suggestion.

[Nese_FZ]

Hello

Unfortunatelly not for me. Maybe because we use oracle database?

Erro do OLE DB ou do ODBC : [DataSource.Error] Oracle: ORA-00904: "t0_1": identificador inválido.

[Eliasaki92]

Hi there,

I am also having the same issue on Power BI Report Server May 2020: 

SessionID: 5de5b949-005b-4d04-8eec-0a8d31ac7968

[0] -1055784932: Microsoft SQL: Invalid column name 't0_1'.. The exception was raised by the IDbCommand interface.

[1] -1055784932: The command has been canceled.. The exception was raised by the IDbCommand interface.

[2] -1055129594: The current operation was cancelled because another operation in the transaction failed.

[3] -1055784932: The command has been canceled.. The exception was raised by the IDbCommand interface.

[4] -1055784934: The command has been canceled.. The exception was raised by the IDataReader interface. Please review the error message and provider documentation for further information and corrective action.

[5] -1055784932: The command has been canceled.. The exception was raised by the IDbCommand interface.

[6] -1055784932: The command has been canceled.. The exception was raised by the IDbCommand interface.

The report was created in Power BI Desktop RS JAN 2020 and everything went fine. Data source is SQL Server. After update to MAY 2020 version, the Query folding changed automatically. I have attached both native queries: 

I can not run the new query folding query in SSMS. If I remove the [t0_1] and do a custom query in PBI RS it works as before but of course you loose the steps in the Power Query Editor. 

Looking forward to some feedback from the product team. Maybe the query folding M guru @AlexPowers  can help here :)?

Many thanks and best regards from Germany,
Elias