Publish Excel file to PowerBI using VBA

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s