Forum Discussion
Ghost query causing report refresh to fail in service
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
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!
3 Replies
- collinq
Super User
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!
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 ?
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!
