Delete data from PowerBI Desktop Model

[jklkj]

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?

[jklkj]

I have considered your response, and I am still unsure of how to deploy a BIM file to the Power BI Report Server in an on-premise environment without internet access. Everywhere I have researched, it only discusses the Power BI Service, which is not utilized nor permitted by the company I work for. Therefore, I did not consider this option from the outset. Could you please provide me with a link where I can find more information, ideally a procedure for automatically deploying a BIM file to PBIRS? Thank you.

[bcdobbs]

Have to admit I can't see any documentation on this. I may well be wrong. I'll install report server my end and have a go.

[bcdobbs]

Hi @jklkj ,
Appears to be a lot more locked down that I thought it would be!

I think though if you run tabular editor on the server it's hosted on and try to connect to ServerName:5132 (See Solved: Is SSAS service included in Power BI Report Server... - Microsoft Power BI Community) it might work for you. I seem to be able to connect to that via SSMS (Set to Analysis Services)

[jklkj]

Hi Ben,
thank you for you reply.

Can I use XMLA endpoint for PowerBI Report Server (on-premise PowerBI)?
Is there any way how to automically (e.g. using PowerShell) update PBIT back to PBIX file?

Thank you.

[bcdobbs]

XLMA is just the protocol used by the analysis service engine that sits under Power BI so I'd be very surprised if you couldn't connect that way.

As to PBIT to PBIX I don't think there is a native way (https://ideas.powerbi.com/ideas/idea/?ideaid=e41d996b-1fc4-463a-b8ab-bdedb73728d3). Not done it but you might be able to do it using pbi tools https://pbi.tools/cli/usage.html

[bcdobbs]

Hi,

Have you considered going down the thin report/connected dataset approach?

You then work on a json bim file (or soon TMDL) and deploy it to an XLMA end point. This is simply a definition of the model and contains no data. Plays really nicely with source control.

Your report pbix files then connect to the dataset in the service and so also don't contain data themselves.

The other option is to save as pbit which is exactly what you're looking for in that it is the report, the data model but no actual data.