Power BI

Needs Votes

VBA for Power BI Desktop

Vote (1269)

Anthony Newell on 06 Jun 2016 03:14:20

Ability to provide a custom UI for a user to enter some parameters / inputs prior to refreshing the data model and publishing to Power BI

Comments (170)

Comments (170)

Brett Edwards on 23 Mar 2023 02:43:27

RE: VBA for Power BI Desktop

Every reasonably sized reporting solution I have ever worked on has had large components of code generated from metadata via scripts. For example, BIML, dynamic SQL or powershell to generate data staging tables and ETL processes. Now more of that work is moving into Power BI, being able to script the solution and not hand code dozens/hundreds of tables that all follow the same pattern can be the difference between a working enterprise solution and a manual mess. VBA for PowerBI, similar to what is already available for PowerPivot in Excel, would be great step forward.

BI Mizusoft on 13 Oct 2022 01:25:33

RE: VBA for Power BI Desktop

I could be C# or Javascript.

James-PR Warren on 31 Jan 2022 16:16:48

RE: VBA for Power BI Desktop

I think we should be clear here what we are after:

A. VBA in PowerBi

B. VBA/External clients ability to control/automate PowerBI (I.E. PowerBI hosting a COM server and exposes a type library)

I think most requests above are calling for Option B, which really has little to do with VBA.

In such a case, we'd really be looking for 4 things:

MVP A PowerBI type library
MVP Ability to create instances of PowerBI from ProgID --> CreateObject("Power.BI.Application")
MVP Ability to open and run PowerBi PBIX files
MVP Ability to add custom connections via a DISP interface.
Nice to have: Ability to access and add datasets to a report (maybe as pure-dax)
Nice to have: Ability to change the dax of existing reports

Here's an example of what I'd suggest:

Dim obj as PowerBI.Application: set obj = CreateObject("Power.BI.Application")

Dim report as PowerBI.Report:   set report = obj.open("my/file.pbix")

Dim customConn as object:       set customConn = new MyCustomConnection

With report

  .connectors.add(customConn)

  With .queries.createQuery("myQueryName")

    .dax = "..."

  end with

  .save

  .refresh

  Dim vData: vData = .queries.data

End With