Recently Teo Lachev released a very short but important post about Automation Excel to PowerBI publishing.
In a nutshell: now we have a VBA method PublishToPBI, which allows to publish workbooks to PowerBI.
This is quite interesting scenario for Excel-based Self-Service BI solutions.
I played around with this method, and share with you results of my experiments.
Excel VBA PublishToPBI method documentation
Unfortunately, there is no official documentation yet. At least in the list of Workbook Methods.
Below is my modest attempt.
Publish workbook to PowerBI as a report or dataset.
- workbook must be saved in xlsx or xlsm format.
- User with PowerBI license must be signed-in in Excel.
- for publishing as a dataset – workbook must have Excel Table or Data Model.
expression.PublishToPBI( PublishType, nameConflict, bstrGroupName )
expression a variable that represents a Workbook object.
|Name||Required/Optional||Data Type / Description|
msoPBIExport = 0, publish workbook as a dataset. Workbook must contain Table Data (range formatted as a Table) or Data Model (aka PowerPivot model).
msoPBIUpload = 1 (default) publish workbook as a report, which can be consumed in Excel Online directly in PowerBI.
msoPBIAbort = 1 (default), workbook will not be loaded to PowerBI if there already exists workbook with same full name (including extension)
msoPBIIgnore = 0, ignores existence of report / dataset and creates new one on Upload/Export.
msoPBIOverwrite = 2, overwrites existing report / dataset with same full name of workbook. Note: method raises an error if find more than one report / dataset with same full name (e.g. if msoPBIIgnore was used previously).
Name of target Workspace.
If omitted, method loads workbook to “My Workspace” of signed-in user.
Note: requires Pro-account, signed-in user must have edit rights in target workspace.
' Creates Dataset from ActiveWorkbook in workspace "Finance Team" (if signed in user has access to it) ActiveWorkbook.PublishToPBI nameConflict:=msoPBIOverwrite, bstrGroupName:="Finance Team", PublishType:=msoPBIExport ' Creates PowerBI Report from ActiveWorkbook in signed in user's workspace ActiveWorkbook.PublishToPBI nameConflict:=msoPBIOverwrite, PublishType:=msoPBIUpload ' Creates Dataset from ActiveWorkbook in signed in user's workspace ActiveWorkbook.PublishToPBI nameConflict:=msoPBIOverwrite, PublishType:=msoPBIExport