Blogs

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

Inkey, November 26, 2020 5329 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!