Data source suddenly stopped showing in gateway connection menu

[JWedge]

I have a report that was working fine with two sources via a gateway (sql server and an excel file).  I made a minor update to the report and republished yesterday.  Since republising the sql source does not show up under the "Data sources in this dataset:" but the excel source does.  Removing the excel source and republishing the gateway status reads "Not configured correctly" and no data sources are listed.

I know the gateway is configured correctly as it still works for other existing reports and creating a new basic report querying both of these sources also works allowing me to map to both data sources.

I have tried rolling back to use previous versions of the report (going back many versions over weeks of development) but they all seem to be affected (they all used to work fine 🤔).

I tried copying the queries across to a fresh report but the model is not simple and this just caused new issues so I am potentially looking at just copy-pasting the M code for each query and function and rebuilding the report from scratch which will take time and I'm not confident I won't somewhow end up with the same issue in the end.

Anyone had a similar issue?  Completely stuck and desperately want to avoid starting over without knowing the cause of the issue.

[v-deddai1-msft]

Hi @JWedge,

Please check if you can refresh it in the desktop. Could you please check the m query for sql server in your advanced editor.

If you have Pro account I suggest you  try to open a support ticket. If you have a Pro account it is free. Go to https://support.powerbi.com. Scroll down and click "CREATE SUPPORT TICKET".

Best Regards,

Dedmon Dai

[JWedge]

Thankyou both for replying, as i mentioned the report and many previous versions have worked through the gateway also with scheduled and incremental refresh. 

However, looks like it may be connected to incremental refresh. 

Started from scratch adding one or two queries at a time and publishing to try and spot when the sql source dissapears.  Got all queries and functions in and still fine.  Enabled incremental refresh, republish and it dissapeared.  Disabled it, republish and it came back!

Oddly, I initially disabled inc. refresh on the original file and it did not help and also tried with inc. refresh enabled on a single query and this worked fine so it is inconsistent and this made me think that it was not the cause.

Going to try and see if it is a particular query-inc. refresh combination causing it (no idea why there would be as they are all similar) and will raise a ticket.

[lbendlin]

Can't say I am surprised. I had cases where incremental refresh would work with some Power Query transforms but not other, seemingly equivalent ones. Still much to learn in that area.

[JWedge]

Yes, steep learning curve getting inc. refresh working in this case (forced me to learn how to implement funtions in Power Query among other things) but frustrating when it works for a while and suddenly stops inexplicably.

Have a support call later but I think the likely outcome will be that I will look for a way to avoid using the gateway altogether since this is ultimately what is stopping me using the report on the service - luckily I think I may have this as an option in this case.

[JWedge]

Turns out the gateway issue is a symptom rather than the cause which does look to be incremental refresh somehow preventing the data source publishing correctly.  

Moving one of the data sources I no longer require the gateway route but I still cannot refresh and get an error: "... Credentials are required to connect to the SQL source. ..."

But the "Data Source Credentials" setting is greyed out as if the data source is not there.  Disabling incremental refresh I have no issue.  Refresh works and the data source shows under "Data source credentials" as expected.

Previously I thought that inc. refresh had not made a difference but discovered that it was taking a few goes to actually disable it (it seemed to re-enable itself a few times before stayin off!)

If I have any other data source is in the model it appears on the service as expected (with or without inc. refresh) and no issue with credentials.

[v-deddai1-msft]

Hi @JWedge ，

If you have Pro account I suggest you  try to open a support ticket. If you have a Pro account it is free. Go to https://support.powerbi.com. Scroll down and click "CREATE SUPPORT TICKET".

Best Regards,

Dedmon Dai

[JWedge]

Thanks, I do have a ticket open.

I have isolated the problem (possibly) to a combination of Value.NativeQuery() and incremental refresh.  Has anyone else had issues combining these? Last week this worked fine, this week it doesn't so I'm now stuck.

Stripping everything out bit by bit removing Value.NativeQuery() from my code was the step that stopped the problem.  But maddeningly when I added it back in they didn't come back so who knows 🙃.  Working on building the report back up but republishing at every step to see if/when the problem reoccurrs but will all be a complete waste of time if the problem decides to pop up randomnly again at some point in the future.

[lbendlin]

What made you choose Value.NativeQuery  over specifying the query in the source line?

[JWedge]

Good question...I think because I could not and still cannot figure out how to invoke a custom function in the source line. 

So with value.nativequery i have:

  value.nativequery(Source, "SELECT bla bla bla WHERE ("&myCustomFunction(RangeStart, RangeEnd)&")")

trying to invoke the function like this in the source line after "[Query = " does not work for me.  I don't get an error but it gets stuck forever on this step.

Am I missing something or is it not possible to invoke a function in the source line like this?  I thought I might be able to store the result of the function as a previous step like

 queryString = "SELECT bla bla bla WHERE ("&myCustomFunction(RangeStart, RangeEnd)&")") and then use that after "[Query = queryString," but it still gets stuck on the step with the invocation.

[JWedge · Accepted solution]

Support confirmed the issue was due to a bug. 

Bug fix rolled today has fixed it, Value.NativeQuery combined with incremental refresh now working again.

[lbendlin]

Create a new Power BI Desktop file and connect to the SQL server only.  Publish to workspace and see if you can schedule it. Report back.