VBA not dead, yet.

excel access vba visual basic

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.

  • 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

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

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.

How I ruined Power BI Desktop file

This is a short story of how I ruined one of my Power BI Desktop files. Luckily I stored it on SharePoint where I have versioning enabled, so I could restore a working version.

In that Power BI report I used an Excel file as a source and decided to turn it into a parameter for other queries – Binary parameter.

BTW, I faced that issue using Power BI Desktop June 2019 version from MS Store.

Parameter

When you create a parameter in Power BI Desktop, you can choose type Binary, but cannot set a value – fields are greyed out

In my case, I wanted to link a parameter to another query. There is no such option in the UI.

So, I created a parameter and opened it in the Advanced Editor to change type to Binary and replace value with a reference to another query #”SourceFile” .

My Mistake

I wrongly entered parameter’s Type, used “binary” instead of “Binary”. I don’t usually use this type, so didn’t remember the correct spelling, I though it should be in lowcase as other frequently used types “text” or “number”.

And got an error message

I reported an issue and clicked Cancel.

Then by mistake clicked on the Close button which closed window with parameter’s code with no questions

For some reason I decided to “Close and Apply” at certain point.

I didn’t know this will be the end of story for my report.

Soon, I tried to get back to the queries but couldn’t. Yes, I couldn’t open Power Query Editor window anymore – lost access to this area of Power BI Desktop file.

Every time I’m trying to open it I see this error window

Conclusion

Those who watched Chernobyl by HBO, probably remember old good Russian proverb “Trust but verify” (Доверяй, но проверяй) said by the head of KGB in one of the episodes. This applies to your files and the way you work as well. “Trust” that everything will be OK with your files, but “verify” by storing them in the locations with versioning enabled such as OneDrive or SharePoint and don’t forget to save frequently during active development phase.

Yammer Analytics in Power BI Part 3

List.Generate Power Query

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

Yammer Users in Group API parameters

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

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

yammer group analytics

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!

Filter Excel Function CUBEVALUE Connected to Power BI

cubevalue cubeset formula

You might know that we can connect Excel to Power BI using “Analyze in Excel” feature.

If this is something new for you – check out following video (3 min)

By default, after clicking on “Analyze in Excel” you get Excel file with Pivot Table connected to Power BI.

There you can add rows, columns, slicers – everything as usual.


But what if Pivot Table is not what you need?

What if you have pixel perfect dashboard which you need to fill with CUBEVALUE formulas (or maybe you use Think Cell addin)?

And what if you want dropdown list of values in cell instead of slicers (or want filter to be generated via formula)?

Let’s check what we can do.

CUBEVALUE formulas

The easiest way to get CUBEVALUE formulas instead of Pivot Table is to convert Pivot Table to Formulas


That action deletes Pivot Table and replaces all its cells with functions CUBEMEMBER…


 

…and CUBEVALUE


As you may see, CUBEVALUE is referencing to a Slicer with countries and a cell with a CUBEMEMBER function.

Replace single value slicer with a cell

Everybody likes slicers, me too. However, in certain scenarios it is better to have a cell with value instead.

Why?

(more…)

Data type conversion in custom columns

data type conversion

You probably know that you can manually set data type for custom column created in Power Query with help of Table.AddColumn function (using 4th argument).

Rick de Groot has recently published a good post about this at Gorilla.BI.

Just be sure you use right data type for the result of your calculation.

Once I faced strange Power Query behavior, as I thought, but later I understood that everything works fine (probably).

(more…)

Export PowerQuery query to CSV

Recently I found a PowerQuery gem, trick with Java/VB Script that allows to export data from Power Query to CSV without R / DAX Studio / SMS and Registration. However, related with risk. As everything else in our life.

Kudos to user Shi Yang from Stack Overflow who replied to How to write to DATA sources with Power Query?.

Shi proposes to use following code (extended with my comments)

Let
     // reference to a query you wish to export to CSV
    Source = ReferenceToYourTableOrQuery,
     // demote headers to have headers in resulting CSV
     // if you don't need headers, remove Table.DemoteHeaders
     Json = Text.FromBinary(Json.FromValue(Table.ToRows(Table.DemoteHeaders(Source)))),
     // trigger execution of script
  Export = Web.Page("
 var fso=new ActiveXObject('Scripting.FileSystemObject');
 var f1=fso.CreateTextFile('C:/Temp/test.csv',true);
 var arr=" & Json & ";
 f1.WriteLine(arr.join('\n'));
 f1.WriteBlankLines(1);
 f1.Close();
 ")
 in Export

All great, but this method doesn’t work with default settings of Internet Explorer.

(more…)

Transform Columns with List.Zip

This post continues series of articles about M Function List.Zip ( first post, second post).

Table.TransformColumns is another function that requires list of pairs if you want to transform several columns in Power Query


When we want to transform all fields we can simply use

Table.TransformColumns(#"Changed Type", {}, Text.Trim) 

// would be great to see same behaviour for Table.TransformColumnTypes

However, when we need to change only part of the table we have to generate list of pairs {column name, function}.

Text.Trim highlighted in last sample is a good example of transformation function.

In general, transformation function can contain any logic and can transform objects of any type.

(more…)