BI Report Refreshes on Desktop / Error Refreshing in Service

[IamTDR]

Hello

As the subject line states, I created a report via BI Desktop and when I publish the report to BI Service the report does not refresh.
I am receiving a error message that I do not understand.  Any advice?

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_0'."}},{"code":"DM_ErrorDetailNameCode_UnderlyingHResult","detail":{"type":1,"value":"-2147467259"}},{"code":"Microsoft.Data.Mashup.ValueError.Class","detail":{"type":1,"value":"16"}},{"code":"Microsoft.Data.Mashup.ValueError.DataSourceKind","detail":{"type":1,"value":"SQL"}},{"code":"Microsoft.Data.Mashup.ValueError.DataSourcePath","detail":{"type":1,"value":"data.fadavis.com;datawarehouse"}},{"code":"Microsoft.Data.Mashup.ValueError.ErrorCode","detail":{"type":1,"value":"-2146232060"}},{"code":"Microsoft.Data.Mashup.ValueError.Message","detail":{"type":1,"value":"Invalid column name 't0_0'."}},{"code":"Microsoft.Data.Mashup.ValueError.Number","detail":{"type":1,"value":"207"}},{"code":"Microsoft.Data.Mashup.ValueError.Reason","detail":{"type":1,"value":"DataSource.Error"}}],"exceptionCulprit":1}}} Table: Lookup_Rolling_Published_PubDate.
Cluster URI: WABI-US-EAST2-C-PRIMARY-redirect.analysis.windows.net
Activity ID: 7431ea3a-ab96-412b-89bf-78feaadb704e
Request ID: a294c60a-76d0-85f1-f724-ef92fba377f5
Time: 2023-04-04 18:44:33Z

[mscla]

In my case, I deleted all the sorted rows and left the Appended Query to the end of steps

[GilbertQ]

Hi @IamTDR 

The error is saying that there is an invalid column name 't0_0' which is coming from your table "Lookup_Rolling_Published_PubDat"

If you can please make sure that the gateway server can read the source SQL Server.

[IamTDR]

Thanks for the reply. 
This is the confusing part. No field in that table is named that. Actually the table listed is rather small. I even tried to do a test and remove said table but the refresh error still existed but then named another table. 

Also noticed that Dashboards with tiles linked to this report I cannot even delete now. Very strange indeed. 

[GilbertQ]

Hi @IamTDR 

That is really weird what happens if you try it again?

Also is it possible to monitor your SQL Server when it is refreshing to see what query is being sent?

[IamTDR · Accepted solution]

I just got my BI report to Refresh in BI Service.
I think this must be a bug. 
In my table, I used a transformation (Power Query) to sort by date.  By removing the Sort step the report was then able to refresh in Service.  (In reviewing, the Sort step wasn't actually needed)

Not sure why Sorting caused this issue, but this was how I resolved my issue after many trial and errors.

[IamTDR]

Tried multiple times during the day all the same results. Curious what happens after a refresh over night.