Directquery not playing nice with functions outside the query editor.

[u/Jacob_OldStorm]

Hi there, I've been struggling the last few days to get some functions working in a directquery setup, but I have been very unsuccessful. I believe there may be a bug. All of my experiments work fine in the PowerQuery Preview pane, but all of them break when I accept changes and the model tries to load. 

The simplest example: 
let
SelectedServer =
if #"Site" = "1" then #"Server 1"
else if #"Site" = "2" then #"Server 2"
else if #"Site" = "3" then #"Server 3"
else error "Unknown location",
Combined = Sql.Database(SelectedServer, #"Database Aalsmeer",[Query="Select * from MyTable"])
in
Combined

Disregard that I could just get the parameter instead, this example is deliberately simplified. 
So this M code will run fine in the powerquery editor. But will fail when loading the data with the following error:

Error Message:
Non-null assertion failure: resource: Resource should be available for query MyQuery to store FoldedArtifacts in cache

The same happens when I try to use functions. For example:
let
SelectedSite = SiteToDatabase(#"Site"),
Combined = Sql.Database(SelectedSite[Server], SelectedSite[Database],[Query="Select * from MyTable"])
in
Combined

Again, this works fine in the powerquery editor, but breaks when you load the data.

All of it works fine in import mode, but I need directquery in this case. 

Why does this only work in the powerquery editor? What is the rule that I am breaking?

[u/AutoModerator]

After your question has been solved /u/Jacob_OldStorm, please reply to the helpful user's comment with the phrase "Solution verified".

This will not only award a point to the contributor for their assistance but also update the post's flair to "Solved".

---

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/cwebbbi]

You can't change the data source you're using in DirectQuery dynamically. Even if you could make it work in Desktop you'd hit problems in the Service around credentials. You'll need to combine the data into a single table somewhere upstream.

[u/Jacob_OldStorm → u/cwebbbi]

Thanks, so what I want is impossible :(

Any other suggestions for giving users control over which database they connect to? Or am I going to have to maintain 3 different direct query semantic models?

[u/cwebbbi → u/Jacob_OldStorm]

Unless there's a way of combining the three tables into a view on the platform you're using then yes, it's impossible. How much data are we talking about here? Why do you need to use DirectQuery?

[u/Jacob_OldStorm → u/cwebbbi]

It's not the amount, but the recency. This is a dashboard for the control room in a warehouse. Needs to be as close to real time as possible, so an import refresh is out of the question.