How To: SQL to Calculate Average of non-Zeroes from a List of Values

I don’t do much coding these days but here’s one I’ve done recently and thought about keeping it for future reference Smile

For a list of values, how to calculate the average only using the ones which are non-zeros?

CREATE FUNCTION dbo.fn_AverageNonZeroes 
(@para int, @parb int, @parc int, @pard int, @pare int)
    DECLARE @average DECIMAL(4,2)


    SET @a=CONVERT(DECIMAL(4,2),@para)
    SET @b=CONVERT(DECIMAL(4,2),@parb)
    SET @c=CONVERT(DECIMAL(4,2),@parc)
    SET @d=CONVERT(DECIMAL(4,2),@pard)
    SET @e=CONVERT(DECIMAL(4,2),@pare)

    IF @a>0 OR @b>0 or @c>0 or @d>0 or @e>0
     SELECT @average=(@a + @b + @c +@d + @e)
             CASE WHEN @a=0 THEN 0 ELSE 1 END +
             CASE WHEN @b=0 THEN 0 ELSE 1 END +
             CASE WHEN @c=0 THEN 0 ELSE 1 END +
             CASE WHEN @d=0 THEN 0 ELSE 1 END +
             CASE WHEN @e=0 THEN 0 ELSE 1 END
         SELECT @average=0.0
     RETURN @average


Microsoft vs Apple vs Facebook vs Google vs Amazon (in 2017)

Here some interesting stats collated by Visual Capitalist. I thought of creating this post just so I can reference it later. In short:

  • Microsoft is the leader in portfolio diversification.
  • Apple and Alphabet have a good portfolio but little diversity.
  • Amazon has good market cap and some diversity.
  • Facebook is a one-trick pony Smile











Source: companies annual reports, May/2017 – visualisation by


My Project Online Administrator is Locked! How to Fix?

Today I've got a call from a very worried customer: "Help, our Project Online Administrator got locked out! What do we do???"


First, let's bring calm into the conversation. You know people will live after that. If your Project Administrator got locked don't panic, the site collection administrator automatically has administrative privileges. So to fix it quickly, ask the tenant or SharePoint online admin to fix this for you and assign you or someone as site collection admin and then you can go and fix the Project Admin. Done!

Now let’s talk about the B-side.

First, How Did you Get Into this Situation?


I know, soul searching is not easy. If you know what caused the issue, great otherwise you should start asking questions. That's a very important first step to understand what happened. Generally think about the last steps performed in the site. Look for things like:

  • Any changes happened to the Administrators in Project Server or in the Administrators from Active Directory
  • Any synchronization happened to the AD group in the Resource Center. Maybe this happened and your Admin became inactive.
  • Any bulk password changes happened in your organization
  • Any user migration recently happened in your organization

You know, things like this to give you a sense of what caused the issue.

So well done! You fixed the problem and you understood what happened.


Let's Learn from this Event…

Let's lay the basics out here. Your company's Office 365 tenant have:

  • A global administrator,
  • A SharePoint administrator,
  • A site collection administrator,
  • A Project Online administrator.

Experience tells us that 90% of the time, there is someone which is incorporate ALL of these roles. Pick the person that is the Global Administrator and let's have a chat with this person and find out if anything special has been done to the Project Administration role. If this is the person that helped you reverting the situation as we talked before, even better. Discuss the situation with him/her. Share with him what  you think caused the lock.

What About Licencing?

Often times Active Directory changes alone can fix lockout situations, in these cases a site collection admin does not require a dedicated Project Online license. The challenge here is sometimes to fix the issue, the person needs to log into Project Online and in this circumstance the site collection administrator needs a Project Online license. It’s possible that he or she already has one.

I Don't Have Licences Left. What Do I Do?

You can temporarily remove licences from an user and assign to other for this. The licences are not named licences, so that's a perfectly legal manoeuvre. Better yet, remove the license assigned to the locked out user since he/she is not able to use it anyway while you fix this.


Free Stock Photos Woman in IT, #STEM

Do you love to support the efforts to attract more women to #STEM ? Do you support a more diverse workplace, outside of your typical blue eyed, white man, in glasses and fancy suits?

Do yourself a favour and start using this great catalogue of stock photos representing women in technology, from diverse backgrounds and biotypes. I know some of these photos are full of Microsoft logos but a lot of them aren’t, so go ahead they are royalty FREE by WOCinTechChat! Bring new life to your presentations, support a more diverse culture in technology Smile

For the full album of free stock photos of women in IT, STEM click here…







Managing Thousands of Projects in Project Online

If you work with Microsoft Project Online you know how great this product is at streamlining project, resource and portfolio management activities. All this is for a single purpose, to allow you to deliver better projects, on time, on budget.

And now a few changes were released this month. Let have a look at 2 very special ones that I would like you to know:

Create and Manage Up to 30,000 Projects

Traditionally, Project Online  allowed for up 5,000 projects. Starting April/2017 this limit will have a significant jump. 6x to be more precise. Now project managers will be able to create and manage up to 30,000 projects in the cloud using Project Online. What was already a fantastic number, now has just increased to great lengths.

Why This is Good?

It offers a better scale, 30,000 projects allows for a much bigger room to move and improve and at the same time offer more flexibility to get more things done.


Pick and Choose the Site URL for your Project Site Configuration

Another great improvement on the way things are done in Project Online is on how information is organized in SharePoint sites. Now project managers can create more project sites and every project now can have a 1-1 relationship with their project site.

Why This is Good?

Again, traditionally this number was limited to 2,000. Often times more sites were needed but this limit was reached, then what people used to do was to split your landscape into several PWA sites, which then extended to another batch of 2,000 sites but the trade-off was that it impacted in the team productivity with a fragmented environment.

…One More Great News Before You Go…

In addition to these great improvements, there is one more fantastic improvement done in Project Online this month: Now users can set the destination URL of the project sites in the Enterprise Project Type template. This not only helps with the meta-hierarchical structure of the organization but also streamlines the process of project creation allocating sites based on similarities and functional equivalences.

How It Works?

For example, if you company has a site collection dedicated to a specific department and all sites are located on it (Sales, Marketing, IT etc), let’s call it for demonstration purposes, now you can configure your all your departmental projects EPT to provision sites directly into this site collection


You do this by going into each EPT, then in the Project Site section, select Allow users to choose:



What if You Can’t See these Things in Your Environment…

This is a Question that many times people ask me when they see these cool announcements and all the new features and yet they don’t seem to get them. Don’t worry, that’s the nature of Office 365 Releases. Releases are done by zones and via multiple channels. Very often these changes are done slowly and according to geographic zones, so if you don’t see them yet, don’t worry. They are coming!


The Test of the Machine


The test of the machine is the satisfaction it gives you. There isn't any other test.
If the machine produces tranquillity it's right. If it disturbs you it's wrong until either the machine or your mind is changed.​​​
One day, the machine will break and you will be in charge of fixing it.
- "Is it hard?" people will ask you
- "Not if you have the right attitudes!! " You will say. "Its having the right attitudes that's hard.”​​​


The Importance of Continuous Development and Training



‘’Sensei is the Japanese word generally associated with 'teacher'. In the Samurai culture, a sensei aim to be a samurai, and through continuous improvement a samurai is always a Sensei. Tokugawa Ieyasu, one of the greatest Samurai in history was not only an expert swordsmen but also a Zen practitioner. He believed that through Zen a state of no-mindedness was reached. Once in combat, he believed, an empty mind is free and has no need for doubt, fear, calculation. The actions are done automatically by the technique and pure focus. Keep that intro in mind, we will come back to it in a minute.

Looking at the project challenges as battles, it is important to have an empty mind in the Zen sense. We should let our pure focus and technique (developed and filtered by successful project experiences) should drive the outcomes.

Thus the importance of constant training, to sharpen the PMO skillset for battle, so then when we’re in the midst of a challenging project task we let all thinking go and let the purity of action to take over and move forward. A focused mind is the formidable tool against scope creep.

Now, back to the intro, Tokugawa Ieyasu practiced cutting bamboos hundreds of times a day, every day, to demonstrate to us, modern day warriors, that not only we are learning everyday but also that it requires a life-time of dedicated practice to achieve great swordsmanship. Become the Samurai warrior in your project you too as well.

What do you think? How has continuous training (of the lack of) shaped your professional role?


Why Cloud Projects Fail?

Businesses are moving to the cloud faster than ever with the premise that, in the cloud, project challenges can be tackled faster and efficiently. Yet, new research reports staggering cloud project failure rates. In this session we will discuss the 6 not-so-obvious reasons cloud projects fail and fool proof strategies to make sure project managers are prepared for them.

Thank you everyone who took the time to join the webinar and discuss this very interesting topic. Fee free to reach out to continue the conversation!

Presentation slides can be downloaded here: The 6-not-so-obvious Reasons Why Cloud Projects Fail



How Secure is my Data in the Cloud?

This is by far the most common question I get when talking to people about cloud migrations. And rightly so, information security (together with data privacy) is amongst the top concerns for businesses looking to go to the cloud computing.


To be quite frank:

  1. Generally the data stored in the cloud is very secure and
  2. Most likely it is safer then it currently is in your company.


A New Model Brings New Threats

Now let’s not make a mistake here, cloud computing does raises security issues that are not typically found when your data is stored on-premises in your own building. So much so that industry standards are still adapting to cloud specific challenges. For example, not many people realises that your company data might be stored in a common datacenter where data from other companies are stored as well. The concept of multi-tenancy scares a hell of a lot of people.


Who is Ultimately Responsible for Securing my Information?

This is an important question and before we answer that, let’s take a step back and discuss the matrix of responsibility here. To answer that question, first we need to assert the nature of the information stored because it is the data sensitivity who will dictate the level of infrastructure security necessary.

OK, so now that we have that in mind, the second part is about addressing the responsibility. The cloud provider is most likely to be responsible for the security and privacy controls which will be available to you.

Then now you have it:

  1. The cloud provider is responsible for the infrastructure lockdown, and
  2. the customer is the one who drives the data protection requirements.

It does not matter that you store your data in the cloud but still uses weak password controls or poorly governed processes around your business and data access, right? That’s why a proper cloud security assessment is so important because most likely, when a data breach happens it is because of you. Don’t be ashamed to assume if you have, weak security controls is something very pervasive in our industry as you can see here.


What Are my Responsibilities as a Cloud Customer?

You need to understand what security controls your business need, what data breach controls you want, what regulatory needs your industry requires etc. Cloud companies like Microsoft or Amazon won’t know that. It is your job to know that and bring it to the discussion table. Like I said before, these cloud companies are extremely capable and secure so normally it is up to the customer to up their game.

In short you as a customer have to:

  • Understand your data sensitivity
  • Ensure your chosen cloud provider offers the controls your business need

So there you have it. Generally the conversation here evolves to So nothing ever goes wrong in the cloud? Are you telling me that data breaches never happen?”…no, but this is a talk for another day.

See ya later!


Surface Pro 3 Battery Problems

Do you have a Surface Pro 3?


Are you experiencing very low, fast battery duration like the case mentioned below? (from winsupersite)


If yes, looks like you’re in for a bit of a pain. Microsoft has acknowledged some Surface Pro 3 models have a battery issue where the battery charge is continuously draining and soon it will be unusable. Think in terms of, 1 or 2 hours max of duration. A far cry from ideal. There is a thread now also in Reddit on the topic and on the apparent silence from Microsoft on this.

How Do I Know My Surface Pro has this Issue?

The issue is related to the manufacturer, called SIMPLO. All models that have a battery from this manufacturer have or will have this problem.

How Do I Know the Battery Manufacturer of my Surface Pro ?

There is a neat command-line utility in windows that gives you that information. In your command-prompt type the command powerfcg /batteryreport


This will generate an HTML report about your battery, including the manufacturer as you can see here:


If the manufacturer reads “SIMPLO”, your device has a problematic battery. In this case, the manufacturer is “LGC-LGC” which indicates this device has an OK battery.

What Should I do if My Surface Pro 3 has a Bad Battery ?

If you’re Surface has less than 1 year, you’re covered by warranty. After that, you should raise your voice and complain to  Microsoft here. There is already a growing list of people doing that and pressing Microsoft for a reasonable solution, after all it is a poor component.


I’ll be at the GovHack Australia 2016!

I am humbled, honoured and excited to be part of the GovHack Australia 2016. I will be there as a Team Leader helping with the idea and concepts of the solution. If you never heard about this event, let me explain a bit more about what it is.


What is GovHack?

GovHack is an event that build teams to create innovative solutions using Open Government Data. Teams are formed with project managers, entrepreneurs, developers, designers, researchers, open data enthusiasts etc. Even story tellers are in there Smile Have a look at the report from last year’s event here.


What is a Hack?

A hack is to take something and make it better.  Our teams will look at the available data exposed by open government data sources and make a cool, useful and engaging application.


How Does it Work?

On the Friday night launch the competition categories are announced, the teams are formed and the event runs for the next 46 hours. The teams will then look at the datasets and create things with them. The best applications are in for prizes in International, National and Local categories. At the end of the GovHack a proof-of-concept and a video are created explaining the solution. Teams work through the weekend and by Sunday 5pm a 3 minute video of your concept and any code/source materials must be made public.


When is GovHack Happening?

In 2016, GovHack will happen from 29th to 31st July. Yep, the Prime-Minister supports it Smile


Who is Behind the GovHack?

This is a non-profit event proudly run by volunteers who form the GovHack Coordination Team. Our ongoing thanks to everyone who gets involved and makes GovHack awesome! That is, the hackers, data providers, sponsors, mentors and a special thanks to the volunteers who run Local GovHack events.



Who is Sponsoring GovHack Australia 2016?

Several big IT companies and Fare behind this effort such as:




How to Write a Better Technical Document…and Use Windows Bash with it

Writing a good technical documentation is hard. Most people don’t appreciate this fact. It is hard because we are emotional beings by nature and technical writing is…well…emotionless Smile The less emotion, the better.

As a person from a Latin background, it is especially hard for me to avoid emotion in my writing. So after many years of hits and misses, I’ve compiled my own guide that helps me stay in check. It is not perfect but I believe it is a good start. Using it helped me to increase the number of hits while lowering the misses and at the end of the day that’s the goal: Not aiming for perfection, but continuous improvement Smile It can be quickly summarized in the following graphic:


Interesting thing is, you can use this even when writing in social media. I am also someone who is slowing moving away from the operational IT tasks and more into the business and stakeholder dialogues. Understanding is paramount for this phase of my career.

Oh yeah, before I forget: I don’t care about these recommendations here in my blog. I want my blog to be organic and the closest as possible to the way I speak in real life. Cool? Winking smile

Checklist for a Better Technical Writing

# Check for This… If Yes, Do This…
1 Words “I” or “We” Replace them. The document must be written in the 3rd person
2 Sentences are  written in different verbal tenses Make sure the whole document is written in past tense
3 A section called “abbreviation/nomenclature” section in your document Make sure it towards the start of the document
4 Use of jargon. (eg.:“the system will go live on xyz”) Avoid jargon with a clearer expression (“the system will be available for general use on xyz”)
5 Abbreviations in the “executive summary” or “abstract” sections avoid them. Expand to their real meaning.
6 Usage of word “that” All “that” has to be used when you know whom you are replacing
7 Usage of word “which” All “which ” has to be used when you are unsure who is being replaced
8 Word “So” Make sure “So” does not start sentences
9 Word “but” Replace them with “however”
10 Word “To” Make sure it is not at the start or end of a sentence
11 Words with “-ing” suffix Make sure they are not starting sentences
12 Graphics, equations, tables in the document Make sure they are addressed in a table of contents
13 Check for judmental constructions “It is very easy to perform the steps A, B, C” or “it is very difficul to determine the result of D, E, F” Consider removing it or replace with cold, hard construction. What’s easy for you might not be for others and you are telling the readers are incompentent if it does not work.
14 Statistics and numbers Make sure their sources are mentioned
15 Word “will” Replace them with “may”.
16 Word “Obvious”, “Of course” Remove them at once. If it is obvious, no need to state. You’re implying the reader is incompetent.
17 Sentences with exclamation marks Remove them at once.
18 Words “can’t” and “won’t” Replace them with “cannot” and “will not”
19 Expressions in parenthesis “()” Consider replacing them with a comma
20 Numbers in sentences Replace for their full wording: “it is 5 MB” should be “it is five megabytes”

But I am Still an Techie at Heart

and considering that I still love to be a hands-on kind of guy. What you will see now is a series of scripts that can be used in your documents to automate the repetitive task of finding words and expressions that convey a colder and more understandable way to send a message. If you are a techie at heart like me, you will enjoy them Smile That are done in Bash.

Bash is coming to Windows platform and now there is an expectation that people will quickly pick it up like they did with PowerShell. Once that happens and you also become a fan of Bash, use the following scripts for your work (from Matt):

Bash Script to Find Weasel Words

Weasel phrases or words are those that make you sound too good but does not really convey any meaningful information. Basically they make things unclear for the reader. For example: ”It is quite difficult to find untainted samples” when a better was is “It is difficult to find untainted samples”

|mostly|largely|huge|tiny|((are|is) a number)\
# Check for an alternate weasel file
if [ -f $HOME/etc/words/weasels ]; then
if [ -f $WORDSDIR/weasels ]; then
if [ -f words/weasels ]; then
if [ ! "$wordfile" = "" ]; then
    for w in `cat $wordfile`; do
if [ "$1" = "" ]; then
 echo "usage: `basename $0`  ..."
egrep -i -n --color "\\b($weasels)\\b" $*
exit $?


Bash Script to Find Passive Voice Usage

Passive voice is a hard one. You can find it everywhere because it is related to the way we think and process sentences. When people advise to read and re-read the text before publishing it, it is because we want to pickup the passive voice. Passive voice usage is bad because often it hides explanatory information, for example: “Termination is guaranteed on any input” instead of “Termination is guaranteed on any input by a finite state-space”

if [ "$1" = "" ]; then
 echo "usage: `basename $0`  ..."
egrep -n -i --color \
\\b[ ]*(\w+ed|($irregulars))\\b" $*
exit $?

Perl Script to Find Lexical Illusions

A lexical illusion is another form of visual illusion, normally happening when a like break contains repeated words but due to the way we read things, it is not easily spotted. Microsoft Word has actually a very good Lexical Illusion detection feature but still a lot of those can be left in place.


#!/usr/bin/env perl
# Finds duplicate adjacent words.
use strict ;
my $DupCount = 0 ;
if (!@ARGV) {
  print "usage: dups  ...\n" ;
  exit ;
while (1) {
  my $FileName = shift @ARGV ;
  # Exit code = number of duplicates found.  
  exit $DupCount if (!$FileName) ;
  open FILE, $FileName or die $!; 
  my $LastWord = "" ;
  my $LineNum = 0 ;
  while () {
    chomp ;
    $LineNum ++ ;
    my @words = split (/(\W+)/) ;
    foreach my $word (@words) {
      # Skip spaces:
      next if $word =~ /^\s*$/ ;
      # Skip punctuation:
      if ($word =~ /^\W+$/) {
        $LastWord = "" ;
        next ;
      # Found a dup? 
      if (lc($word) eq lc($LastWord)) {
        print "$FileName:$LineNum $word\n" ;
        $DupCount ++ ;
      } # Thanks to Sean Cronin for tip on case.
      # Mark this as the last word:
      $LastWord = $word ;
  close FILE ;

For more tips, you can have a look at 

So there you go. These are my tips. Share yours in the comments and I will include them here and credit it to you.


IoT, Office 365 and the Prime Position of STEM Kids in Australia


5 million new IoT (Internet of Things) devices will come online every day in 2016. How can companies join the party for a piece of this 50 billion dollars’ cake?

Come to Microsoft Innovation Centre in Brisbane (level 28, 400 George st, Brisbane) and join me to discuss:

  • The state of connected devices in Australia,
  • How can we use IoT and Office 365, and
  • Why Australians are in a prime position to lead the world in this area and the role of the STEM programs for kids is an important strategic move in the innovation agenda.





Where Can I Download the Presentation Slides?

You can download the slides for this presentation here.


I am Speaking at the Collab365 2016 Event

Once again I got confirmed as a speaker for the Collab365 Conference. This is one of the coolest conferences around from the people organizing it to the folks participating online. A single session can easily reach thousands of people, so you can imagine my excitement. And as a plus, it is always great being at Microsoft HQ in Redmond. Also a good time to reconnect with folks from the Office 365 team in their offices. (*cough*also visit the Microsoft store*cough*)

400x133_speakerbadge_summit.jpg (400×133)

My session is as usual around Office 365 Compliance and Data Protection and it is called: "Office 365 eDiscovery: DLP for your Business and Your Data".

Join the Collab365 Summit and watch the sessions, there is an incredible amount of brilliant minds and souls behind this event, doing this for the community at large.

Where Can You Get More Information and Watch the Sessions?

Go to . You can also follow the twitter @Colla365 and the hashtag #Collab365

Also, who doesn’t love an opportunity to visit Microsoft Headquarters in Redmond Smile