Dear all,

We are currently working on developing a tool that deletes data from PowerBI Desktop before the business users save it to TFS.

However, we have encountered two issues that we are struggling to resolve and we were hoping you could provide us with some assistance.

Our tool has been developed with the following prerequisites:

• PowerBI Desktop (for Power BI Report Server)

• Windows Server 2019 Standard

• Windows PowerShell ISE (as administrator)

Issue 1

Our first issue is related to data deletion. We initially approached this by connecting to the Analysis Services workspace created by PowerBI Desktop,
finding the appropriate port, and then using PowerShell to connect to it and delete the data using the "Invoke-ProcessTable" function.
This method worked without any problems until we started using PowerQuery Groups.

What is the task:

The task is to be able to delete data from a PBIX file by Powershell, even when PowerQuery groups are used.

Working Solution:

We use PowerShell to open the PBIX file and find the port to the Analysis Services workspace that is created in the background.
As the server name, we use localhost:<port>. We have an iteration which deletes all tables in a database. We delete the data using the "Invoke-ProcessTable" function.

Not Working Solution:

This was the working solution to delete data from the Power BI model, but we encountered a problem when we started using PowerQuery groups.

Error Message:

Question:

How can we delete data while also utilizing PowerQuery groups?

Issue 2

Our goal is to ensure that once the data is deleted, it remains deleted even after saving and closing the PBIX file.
Currently, however, we've encountered an issue where the deleted data reappears when we reopen the file.
As such, we're looking for a solution that will allow us to permanently remove the data from the file, so that it does not return upon reopening.

What is the task:

Our goal is to have a PBIX file without any data permanently, once the data has been successfully deleted.

Not Working Solution:

Our initial approach for addressing the issue of data persistence was to simply save and close the PBIX file after deleting the data using PowerShell.
However, this solution did not prove to be effective in achieving our goal of permanently removing the data from the file.

Question:

How can we ensure that the deleted data is not restored when we save and close the PBIX file?