Logo
banner

Blogs

Power BI > How to make PBI Reports pointing to deleted Power BI Dataset working again?

, November 26, 2020 1129 Views

Recently, we had come across an issue which we thought is very common and is easily resolvable. But, we were highly mistaken.

We had several reports which were using a shared Power BI Dataset. We accidentally deleted the dataset on Power BI Service. Naturally, it deleted all the other reports pointing to this dataset.

The only option we were now left with was to re-publish the dataset (as we had the dataset pbix file in our repository) and then point the existing reports to this new dataset. Hence, as the first step to get out of this issue, we re-published the dataset with the same name.

Now, we had all these report files but when we tried to open the report files, it gave us the below error:

We were quite sure here, that if we would click on “Edit”, it would provide us the list of datasets on Power BI Service and we could then choose the newly published dataset. Unfortunately, when we clicked “Edit”, it gave us a blank screen. We weren’t able to either access the reports or change the dataset. 

Now what!

As we know, .pbix file is a collection of several other files. Among these files, there is a ‘Connections’ file which stores the connection string or DatasetId of the dataset that it is pointing to. If we convert a .pbix file to .zip file, it would show us all the files that it consists of.

For example:


The content in the “Connections” file will be something as below:

Please note that the “Connections” file will only exist for Live Connection Dataset or if there is any connection to a database.

Let us start with the beginning and re-generate the issue to better understand the case. As step 1, publish the dataset and then create a sample report connecting to that shared Power BI Dataset. 

And below is the sample report, Sample1 which is pointing to this dataset(Sales and Marketing Sample):

To understand this case better, we would delete the dataset on purpose and see how the report pointing to this dataset would behave in Power BI Desktop.

As we stated earlier, it gives us an option to edit but doesn’t open any windows after clicking it.
We do not have any other option than creating a new report file.

Now, let’s re-publish the same dataset using the backup pbix file. Although the name of the dataset is the same, DatasetId is changed in the Power BI Service.

We can see that the datetime has been changed now and so we can say that it has been published after.

Now, we have our dataset that has been uploaded once again after it got deleted on Power BI Service and a broken report which is pointing to the deleted DataSet. To fix this, we would need to create a new report, Sample2, that just points to this new uploaded dataset. Now we have 2 reports, one pointing to the deleted dataset and the other one pointing to the newly uploaded dataset.

Let’s see if we can find any similarities/differences between the Connections files of both the pbix files.

So, to access the ‘Connections’ files of both these .pbix files, we need to convert them to .zip.
Please note that we won’t be converting them to .zip by right-clicking > Send to > Compressed (zipped) folder. We would directly change the extension of the .pbix file. 

Go to the folder where the reports are stored. Make sure View > File name Extensions is checked.


Now, directly change the extension as –
Press F2 and change the extension ‘.pbix’ to ‘.zip’.
It would work as below:


We would then compare both the
‘Connections’ file and see the difference.

We can see here that values for  ‘Initial Catalog’, ‘PbiServiceModelId’, and ‘PbiModelDatabaseName’ have been changed. So now we just need to replace these new values for the ‘Connections’ file of the old dataset. Make sure you don’t unzip the zipped folder. Please note that this is the most important part of this process.

Extract only the Connections file from Sample2.zip and replace the old Connections file in Sample1.zip with this one. You can replace it by just dragging and dropping the file. Once done, rename the Sample1.zip to Sample1.pbix and try opening the report.

This way we can recover the reports that are connected as Live Connection to a deleted Power BI Dataset.

If anyone has a better solution, you are always welcome here to refine the knowledge which Power BI Users have. Mention your queries/suggestions in the comments section below.

Happy Reporting!

mm

Inkey

INKEY is your solution partner.
Our focus is to deliver you in-time intelligent innovative solutions ("key") for the problems in hand. Maintaining a quality standard right from the inception of a project is our top most priority.

Our team of talented professionals will execute your projects with dedication and excellence. We take ownership and accountability for the effort that goes into meeting our client’s needs.

Years of experience and proven success of delivering innovative custom solutions.

More posts by

2 responses to “Power BI > How to make PBI Reports pointing to deleted Power BI Dataset working again?”

  1. Jason Kirwan says:

    Thanks for the great write-up on this. As fate would have it, I just erroneously deleted some datasets that are the source of several reports I have spent weeks building – I believe I followed the instructions, noting to not unzip the broken file, but keep getting this error: “Unable to open document. This file is corrupted or was created by an unrecognized version of Power BI Desktop. It can’t be opened.

    Have you hit this error before? Any tips to try and save me weeks of work? 🙂 Thanks again!

    • Admin says:

      Hello, thank you for appreciating.
      We have encountered that error but not while doing this process.

      Unfortunately, if the file is corrupted, there is no way to retrieve that file.
      You can get the previous version here:
      C:\Users\*yourusername*\AppData\Local\Microsoft\Power BI Desktop\TempSaves

      For the broken files that you have, do you have any backed-up files? Or can you download the files from Power BI Service? If yes, you can do this process again. Please let us know so that we can help you forward.

Leave a Reply toAdmin Cancel Reply

Your email address will not be published. Required fields are marked *