Disable data load in power query editor

[anandpranav]

We work on semantic model and which is connected with databricks and our requirement is we want to do some modification in source table but that change should not reflect in power bi.. we loaded data from databricks and in power query editior we unchecked "enable data load" and  "include in report refresh" and publish the report in power bi service, however after refresh again we are seeing the modified data in power query editior which we did in Databricks which we don't want.. changes should't be reflect .. kindly help me to achieve this... Thanks.

[MasonMA]

@anandpranav 

Hi, 

Even if a query is unchecked for load and refresh, the semantic model still holds a reference to the source table especially if you're using DirectQuery or Composite Model, or the query is used in a relationship/downstream reference, Or, the model schema still knows about that table’s structure.

Power BI doesn’t cache or isolate the schema by default, so if your Databricks table changes, Power BI reflects those schema changes, even if it doesn't reload the data.

That being said, instead of connecting directly to a live or frequently updated table in Databricks, you may 

• Create a snapshot or staging table in Databricks that holds a static version of the data.
• Connect Power BI to this static table.
• Only update this table manually when you want changes to reflect in Power BI.

You can run this in a Databricks SQL notebook or SQL Editor if it is an one-time copy, 

CREATE OR REPLACE TABLE database.snapshot_table
AS
SELECT * FROM database.source_table;

This will give you full control over when changes are visible.

Hope this helps:) 

[BA_Pete]

Hi @anandpranav ,

@MasonMA's explanantion is correct and they have provided you a good solution to your question.

I just want to tag onto this and suggest (possibly for other users reading in future that don't have Databricks), that the same effect can be achieved by using Dataflows (DF) in the Power BI Service.

You can query your snapshot in a DF, refresh it once to initially load the data/snapshot, then don't refresh the DF again until you want to pick up the latest changes. Connect your model to this DF instead of the source system to gain the same control over when data is updated.

Essentially the same method, just using a more-widely accessible tool/platform than Databricks.

Pete