Forum Discussion
automate routine functions with VBA-like script
I’m an experienced VBA programmer and am accustomed to using it to automate many different aspects of Word, Excel, Visio, and PowerPoint.
I’m starting a new project in which I’ll be creating six or eight PBI reports, each of which will have between five and 15 pages. The visuals on each page will be the same in every case but the number of pages and data sources will be different in each report.
Because of the singificant degree of repetition, I want to automate as much of the report creation process as I can. I will make extensive use of parameters and a template, but am wondering whether there is a VBA-like capability in PBI to do things like rename pages using a format such as “<fixed name> n” where n=1 to max page count; duplicate a page one or more times; copy and rename bookmarks.
Thanks.
9 Replies
Scott_Visio not sure if it is possible and also not sure if this is the right approach when it comes to Power BI. maybe you need to look at Row Level security, create one report, and then it gets filtered based on the user who is viewing the report.
✨ Follow us on LinkedIn
Learn about conditional formatting at Microsoft Reactor
My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
Scott_Visio how much data is too much data, it is a big debatable question. Power BI has an amazing compression algorithm but it all depends on what is included in the data, cardinality, and also type of data (numeric vs string), and above all how good is data model, and how efficient are DAX code, all these leads to performance, scalable solution.
Having said that, size is not the only thing that can have a performance impact and many more things to consider. I worked with 300 ~ 400 million rows dataset and these perform lightning-fast, at the same time I have seen models (poorly designed), with small data sizes and perform very badly, so there is a lot to consider.
All the best on your journey and the community is there to support you in this journey.
Cheers!!
✨ Follow us on LinkedIn
Learn about conditional formatting at Microsoft Reactor
My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
Interesting point about high performance with hundreds of millions of rows but circumstances where the model has a negative effect. I'll look for some design tips and revisit my model.
Back to my original question about VBA-like automation functions using a specific example: a report has ten pages, one for each floor of a building, and each page includes a floor plan created with Visio and the Visio Visual. Each page also includes a collection of slicers and other visuals. I'd like to make the same change, perhaps a formatting change, to each visual of a certain type on each page. Describing this in VBA-like pseudocode:
for each page for each visual if visual.name = "ABC" then <make changes> end next visual next pagePossible to script this for Power BI? From what I've heard so far, I'm guessing the answer is no, but thought I'd ask.
Hi, Scott_Visio
This feature is currently difficult to implement in PowerBI reports.It is indeed a good proposal. I think you can post it on the PowerBI Idea forum. If you get enough votes, the Product Team will evaluate and consider it.
Best Regards,
Community Support Team _ Eason
I haven't used these much myself, but there are scripting possibilities via PowerShell and Tabular Editor.
AlexisOlson TE is more on the modelling side, not adding new pages/visuals, and PowerShell is more for automatic tasks in Power BI service (like admin stuff)
I know but I do think there are some relevant bits from each one.
For example, you can duplicate reports and change data sources with PowerShell while Tabular Editor is useful for scripting edits to measures and updating column properties and relationships. Neither of these applies to pages and visuals but I don't think any single tool can cover all bases and these two may cover some of the use cases Scott_Visio is interested in, though certainly not all of them.
Thanks for making a distinction between the best uses for PS and TE. I'm familiar with the former but not the latter so I'll take a look.
Also, I'm trying to create a way for less-technical members of my client's team to create a new report from my template. That group is less likely to be familiar with either PS or TE.
Thanks for the suggestion and I will definitely consider the idea as I get further into the project and have a better sense of whether row access is a user-based issue.
My question was based on the assumption that I will have gobs of data (note the technical term 😉 ) for each of the six or eight reports and that it was probably better to separate the data to improve efficiency. I don't have a good sense yet of how much data is too much for one report. For example, is it better to load a subset of the data into a report and use 80% of it? Or is it OK to load the same data into multiple reports if any one report only uses 20% of the data after filtering?
