How can I replace a table or view by a query in datasource?

[EricMamet]

I suppose it's pretty straight forward...

In this particular case, I want to replace a view by a miodified version of the view code itself (my source is Snowflake although I suppose it does not matter)

[amitchandak]

@EricMamet , giving a query in the advance option of connection?

[EricMamet]

Thanks @amitchandak 

I am already using a "View" but I want to replace it by a query with a modified version of the underlying SQL (without redefining an entirely new datasource if possible)

[edhans · Accepted solution]

@EricMamet - Connect to the new data source as needed in a new query and navigate and transform it as necessary.

Then, open the advanced view of the new query and copy that code to the advance view of the old query.

Power BI will see that as the same query on the DAX side, not as a new table, which is I suspect what you want. You can then delete the new query you created.

If it is as simple as an identical query/view/table but a different server, you can just use the Change DataSource icon on the ribbon, but I suspect your issue is slightly more complex than that. That is great for switching servers and databases (dev/prod/test) but useless for switching tables, queries, views, etc.

If not, can you please be more specific about what you are trying to do. You can see the link below for some assistance on re-using M code from one query in another.

 See this article if you need help using this M code in your model.

[EricMamet]

@edhansThanks for your answer that seemed a perfect solution... until I discovered that the Snowflake connector does not allow direct SQL 😫

But this is an entirely different problem...

[edhans]

Doesn't allow direct query, or doesn't allow you to type in SQL statements?
If the latter, you don't want to do that.

Either use Power Query to craft your transformations allowing Query Folding to work as much as possible (I've generated transformations that aggregated 5-6 tables and generated 800+ line SQL statements before), or create a view. I don't know much about SnowFlake if that is possible like SQL Server, but if it is a relational database, then that might be your answer if you want to key SQL yourself.

That advanced query button that allows you to type SQL in Power BI directly is problematic. It has permission issues in the service, blocks any further transformations that fold, blocks any further transformations period for Direct Query, and prevents Incremental Refresh from working at all. Just avoid it.

[EricMamet]

I meant typing the SQL directly in the datasource.

It's purely for "experimentation" purpose.

I would usually not want "adhoc" queries on the database anyway.

In this case, it was to avoid certain undesirable complexities in the underlying query (UNION to another fact table...)

Thanks for the tips