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.
Purpose
Publish workbook to PowerBI as a report or dataset.
Requirements
- 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.
Syntax
expression.PublishToPBI( PublishType, nameConflict, bstrGroupName )
expression a variable that represents a Workbook object.
Parameters
| Name | Required/Optional | Data Type / Description |
| PublishType | Optional | Enum XlPublishToPBIPublishType
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. |
| nameConflict | Optional | Enum XlPublishToPBINameConflictAction
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). |
| bstrGroupName | Optional | String
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. |
Example
' 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
Some Details
Detail 1
If no Table or Data Model in a workbook – you will see following error
The workbook contains no table data or data model. Power BI can only work with table data or data models.
Detail 2
If you add a macro in your xlsx file, method PublishToPBI will load macro-free workbook without prompt.
Usually, when you save xlsx with macro, Excel informs you that VB project will be deleted if you don’t change file format.
Detail 3
Once you uploaded workbook from OneDrive – you cannot re-upload it even using msoPBIIgnore or msoPBIOverwrite for parameter nameConflict.
This workbook was uploaded to Power BI. Power BI will automatically refresh with update to your workbook.
if you changed Data Model, you need to delete old file from Power BI and then publish new version.
Tiles on dashboard will not connect to a new workbook, as it will have new UUID on Power BI.
You may see UUID in URL, e.g.
for XL file: https://app.powerbi.com/groups/me/workbooks/1_808046_2358971
for dataset: https://app.powerbi.com/groups/me/datasets/1afbb39c-359c-49cb-b3a3-b95dfb06cde8
Detail 4
You can rename file on Power BI
but source ‘File Name’ remains unchanged
However, re-uploading of old file doesn’t work after rename, and does not show any error. Seems a “feature”.
So, I do not recommend to rename Report / Dataset on PowerBI, unless you rename a source file too.
Detail 5
File location (source) makes sense. Below you may see – same file loaded from local folder and OneDrive for Business
 |
 |
Detail 6
File extension makes sense
Two files with the same name but different extensions: xlsx and xlxm.
Detail 7
However, sometimes file extension makes no sense
Using nameConflict:=msoPBIIgnore you can upload same workbook as different PowerBI reports / datasets
For each Report / Dataset PowerBI creates a new UUID. Therefore, when you delete a Report / Dataset all associated tiles lose connection to their sources. Even if you upload a workbook with same name again, it will be a new UUID and you have to re-create tiles on dashboards.
Be careful when duplicating Reports / Datasets, if later you want to upload a workbook using msoPBIOverwrite – you will get an error as Power BI cannot decide on itself which workbook to overwrite.
Detail 8
Avoid changing of data model when you publish your workbook as a data set. Changing of column data types, column names, measure names etc. can lead to undesired results, dependent visuals can stop display data.
Detail 9
Some of my conclusions conflict with statements on page
https://powerbi.microsoft.com/en-us/documentation/powerbi-service-publish-from-excel/
Probably UI and VBA methods work in different ways.
Detail 10
You cannot download workbook from PowerBI (at the moment of writing this post). So, keep your source workbook in a safe place.