Cancelling Change Data Source in mixed model also deletes all changes in Power Query

[C4YNelis]

I just lost half a days work when I was ready to apply all changes I made in the Query Editor. Before I were about to apply my changes, I noticed the mixed model was still based on an older version of the Direct Query source. I went to change my Direct Query source, just after it started to apply all changes (and also started to load all edited power query queries), I decided I first needed to delete a duplicate table in the local model, to prevent having to wait twice for all changes to be loaded. What happened next is that Power BI Desktop deleted all my changes in the Power query queries. This is a very annoying bug, that has cost me a lot of time just now. At the same time, it is quite a specific scenario that probably doesn't happen too often.

So my steps:

1. Opened and worked on a mixed mode report (a report where the direct query source had also previously undergone extensive changes not yet reflected in this model).

2. Made a lot of changes in Power Query queries (the local part of the model)

3. Changed my Direct Query source to the same source, hoping to load all the recent changes.

4. Cancelled the data load when it started

At this point I saw all my work in the Direct Query editor being reset to the way it was when I first opened the report this morning.

It would be nice to have this fixed.

Cheers,

Niels

[Anonymous]

Hi @C4YNelis 

I don't quite understand the third operation, why do you have to go and change the source when you are using the same data source? You did the operation in Power Query without applying the save and just modified the source and didn't load data after the change , I don't really understand your operation. Can you describe in more detail what you intend to do?

Best Regards,
Community Support Team _ Ailsa Tao

[C4YNelis]

Hi @Anonymous ,

thank you for your quick reply. I agree, reading it like this, it doesn't seem to make much sense. In all honesty I am now in doubt whether I changed to the same source (I think I did), or perhaps to a different one (TEST vs PROD environment), but I'm fairly sure I changed to the same source in hopes of loading the new version of the external model, without having to apply all changes in the Query Editor (due to the relatively long wait).

If you're interested as to what exactly I'm trying to do, it's a long story, so bear with me please as I will do my best to explain my actions.

I've been requested to work more on securing reports (data) lately. Specifically, there was a request to not show all sensitive data anymore to every user. In other words, apart from RLS, there was a need to start introducing OLS. You're probably well aware that OLS is a feature that is not really supported, but you can use it with Tabular Editor still.

As I'm working in a shared capacity (without the ability to leverage Deployment Pipelines), in order to introduce OLS in a safe way, I couldn't risk just "blindly" introducing OLS in my main dataset (which is also a source for many (+- 50 ~ 70) other reports) in a production environment in the power bi service. In order to mitigate those risks, I've created a TEST environment where I can see the effect of major changes first, without breaking half the company reports in case of a mistake.

Back to OLS; having introduced OLS, you get errors in visuals for fields that now no longer seem to exist for certain roles. So, in order to fix this, I've started using field parameters as a workaround combined with RLS, to enhance the user experience (effectively hiding all hidden fields without any errors and dynamically substituting measures for others based on a security level, in case the original measure was unavailable for that specific user (role). Quite a bit of work and quite complex, but in the end it worked like a charm.

Except... Field parameters are not supported when you use your model as a source in other reports, which meant that all my nice workarounds just disappeared all together. I then tried a different approach, effectively using connected tables, linked via an indexfield, using RLS to simulate OLS. It occurs somewhat more of a performance penalty, but it also works without those ugly errors for users and what's more, it also works when using that model as a source for other reports.

So having made all those changes, you can imagine that the report I am now working on (in mixed mode - using the OLS model described above together with a local model), has changed quite a bit. Since the local model of this report uses custom functions in Power Query to build the model, which takes a while to load, I was hoping to "reload" the data source to reflect all the recent changes in the external dataset. However, when reloading this dataset, it also started to apply all changes, taking a long time to refresh all those tables.

Also I realized I had no need for a newly introduced local table, since that was already available in the (new version) of the external dataset that was being reloaded, so to prevent having to wait twice for everything to refresh, I cancelled my reload to first delete this table. When doing so, PBI Desktop just reset all my changes in the Power Query Editor, effectively deleting hours of work.