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

[v-qiuyu-msft]

Hi @Anonymous, 

Since you can refresh report successfully in Power BI desktop, please republish the report to Power BI service, then refresh the dataset in Power BI service. 

If the issue still occurs, please go to Refresh History, there should be RequestID and ClusterURI information, please share them with us. 

Best Regards,
Qiuyun Yu

[Anonymous]

Hello Quiyun Yu,

thanks for your reply! I've tried multiple times to republish en then refresh, but the issue remains. 

The RequestID is c7644266-9dd0-48be-bfec-258e14b8d981 and the URI cluster: WABI-WEST-EUROPE-redirect.analysis.windows.net.

Kind regards,

Nina

[ASarbutt]

Can confirm this appears to be a similar issue my department is having with a dashboard that was refreshing fine up until 27th April. 

Now the refresh fails with

{"code":"DM_ErrorDetailNameCode_UnderlyingErrorMessage","detail":{"type":1,"value":"Microsoft SQL: Invalid column name 't0_1'."}},

A fresh upload did not seem to resolve the refresh problem.

[Anonymous]

Hi @v-qiuyu-msft and @ASarbutt 

@ASarbutt I had the same. It was working fine untill thuesdayevening 28 april. Do you also have a connection with a SQL database in Azure?

I have investigated this issue and I now know which table is causing the issue. The table 'AgenturenTimeCare '. The data source is a SQL database in Azure. I've also a connection with other SQL databases in Azure, but they are working fine. 

The Power query that I use:

let
Bron = Sql.Database("<name>-sql.database.windows.net", "timecare"),
dbo_Prestaties = Bron{[Schema="dbo",Item="Prestaties"]}[Data],
#"Sorteer shifts aflopend" = Table.Sort(dbo_Prestaties,{{"shiftFrom", Order.Descending}}),
#"Kolomnamen hernoemen" = Table.RenameColumns(#"Sorteer shifts aflopend",{{"shortName", "Type dienst"}, {"shiftFrom", "Datumtijd start dienst"}, {"shiftTo", "Datumtijd eind dienst"}, {"shiftTime", "Gewerkte tijd excl. pauze"}, {"shiftBreak", "pauzetijd"}}),
#"Filter Type dienst" = Table.SelectRows(#"Kolomnamen hernoemen", each ([Type dienst] = "CT" or [Type dienst] = "MCC D" or [Type dienst] = "MCC FD")),
#"Nieuwe kolom %KEY_DATE" = Table.DuplicateColumn(#"Filter Type dienst", "Datumtijd start dienst", "%KEY_DATE", type date),
#"Wijzig type nieuwe kolom" = Table.TransformColumnTypes(#"Nieuwe kolom %KEY_DATE",{{"%KEY_DATE", type date}}),
#"Wijzig volgorde van de kolommen" = Table.ReorderColumns(#"Wijzig type nieuwe kolom",{"shiftId", "%KEY_DATE", "Gewerkte tijd excl. pauze", "Type dienst", "Datumtijd start dienst", "Datumtijd eind dienst", "pauzetijd"}),
#"Verwijder dubbele shiftId's" = Table.Distinct(#"Wijzig volgorde van de kolommen", {"shiftId"})
in
#"Verwijder dubbele shiftId's"

When I refresh the model in PowerBI desktop it works fine (no errors), but when I publish the model to PowerBI service and then refresh the dataset, I get the following error message: "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." 

I hope that you can help me!

Kind regards,

Nina

[v-qiuyu-msft]

Hi all,

I would suggest you create a support ticket to get help. 

Best Regards,
Qiuyun Yu

[shanawazsheriff]

Hi All and @v-qiuyu-msft 

Have the same issue with one of my reports which has been running good in production till 26th April 2020. It is complaining about a missing column called t1_0. The refresh fails in PowerBi service. Works well in PowerBI desktop. This is a business-critical daily report. Could you please help to have it fixed?

Details of error

Data source error:	Microsoft SQL: Invalid column name 't1_0'.. The exception was raised by the IDbCommand interface. Table: BP Report.
Cluster URI:	WABI-NORTH-EUROPE-redirect.analysis.windows.net
Activity ID:	3ab1adc6-cc44-4fde-9de7-1111f836e6aa
Request ID:	95d41aea-d271-d81d-bac8-48e6b28d8364
Time:	2020-05-05 14:21:21Z

Thanks,

Shanawaz.

[shanawazsheriff]

Hi,

Have updated to the latest version of PowerBI desktop and it has stopped working on desktop as well. I am getting the same error as in the PowerBI service. Error: Missing column 't1_0'. Not understanding why all of a sudden this error would show up on a report that has been running fine for more than a year.

[Anonymous]

Hi all,

I've a Microsoft support ticket for this issue: 120050522000357

the last update is of this morning:

"First of all, I want to  once again apologize for the experience you’ve had with Power BI.

The investigation is still in progress, and at this point of time there are no updates with respect to timelines from the Product team yet.

I will continue to follow up and keep you posted with the developments related to your issue"

Kind regards,

Nina

[JCvanVeelen]

Exact same issue here after I updated the On Premises Gateway to version 3000.37.35 (April 2020(Release 2)) last friday. The in the error (copied below) mentioned column "t0_1" does not exist in the mentioned table. Refreshing workt without any problem for months and still works in the desktop version without errors.

ERROR MESSAGE:

Something went wrong
There was an error when processing the data in the dataset.
Please try again later or contact support. If you contact support, please provide these details.
Data source error: {"error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","pbi.error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","parameters":{},"details":[{"code":"DM_ErrorDetailNameCode_UnderlyingErrorCode","detail":{"type":1,"value":"-2147467259"}},{"code":"DM_ErrorDetailNameCode_UnderlyingErrorMessage","detail":{"type":1,"value":"Microsoft SQL: Invalid column name 't0_1'."}},{"code":"DM_ErrorDetailNameCode_UnderlyingHResult","detail":{"type":1,"value":"-2147467259"}},{"code":"Microsoft.Data.Mashup.ValueError.DataSourceKind","detail":{"type":1,"value":"SQL"}},{"code":"Microsoft.Data.Mashup.ValueError.DataSourcePath","detail":{"type":1,"value":"sql-pa06;Datawarehouse"}},{"code":"Microsoft.Data.Mashup.ValueError.ErrorCode","detail":{"type":1,"value":"-2147217900"}},{"code":"Microsoft.Data.Mashup.ValueError.Message","detail":{"type":1,"value":"Invalid column name 't0_1'."}},{"code":"Microsoft.Data.Mashup.ValueError.Reason","detail":{"type":1,"value":"DataSource.Error"}}],"exceptionCulprit":1}}} Table: metaSourceDate.
Cluster URI: WABI-EUROPE-NORTH-B-redirect.analysis.windows.net
Activity ID: 35061f31-72c8-469a-b73c-751f6b29ee82
Request ID: 1741ee24-9440-db11-362d-da0e2a6fafc8
Time: 2020-05-11 13:50:46Z

[Anonymous]

Hi,

We are facing the exact same issue, with Power BI service

• Erreur de source de données{"error":{"code":"ModelRefresh_ShortMessage_ProcessingError","pbi.error":{"code":"ModelRefresh_ShortMessage_ProcessingError","parameters":{},"details":[{"code":"Message","detail":{"type":1,"value":"Microsoft SQL: Invalid column name 't0_1'."}}],"exceptionCulprit":1}}} Table: XXXXXX.
• URI du clusterWABI-NORTH-EUROPE-C-PRIMARY-redirect.analysis.windows.net
• ID d'activité7e2021f3-1a2b-44d0-97e6-7f4c243bda72

I opened a ticket and get in contact with support. After checking that everything was OK on my side, I get this response:

******

Internally i had a word with my team about the issue, they confirmed that we got few cases about the issue from the different regions.

It is already escalated and Product Group team is working on it.

If we get any update from the Product Group Team, will keep send you an updates.

*******

I had a Power BI desktop version from 2019. The update was working fine, so since 10 days I was updating on desktop and then publishing online. But yesterday I updated the PBi Desktop with the last version (April) and then I get the same error on the same table with PBI Desktop. It is now impossible to update te reports !

Please note that I have others reports using the same dataset which have no problem on updating.

Hope the microsoft team will find a solution soon.