VBA not dead, yet.

Ivan Bondarenko November 26, 2019 1 Comment

VBA, from what I see, is still used here and there.

Before Power Query had come to Excel I used mainly VBA macros to perform data cleansing and transformation. Hours of coding…

Now I can’t believe I had to fight with IT department to get Power Query addin installed (when it was a standalone add-in. Now it is a built-in part of modern Excel).

Long story short. Now it is Power Query that helps me with data transformation, cleansing, and consolidation.

I built several dozens of workbooks that transform and consolidate data from various sources.

Once extraction and transformation problem is solved comes another thing – how to automate update of all those workbooks? Obviously, I don’t want to open each manually and hit Refresh button every day.

Using VBA, I developed a solution that helps me to update Excel workbooks automatically on schedule – check https://excel.city/excel-automation/ if interested how I do this.

Why not to schedule in Power BI?

“What the hell?” – one may ask: “Create Power BI datasets, schedule refresh in Power BI service, use Gateway when needed, and voila”.

That’s in theory.

Firstly, we need to keep in mind that some organizations prefer to store everything within corporate network only.

Secondly, Excel still can handle more scenarios than Power BI. Or maybe not more, but many scenarios, that Power BI can’t perform. Due to Excel’s extensibility with add-ins and embedded programming language – VBA (Visual Basic for Applications).

Thirdly, far not everything is stored in the databases and accessible from Power Query – the main engine used to pull data to Power BI data models.

Excel add-ins and VBA help to fill that gap quite often for me.

As an example, in Excel I can dynamically change “Named Range” parameters for Power Query queries using formulas or via VBA.

Or, let’s say, you need to pull data sitting in SAP and for some reason not available in corporate BI system – here comes in handy SAP GUI scripting, which can be executed in Excel via VBA.

In the end, through many years, BI solutions come and go. Excel stays!

And if you want Excel to become even better, post your ideas or vote for existing ones at http://excel.uservoice.com.

Notable Ideas

Dear reader, using an opportunity, I’d like to ask you to take a look at the following ideas for VBA improvement.

Vote if feel it will be useful for you too.

Improve Workbook.Query object – add necessary properties – vote here

RefreshAll method has to be improved – vote here

SaveCopyAs for some reason doesn’t save file to OneDrive and SharePoint – vote here

Authentication to SharePoint should not be a problem in VBA – vote here

VBA Trends on Google

What do we know about popularity of Excel, VBA and Access around the globe – let’s ask Google.

Google Trends worldwide for VBA – despite high interest it is gradually declining

Interest over time
Nov 16, 20_
O
May 22. 2016
Nov 26, 2017
Jun 2, 2019

Considering popularity of idea to introduce Python to Excel it is probably worth to learn Python. However, at the time of writing, Excel has only VBA as embedded scripting language.

VBA in United States – slightly declining from 2016

Interest over time
Nov 16.20...
O
Note
May 22, 2016
Nov 26.2017
Jun 2, 2019

Excel and Access Trends

Excel and Access are probably two the most important “containers” of VBA code. So I decided to check what’s happening with interest to these Microsoft products

Microsoft Excel in United States – steady since 2016

Iterest over time
2019
50
Nov 26.2017
25
May 22,

However, Worldwide it is gradually declining

Interest over time
Jun 2019
75
Nov 26.2017
Note
May 22 2016

Interest to Microsoft Access in United States is decreasing over time

Very similar – Microsoft Access Worldwide

Interest over time
Nov 16, 20_
May 22, 2016
Nov 26, 2017
Jun 2, 2019

If you are still using Microsoft Access there must be a good reason for that.

Afterword

Clearly, VBA is not the hottest topic nowadays and it definitely requires modernization to stay competitive. However, even in current form it is still widely used.

To be honest, maybe simpleness of built-in IDE (not so many bells and whistles as in VS Code / Visual Studio) makes it easier to understand and accept by a common folk.

Does it worth to invest time in learning VBA, Excel or Access?

This I can’t tell you. All depends on your situation.

However, automation of tedious tasks in Excel has never been a bad skill.

Yammer Analytics in Power BI Part 3

Ivan Bondarenko March 4, 2019 No Comments

This is the third post in the series “Yammer Analytics in Power BI”

1 – Intro to Yammer Analytics in Power BI

2 – Paging through Yammer group messages

3 – Paging through Yammer group members

4 – Paging through Yammer user messages

Group Members

Querying group users (members) is a relatively simple task as we have an API for that

https://developer.yammer.com/docs/usersin_groupidjson

Note that API returns only 50 users per page, meaning we have to build a loop through unknown number of pages.

Basic Patterns

For getting users from Yammer group we can use following pattern:
https://www.yammer.com/api/v1/users/in_group/[groupid].json

or to get response in XML format
https://www.yammer.com/api/v1/users/in_group/[groupid].xml

For paging we need to add “page=N” as a query option
https://www.yammer.com/api/v1/users/in_group/[groupid].json?page=1

Using paging is as important as breathing, because as we may see from documentation, Yammer returns only 50 records per query.

It is not said in the documentation but API returns [more_available] tag

So we can use it to exit loop.

First Step

Let’s firstly build a “response catcher”. A function that will transform web response into a meaningful form.

We will be providing URLs with a following pattern

url_base = "https://www.yammer.com/api/v1/users/in_group/" & GroupID & ".json"

as an argument to a function fGetUsersPage:

(url as text) as table =>
let    
    response = Web.Contents(url, [Headers=[Authorization=#"Authorization"]]),
    body = Json.Document(response),
    moreavailable = try Logical.From( body[more_available] ) otherwise false,
    users = body[users],
    #"Converted to Table" = Table.FromList(users, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", 
        {"id", "state", "full_name", "job_title", "mugshot_url", "web_url", "activated_at", "stats", "email"}, 
        {"id", "state", "full_name", "job_title", "mugshot_url", "web_url", "activated_at", "stats", "email"}),
    #"Expanded stats" = Table.ExpandRecordColumn(#"Expanded Column1", "stats", {"following", "followers"}, {"following", "followers"})
in
    #"Expanded stats" meta [ MoreAvailable = moreavailable ]

This function returns a page with list of users (up to 50 items) and some of their attributes available through API. On top of this, response will contain a meta parameter – MoreAvailable, which takes true/false value. This meta flag will help us to stop “do-while” loop.

Pagination

I prefer to use List.Generate function for loops in Power Query, so I ended up with the following code that iterates through pages with Group Members and returns a table with users and their parameters

let
    Delay = 1,
    GroupID = Text.From( GroupID ),
    url_base = "https://www.yammer.com/api/v1/users/in_group/" & GroupID & ".json",
    Source = List.Generate(
            ()=> [
                i = 2,
                url = url_base,
                Page = fGetUsersPage(url_base),
                more = true,
                last_page = false // change to true when last page reached
            ],
            // arg2 = do while
            each [last_page] = false and [i]<100, // hardcoded limit, increase for groups with more than 5000 members
            // arg3 - iteration
            each 
            [ i = [i] + 1,
            Page = try Function.InvokeAfter(
                            ()=>fGetUsersPage(url_base & "?page=" & Text.From([i])), #duration(0,0,0,Delay) 
                        ) otherwise null,
            more = try Value.Metadata(Page)[MoreAvailable]? = true otherwise false,
            url = url_base & "?page=" & Text.From([i]),
            last_page = if not [more] then true else false // [more] in square brackets is a reference to the result of previous iteration
            ],
            // arg4 - output of each iteration
            each [[i], [url], [Page], [more], [last_page]]
    ),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"i", "url", "Page", "more", "last_page"}, {"i", "url", "Page", "more", "last_page"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded Column1",{"Page"}),
    #"Expanded Page" = Table.ExpandTableColumn(#"Removed Other Columns", "Page", {"id", "state", "full_name", "job_title", "mugshot_url", "web_url", "activated_at", "following", "followers", "email"}, {"id", "state", "full_name", "job_title", "mugshot_url", "web_url", "activated_at", "following", "followers", "email"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Page",{{"id", Int64.Type}, {"state", type text}, {"full_name", type text}, {"job_title", type text}, {"mugshot_url", type text}, {"web_url", type text}, {"email", type text}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"activated_at", type datetimezone}}),
    #"Extracted Date" = Table.TransformColumns(#"Changed Type1",{{"activated_at", DateTime.Date, type date}}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Extracted Date",{{"following", Int64.Type}, {"followers", Int64.Type}}),
    #"Inserted Age" = Table.AddColumn(#"Changed Type2", "Age", each Date.From(DateTime.LocalNow()) - [activated_at], type duration),
    #"Inserted Total Years" = Table.AddColumn(#"Inserted Age", "Total Years", each Duration.TotalDays([Age]) / 365, type number),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Total Years",{"Age"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Total Years", "UserYears"}}),
    #"Replaced Value" = Table.ReplaceValue(#"Renamed Columns","48x48","100x100",Replacer.ReplaceText,{"mugshot_url"})
in
    #"Replaced Value"

Highlights

I’d like to highlight some parts of this code as you might want to re-use these techniques.

1. To add a delay between API queries we can use Function.InvokeAfter

Page = try Function.InvokeAfter(
                            ()=>fGetUsersPage(url_base & "?page=" & Text.From([i])), #duration(0,0,0,Delay) 
                        ) otherwise null,

2. Use “try … otherwise …” to suppress potential errors, unless you really want to fail entire refresh process when one of the pages cannot be returned.

3. And the last tip I find really helpful – return all record fields in 4th argument of List.Generate

        // arg4 - output of each iteration
        each [[i], [url], [Page], [more], [last_page]]

It helps a lot with debugging as I may see steps of List.Generate: page index, URL, returned page table and “loop stopping” parameters (“more” and “last_page” in this case):

List.Generate output — List.Generate result expanded

That’s all for today.

Stay tuned. New posts are coming hopefully soon.

Yammer Analytics in Power BI Part 2

Ivan Bondarenko January 21, 2019 No Comments

This is the second post in the series “Yammer Analytics in Power BI”

1 – Intro to Yammer Analytics in Power BI

2 – Paging through Yammer group messages

3 – Paging through Yammer user messages

In the first post I shed some light on simple Yammer authorization approach and general Yammer API URLs.

In this post, let’s try to build a function to get group messages with their attributes.

(more…)

Intro to Yammer Analytics In Power BI

Ivan Bondarenko December 18, 2018 3 Comments

Have you heard about Yammer? It is a social network for organizations, kind of a “private Facebook”.

Let’s imagine your organization is using Yammer as an internal platform for digital collaboration, engagement, knowledge sharing etc.

It is a large international organization with employees around the globe (think big).

I have access to Yammer in one of such organizations since 2014 and in November 2014 I founded a group dedicated to “Data Analysis & Reporting” related questions.

IMHO, any large organization needs something like this. There are usually tons of company-specific questions around data & reporting: where to get data from, how to clean / transform, how to interpret etc.

With the good thoughts in my mind I started utilizing Yammer to spread the knowledge, share tips and tricks on various topics: Excel, VBA, Power BI, O365 services, SharePoint, MS Flow etc..

Why not in public place? Because of company-related technologies/data on screenshots and videos.

After a certain time I was obviously keen to know stats about my group. Does anyone read my posts?

Back in the days we used https://analytics.tryane.com for that. Great tool I have to admit!

Below are stats of my group after 1 year

It is not a secret that adoption of internal social media in any organization takes time.

That screenshot was made in Nov-15, pretty much one year since Yammer was widely introduced in the organization.

People were reluctant to use Yammer in the beginning. As of today, many are still considering it as evil, “waste of time” etc. But this is not something I want to talk about today.

You are here to know how to get data from Yammer to Power BI in the end.

Before pulling data to Power BI and reinventing the wheel, you might want to consider existing options like Tryane analytics or something similar.

For example, by default we have Yammer’s Group Insights, which it not a bad thing I should say, but too high-level.

Below is the last 12 months report for my group with 556 members at the time of writing.

Default ‘Group Insights’ feature gives a very high-level overview as you may see.

And we always want more, aren’t we? 🙂

Another option would be a solution from tyGraph for Yammer! Yes… but I couldn’t get it working. Perhaps, because I’m not a Verified Admin.

Ooooookay, but I just need the info about messages in my group and my personal messages (how many likes do I have, eh?)

Building Yammer analytics on Power BI

Fortunately, Yammer has the REST API.

https://developer.yammer.com/docs/rest-api-rate-limits

Documentation is far not perfect, but it provides an idea on where to start.

There is also a link to mass Data Export from Yammer, but again – for verified admins only.

Good news! If you are not a verified admin, you still can use GET requests to API.

Firstly, we need to connect to Yammer API. One of the easiest options would be using Microsoft Flow and export necessary data to CSV / Excel / Dataflow

However, O365 admins can limit list of allowed for MS Flow connectors.

As you may guess, for me this option doesn’t work. But you can give it a try!

Alternatively, consider making GET requests using Power Query to get data from Yammer

Explanation of methods in that documentation is not very detailed and not everything is covered.

For example, I was looking for a way of exporting all my messages.

In other words, “all messages from a user with a certain ID”.

Ended up Googling for that query.

Crikey! Seems like Queensland government uses Yammer!

Google has indexed one of their documents

Link to that doc: https://www.forgov.qld.gov.au/file/21891/download?token=5DVxAoBr

It covers following

1. export all messages from a group

2. export new messages

3. export group members

4. export all messages from a user

5. export all messages in a conversation

Querying Users of a Certain Group

Shortly, for getting users from Yammer group we can use following pattern

https://www.yammer.com/api/v1/users/in_group/[groupid].json

To get response in JSON format, or

https://www.yammer.com/api/v1/users/in_group/[groupid].xml

To get response in XML format.

For paging we need to add “page=N” as a query option

https://www.yammer.com/api/v1/users/in_group/[groupid].json?page=1

Using paging is as important as breathing, because by default Yammer returns only limited number of records.

For example,

https://www.yammer.com/api/v1/users/in_group/[groupid].json

Returns only 50 users per page.

If you are eager to test this yourself, open your group page in a browser and get Group ID from the URL.

Build a query string using pattern above and navigate to it in the same browser, just in a different tab.

That way you don’t need to enter credentials second time as you has been authorized in that browser (when opened your group page).

On the screenshot below you may see fields provided by a standard response to https://www.yammer.com/api/v1/users/in_group/[groupid].json

As it is not an OData service, we don’t receive NextLink in the response, hence cannot utilize code from TripPin Tutorial (best PowerQuery vs OData tutorial available on the web today).

The only indicator of the next page existence is “more_available” tag, which keeps “true” value until we get to the last page.

It turns to “false” once we reach the last page

I have to add here, that current version of Yammer API doesn’t allow us to define list of fields we need, so we receive a standard response (leave a comment if I’m wrong).

What could be good to know about user for the resulting analysis?

User ID – can be used to count # of unique users

Name – to display user names when we drill down to user level

Job Title – allows to roughly count number of managers reading post

Mugshot_Url – link to an employee photo.

Activated_at – allows to split users on new/old employees.

Stats – users with the large (higher than average) number of followers can be treated as “VIP-employees”, so we can count likes from them as “super-likes”.

Web_Url – could be useful if you need to provide a link to user profile from your report

Rule of thumb: “keep your data model lean – don’t import columns that you don’t plan to use”.

As it is an “intro” post, I won’t publish queries with paging here yet, it will be in the next post.

From Theory to Practice

“Enough theory, show us practicalities!” – I clearly hear from you, dear reader. Fair enough!

Querying API from Power BI is not the same as navigating to the URL in a browser because we have to provide authorization token with each individual request.

There is a separate section about authentication in the Yammer API documentation

https://developer.yammer.com/docs/api-requests

IMHO, the simplest way is to use “Authorization” query option and provide a “Bearer value”

Where can we get a token value from?

Navigate to: https://developer.yammer.com/docs/userscurrentjson

Find a “Key icon”, Log In, copy token

This is a temporary token, that will expire after a certain time, but this is the simplest option for getting started.

Alternatively, you can register an application and get developer’s token, read more here: https://developer.yammer.com/docs/test-token

Finally, we can create a query in Power BI.

Query to Yammer in Power BI

Go to New Query -> From Web, switch to Advanced Mode

Use https://www.yammer.com/api/v1/users/current.json to test your connection.

Paste URL and add Header “Authorization”.

Type and paste “Bearer token” (don’t miss a space between “Bearer” and “token value”)

Click “OK” to test your query, you should receive an info about yourself

That’s all for today.

In the next post I’ll explain how to build a query with paging through the group members and messages, and through specific user’s messages.

Stay tuned and thank you for reading!

Set of CSVs as a database for Power BI

Ivan Bondarenko September 10, 2017 No Comments

What if part of your reporting database is a set of CSV files?

Apart from possible problems with different number of columns, data types, delimiters, encoding etc., you have to care about performance.

According to my practice, large number of files kills productivity. It is better to firstly combine CSV / TXT files into one, then use Power Query to load it.

(more…)

Publish Excel file to PowerBI using VBA

Ivan Bondarenko April 21, 2017 No Comments

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

(more…)

Shift cells up/down in same column in Power Query

Ivan Bondarenko March 14, 2017 3 Comments

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?

(more…)

Bar-Mekko chart in Excel with Power Query

Ivan Bondarenko February 13, 2017 3 Comments

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?

(more…)

Part 2: Combination of rows of tables list in Power Query

Ivan Bondarenko September 22, 2016 1 Comment

Part 1 describes approach showing how to work with List.Generate, Table.FromRecords, List CrossJoin in Get & Transform (aka Power Query, M language).

Homework is done. Advice is digested.

And I’m ready to introduce another solution to combine multiple tables rows.

It is so simple, and can be done in UI!

All regards and honor should go to Imke Feldman (http://www.thebiccountant.com/) as she found that “epic Pokemon”.

I looked at this task as a programmer, thinking about loops and iterations.

Imke – completely differently – as simple user, don’t even thinking about programming, as she said :-).

Remind, we start from four tables

And want to mix all rows to get all possible combinations (indexed).

How to do this using UI only? (more…)

Combination of rows of tables list in Power Query

Ivan Bondarenko September 20, 2016 6 Comments

Recently I faced interesting Power Query problem. Actually, initial problem has nothing related to Power Query.

I required to export quite big volume of data from SAP BW using Business Objects Analysis addin (BO Analysis, or BOA) and save as CSV. Amount of data is literally huge, so it was impossible to export it in one query and even in 10.

I wrote a VBA macro with a loop, where each iteration had to 1) set variables and filters 2) refresh data 3) save result as csv.

So, I had to prepare set of variables and filters for each step of loop, setting filters on several dimensions.

Following four tables describe all possible filters I needed:

(more…)