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.

PowerQuery cheat sheet

Developing queries for Power BI and Power Query I had to look into documentation or check my previous solutions from time to time in order to get answers to same questions again and again. So, I ended up with a creation of cheat sheet for myself. Couldn’t wait more for cheat sheet from Gil Raviv (know, he plans to make it, stay tuned).

My version is not nicely formatted as DAX Reference Card from PowerPivotPro, but still helpful. It helps me with rarely used symbols and data types, and vice versa, frequently used pieces of M code. E.g. Carriage Return symbol in Power Query, get Excel cell value, or work with datetime and duration types etc.

Continue reading

Shift cells up/down in same column in Power Query

In my previous post I wrote about one interesting technique used by my colleague Zoltán Kaszaki-Krsjak.

Categoty_tmp = Table.AddColumn(Buffer, "Category_tmp", each Buffer[Category]{[ID2]}?),
// It helps to shift values of column [Category] one row upwards.

It is a trick that you most probably will not use in any of your solutions. However, worth to know how it works and how to do this without adding new columns.

“each Buffer[Category]{[ID2]}?” is just a function, so we can combine it with methods described in one of my posts (Transform Column Using Custom Function).

Here is the code.

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Duplicated Column" = Table.DuplicateColumn(Source, "Category", "Method1"),
#"Duplicated Column1" = Table.DuplicateColumn(#"Duplicated Column", "Category", "Method2"),
#"Duplicated Column2" = Table.DuplicateColumn(#"Duplicated Column1", "Category", "Method3"),
Buffer = Table.Buffer( #"Duplicated Column2" ),

Method1 = Table.FromRecords( Table.TransformRows( Buffer, each [Index = [Index],
    Category = [Category],
    Method1 = Buffer[Method1]{[Index]}?,
    Method2 = [Method2],
    Method3 = [Method3] ] ) ),

Method2 = Table.FromRecords( Table.TransformRows( Method1, (row) =>
    Record.TransformFields( row,
    {"Method2", each Buffer[Method2]{ row[Index] }? } ) ) ),
    // method offered by Miguel Escobar in comment to previous post

Method3 = Table.ReplaceValue(Method2, each [Method3], each Buffer[Method3]{[Index]}?, Replacer.ReplaceValue, {"Method3"})
in
Method3

File with methods is here.

But which method is faster?

Continue reading

Bar-Mekko chart in Excel with Power Query

Seems, Excel charts is an area that till now wasn’t considered in blogs as a target for Power Query application (Get & Transform in Excel 2016).

Nevertheless, PQ can replace some VBA solutions and make your workbooks macro-free.

In far 2015 my colleague Zoltán Kaszaki-Krsjak shared with me a very good example of how Power Query can help with generation of specific tables for specific charts, which are widely used in our organization.

Idea to write a blog post about this technique became dusty in me OneNote, and probably would wait more if only Jon Peltier hadn’t attracted my attention to this topic again by his recent post.

Sample workbook contains a solution for Bar-Mekko chart (or “variable width column chart”)


Such chart allows to easily see share of categories, growth or absolute value. Can be used to compare market segments or productivity of departments / subsidiaries. Red line in this case shows average growth – another small but important detail.

Interested how to build it?

Continue reading

Transform Column using custom function in Power Query (part 2)

Year ago I wrote a post Transform table column using own function in Power Query. According to stats, it is quite popular topic, and I’m not surprised.

However, I think that old post is quite complex and too long. Advice should be shorter.

In addition, that post do not cover another very important scenario, when you need to transform column using value from another column.

Consider following case


Task:

  1. We need to Trim column [Name]
  2. We need to divide [Amount] by [Counter] (and do not want to add additional column)

Let’s do this.

Continue reading

Decode Active Directory field UserAccountControl using Power Query

Recently my colleague had to identify inactive accounts in Active Directory and use this data in automated report on regular basis, to quickly react on new switched off users.

There are plenty of VBA solutions or Vbscripts that work with Active Directory and pull data from it.

However, for automated reports developed in Excel or Power BI it will be more convenient to use Power Query (Get & Transform) to get data directly from AD. In addition this helps to reduce mirroring of data.

Information about user status is stored in field UserAccountControl.

According to documentation on MSDN, field value is not very user friendly

“To disable a user’s account, set the UserAccountControl attribute to 0x0202 (0x002 + 0x0200). In decimal, this is 514 (2 + 512).”


Resulting value is a sum of different values of multiple properties.

E.g. what 2146 could mean?

Not easy to say when number is in decimal notation.

However, it is much easier if value of UserAccountControl is represented in binary string – of 0 and 1.

2146 = 2048 + 64 + 32 + 2 = > 100001100010

For example, if we need to check property ACCOUNTDISABLE, we only need to check second digit (from right).

Unfortunately, there is no standard function in Power Query that converts decimal number to binary notation, so I had to create own function.

As usually, it is available on Github: https://github.com/IvanBond/pquery/blob/master/Number.ToBinaryString.m

// Number.ToBinaryString( 1026 )

// result: 10000000010

Function code is quite short, although it is recursive


When number is in binary notation, we can use Text.End, Text.Start function to get needed digit.

Just an idea. Can be used with Power BI alerts to unblock self-blocked users even before they call to IT, for audit, or with Microsoft Flow somehow…

Скачать производственный календарь в Excel

Производственный календарь (или календарь рабочих и праздничных дней) – одна из важных составляющих многих отчетов и инструментов планирования.

Из-за “плавающих” выходных и праздничных дней в России мы не можем каждый год использовать один и тот же набор данных.

Лично у меня ежегодно возникает вопрос – где скачать производственный календарь. Желательно в удобном формате Excel и с наименьшими трудозатратами. И вот совсем недавно я нашёл для себя решение, которое, уверен, пригодится многим.

Continue reading

Changed Type step in Power Query

My work is connected with data coming from different countries, which have different local formats of date, numbers etc.

To be able to consolidate data in Excel models, I have to convert data accordingly.

In Power Query and Get & Transform we can set default Locale in Query Options

However, when I load data from different sources and apply Changed Type ( Table.TransformColumnTypes ) operation I must use corresponding locale for argument “culture”, which is optional.

I noticed interesting behavior. When I change type

New step “Changed Type” appears in Applied Steps without gear

Of course, when Power Query engine created this step it used default Regional Locale, which is “en-US”, as a result values are wrong. I have to use Russian locale for this sample.

When I manually add argument “culture” in formula bar – gear appears near the “Changed Type” step:

It shows nice menu – Change Type with Locale

It even shows sample format when I select Locale

Conclusion

For me it is fine to add locale manually, but when I explained this to my colleagues they were not happy to hear this.

Maybe I don’t know something… But how to call this “Change Type with Locale” by default?

E.g. when I change Data Type – ask about locale I want to use.

Probably, worth to add something like checkbox “Always ask about locale when I change data type” in the area of Current Workbook options.

Or just display gear for step Changed Type by default to let user go the and select local from list.

There is also an idea with close meaning on Power BI uservoice forum

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/17155889-add-using-locale-option-to-new-data-type-menu-bu

UPD:

The option was found. Thanks to Frank and to Maxim Zelensky (@Hohlick)

change-type-right-click

To be honest, I’ve never used this menu to change type. Much faster was to change it from ribbon and from  new small button in column header, and then manually add locale.

Good to know that at least option exists, although is very hidden :-(.

Sort months by first letter in PowerPivot and Power BI

This post is a logical continuation of previous one where I described how to place measures in rows in Power BI table visual.

Here I again talk about trend tables that looks like


It displays several measures in a matrix with months, so we may see development of each measure.

Data viz gurus will say that such data must be on chart.

Right, but sometimes people want to see figures in tables and … nothing else.

One of the problems here is to display month first letters in right order. Continue reading

How to organize measures in tables horizontally in Power BI

Standard way of display measures in Power BI table visuals (Table and Matrix) – in columns.


But what if you required to display measures in rows and Year/Month in columns to see trend and remain relation with slicers and other visuals, keeping table flexible?


I couldn’t find something like Transpose table in Table/Matrix properties. And it is not possible to pull measure in rows.

In addition, required table contains rows with same value in column [KPI] – “Growth” with indent. And in different rows it has Growth of different measures.

How to do this in Power BI Desktop? Continue reading