Ideas on how to automate manual pbix preparation steps

[Deez]

Hi All,

I have a pbix file that needs to be prepared before deploying.

Scenario: 

I have 1 pbix that contains a full list of sheets with specific views

The views are seperated by blank seperator sheets. 

I need to split the above app into 3 pbix files based on these specific groups

Example:

Sheet No.	Sheet Name	New .PBIX File
Sheet1	<< Administration View >>
Sheet2	AdminView1	Administration.pbix
Sheet3	AdminView2	Administration.pbix
Sheet4	AdminView3	Administration.pbix
Sheet5	<< Finance View >>
Sheet6	FinanceView1	Finance.pbix
Sheet7	FinanceView2	Finance.pbix
Sheet8	FinanceView3	Finance.pbix
Sheet9	FinanceView4	Finance.pbix
Sheet10	FinanceView5	Finance.pbix
Sheet11	<< Sales View >>
Sheet12	SalesView1	Sales.pbix
Sheet13	SalesView2	Sales.pbix
Sheet14	SalesView3	Sales.pbix
Sheet15	SalesView4	Sales.pbix
Sheet16	SalesView5	Sales.pbix

 * Sheet Names enclosed with << >> are just blank seperator sheets to assist when manually spliting the file.

If I was to prepare a file for Administration:

1 - Open the main App with ALL views

2 - Delete sheets that are not applicable to the view

  a-  i.e. for Administration.pbix, I will only keep sheets 2,3,4 and delete the rest of the sheets

3. Select the First sheet so that the app opens here

4 - Removing a page filter for a specific field if there is a filter applied to that field.

  a - i.e PageFilter on CountryID = "18" > if there is and Page Filter on this field, it needs to be removed

5. Save As Administration.pbix

I would then do the same steps for Finance & Sales.

Is there any way to automate these steps either in PowerBI.com or via an external script or macro? 

[v-yulgu-msft]

Hi @Deez ,

Using Query Parameter to dynamically load data source might be a workaround.

Deep Dive into Query Parameters and Power BI Templates

Power BI Desktop Query Parameters, Part 1

Regards,

Yuliana Gu

[Deez]

Hi @v-yulgu-msft ,

Thank you for your response and for looking into this!

It is an interesting article but unfortunately I don't think it will assist with my requirements.

The table pasted above represents the actual sheets/tabs structure in my PowerBI App, not separate data sources:

Is there a way to run some sort of script or macro etc. to get this done programatically?

[v-yulgu-msft · Accepted solution]

Hi @Deez ,

---

The table pasted above represents the actual sheets/tabs structure in my PowerBI App, not separate data sources:

Is there a way to run some sort of script or macro etc. to get this done programatically?

---

I'm afriad it is not supported by now.

Regards,

Yuliana Gu

[Deez]

Not the answer I was looking for but thanks for letting me know. There should be a better way to easily manage your sheets with a quick and easy way to rename/hide/delete etc.

I guess I should submit as an idea.