Ghost query causing report refresh to fail in service

[ERussell]

I recently published a report up to the service that has been working, but now in the service when I try to refresh I see an error message that says:

"You can't schedule refresh for this dataset becasue the following data sources currently don't support refresh:

* Data source for Query1"

The thing is that there is no Query1 in the model!  I tried creating a blank Query1, and publishing but the error persists. I tried deleting that query and publishing, still getting the error... tried publishing to a different workspace, still getting the error.  I ran into the problem once before and the only thing I was able to do to fix it was completely start from scratch and rebuild the report.  If there's anything I can do to try to fix it without having to do it for this report (it's a lot more involved than the last one) I'd love to know.

Thanks!

Edward

[collinq]

Hi @ERussell ,

I am wondering if in one of your existing queries if you have a reference in the M code to a "Query 1".  Perhaps Query1 existed initially and was renamed but the rename was done in a way where the M code didn't update properly and part of the code still has a reference to Query1.

I would appreciate Kudos if my response was helpful. I would also appreciate it if you would Mark this As a Solution if it solved the problem. Thanks!

[ERussell]

Sorry for the delay, busy day at work today.  I went through all the other existing queries' M code and I don't see a reference to a Query1. I copy pasted every one into Notepad++ and went through looking for any references to "Query". As far as I can tell everything looks fine ?

[ERussell · Accepted solution]

I figured out what was causing the problem.  This step in one of my queries:

 #"Run Python script" = Python.Execute("

def f(flag):
    global previous_seq
    previous_seq = 0 if flag == 0 else previous_seq + 1
    return previous_seq

previous_seq = 0
dataset['Fiscal Period'] = dataset[['PERIODCODE']].apply(lambda x: f(*x), axis=1)

",[dataset=#"Changed Type"]),
 

What it was doing was numbering the fiscal periods 1-# based on the opening and closing period codes. This allowed me to handle years with <> 12 fiscal periods, but going through the history there was only one year that had 13 periods. So instead, I just filtered the years past that year, created an index column starting with 1, then used Number.Mod([Index], 12) and replaced the 0's with 12. Stuck with only being able to hand years with 12 periods, but at least the refresh is working from the service again.  What I don't get is why the query previously worked in the service (albeit through a personal gateway rather than my enterprise), and suddenly stopped when that query hasn't been changed since I first wrote it. Ah well, such is life 🙂 Thanks for your help!