Mysterious Disappearing SQL Statement in Get Data > Import

[ChiliGuy]

I have been consistently using “Import” and pasting SQL into the “SQL statement” box in “Advanced Options” in order to filter the rows and columns getting pulled into my data model. To date, if I wanted to edit that SQL (to add or remove a column or add additional filtering logic) I would go to “Edit Query” in Power Query Editor and simply copy and paste my updated SQL into that box.

This has worked flawlessly up until now. Yesterday, I updated my PBI Desktop to the November update, and I’m noticing new behavior. Now, when I go into the Power Query Editor and click the gear icon for “Source”:

First, instead of seeing the actual data (columns and rows) in the canvas, I now see a list of the schemas in my Oracle database:

And when the settings pane opens, “Advance options” is closed by default:

And when I open it, there is no SQL being displayed in the SQL Statement box:

Why is this happening? Is this a new “feature” with the November update? Does this have anything to do with setting the option for Formula > Enable M Intellisense…”??

Please help… this could become a major update issue for all the data models I’ve built over the years!!

[WV]

I am having the same issue!!!! I had it with Oracle SQL data base and with Azure Synapse. I am not able to find any real resolutions!

I am working in a report from my desktop and I have 23 queries - working in the tool as normal. I save and publish. When I go in the next day - ???? - just like that, ALL the queries are GONE. The size went from 840mb to 12kb. I was able to download the file I published and start working again from that but super scary.

[v-shex-msft]

Hi @ChiliGuy,

Any changes on your data connector that you applied? Any previous feature you need on your report? Please share more detailed operations steps to help us clarify these. 

How to Get Your Question Answered Quickly 

In addition, I'd like to suggest you enter the 'advanced editor'(view ->advanced editor ) to check the whole power query formula to confirm if these additional SQL statements are stored in the data connector steps optional parameters.

Regards,

Xiaoxin Sheng

[ChiliGuy]

The Gateway is updated whenever an update is made available. The October update was completed and there hasn't been an update released since then. Since I just noticed this last week, I suppose the October (or an earlier) update could have been a cause, but I fail to understand how any changes in the Gateway (which is local) would remove the SQL from a PBIX's Import object (which is in the Service). Can you explain how the two might be related?

[v-shex-msft]

Hi @ChiliGuy,

I also not so sure about this, maybe they do some optimate on the data connector encode or interface/ports, and these options parameters not compatible with the old version gateway or blocked by your device settings.

Have you configured the whitelist of power bi service requests? 

Add Power BI URLs to your allow list 

In addition, you can also consider submitting a ticket to ask the power bi team to trace these if the issue related to the backend processing.

submit a support ticket

Regards,

Xiaoxin Sheng

[amitchandak]

@ChiliGuy , see if there are some known issue on the issue page - https://community.powerbi.com/t5/Issues/idb-p/Issues

[ChiliGuy]

I have checked the Issues page and have found no reports similar to mine, at least not recently.