"Cancellation occurred when sending or receiving a request" - is there a way to find fault field?

[Aleksei_Pr]

Hello,

I experienced "Cannot connect to the mashup data source" errors trying to have my report updating in service using On-premises gateway in personal mode. I hope I've managed them by installing 32-bit version of ODBC BigQuery driver in addition to 64-bit one. To get to this idea I spent 3 days emailing support (REG:118081318773964). Now I stacked with new type or error. Details of this error message are not too much informative as well (see it below).

Could someone just say "you have invalid X field in Y table?". Why there are error messages wich are so uninformative? Even log files give no clue about specific table or query. I have more than one hundred of tables in my report and I already spent  three days looking through them trying to find an error. Support engeneer just plays for time.  I'm completely unsatisfied. 

Cancellation occurred when sending or receiving a request.

Please try again later or contact support. If you contact support, please provide these details.

Cluster URIWABI-WEST-EUROPE-redirect.analysis.windows.net

Activity ID8bebdfdd-60fd-7310-fc2c-6bb0c48d1b84

Request ID1318caeb-9e95-edb7-df20-29a448d15ff6

Time2018-08-16 21:18:17Z

[v-juanli-msft]

Hi @Aleksei_Pr

Do you connect to Google BigQuery using Google BigQuery connector in Power BI Desktop?

Could you refresh the data in Desktop successfully?

How many datasource do you connect to?

Best Regards

Maggie

[mdmoo]

I have had the issue with the data rates:

ERROR [HY000] [Microsoft][BigQuery] (100) Error interacting with REST API: Exceeded rate limits: Your user_method exceeded quota for api requests per user per method. For more information, see https://cloud.google.com/bigquery/troubleshooting-errors Table: AllAccounts_Reach.

I got around it on the desktop version by disabling the load tables in parallel option under Current File->Data Load

My issue is that the same error occurs when I publish it and I try to refresh. It is almost like that option is ignored when it is refreshed by the service. The only other answer I have it that the connec tion between Google and the PowerBI coulds is so fast that even the smallest amount of data exceeds the data rate.

[Aleksei_Pr]

I had this issue. Solved by using Simba BigQuery ODBC connector.

[mdmoo]

I have had the issue with the data rates:

ERROR [HY000] [Microsoft][BigQuery] (100) Error interacting with REST API: Exceeded rate limits: Your user_method exceeded quota for api requests per user per method. For more information, see https://cloud.google.com/bigquery/troubleshooting-errors Table: AllAccounts_Reach.

I got around it on the desktop version by disabling the load tables in parallel option under Current File->Data Load

My issue is that the same error occurs when I publish it and I try to refresh. It is almost like that option is ignored when it is refreshed by the service. The only other answer I have it that the connec tion between Google and the PowerBI coulds is so fast that even the smallest amount of data exceeds the data rate.

[Aleksei_Pr]

Hi Maggie,

That connector returns error “ERROR [HY000] [Microsoft][BigQuery] (100) Error interacting with REST API: Exceeded rate limits: Your user_method exceeded quota for api requests per user per method" so I use ODBC driver which works fine, And yes, report refreshes in Desktop without any issue.

I have 31 datasources and 131 tables. Last message from support engineer says that there is "Not a legal OleAut date" error and I'm supposed to use piece of M-code to replace wrong dates whith nulls. It could be helpful for anybody experiencing this type of errors so I'm pasting it here:

#"Replace Date" = Table.TransformColumns(#"Find Date", List.Transform(Table.ColumnsOfType(#"Find Date", {type date, type nullable date, type datetime, type nullable datetime, type datetimezone, type nullable datetimezone}), (name) => {name, (date) => if date = null or Date.Year(date) < 101 then null else date, Type.TableColumn(Value.Type(#"Find Date"), name)}))
in
#"Replace Date"

But support pointed to the table which I don't have in my report. As I have more than a hundred tables and some of them have even several date fields it will take significant time to apply this code to all date columns just to see if it helps. I would like to have more specific information about source of error.

[Aleksei_Pr · Accepted solution]

The source of error was a date earlier than 01/01/1900 in one of the tables. The code below finds date fields in a table and fixes wrong data. Open andvanced editor and replace last two lines with this code (#"Previous Step" is to be replaced with the name of the last step in query). That's it.

#"Replace Date" = Table.TransformColumns(#"Previous Step", List.Transform(Table.ColumnsOfType(#"Previous Step", {type date, type nullable date, type datetime, type nullable datetime, type datetimezone, type nullable datetimezone}), (name) => {name, (date) => if date = null or Date.Year(date) < 101 then null else date, Type.TableColumn(Value.Type(#"Previous Step"), name)}))
in
#"Replace Date"