Free Marquee™ for Google Sheets Template demo

Microsoft Power BI isn’t just for getting data from Microsoft products. The PBIX demo file that you can get once you register below, allows you to query the data from your Google Sheet into Power BI and then share resulting reports and dashboards via PowerBI.com with co-workers or the world if you desire. If you have the Power BI mobile app, you now have Google Sheets data on the go.

Demo File Only

This PBIX  is provided as a demo only, with no support or warranty offered as a result. Testing was only sufficient for a demo and not for production use. You may encounter errors in your environment with the use of this model in it’s current state. You are welcome to expand the solution. If you do, please add to the comments below so that we can all share from your experience.

Note: the PBIX file only connects to the first tab in your Google Sheet.

Google Sheets API Oddities

This was an interesting project as Google Sheets doesn’t have the same concept of table as Excel does. Therefore, there’s two conditions you may encounter for which we don’t yet have a good solution.

First, you shouldn’t have a blank column heading. This will cause the model to error out on the last data transformations as Power BI expects column headings to be present.

Second, the Google Sheets API doesn’t appear to return cells that are null that are in the last column of your sheet. Since the cells are returned as a list and we fold the list every X rows, this throws off the row count and fold points. As a workaround, we recommend having the last column of data have values in all cells.

Send me the data model!

 

Setup

You need three pieces of data in order to use this PBIX file.

  • The number of columns in the Sheet
  • Your Spreadsheet ID
  • Your Browser API Key

Steps to get your SpreadsheetID

  • Navigate to your Sheets page.
  • The key is in the URL, see the bolded red text below.
    • https://docs.google.com/spreadsheets/d/1gLzc8AxdlUl1MPY4t2ATKjc1UfBNj7iUaHRexwLYSKQ/edit#gid=0.

Steps to get your Browser API Key

  • Log into your Google account
  • Click this link to start the wizard.
  • Click Continue button to create a new project.
  • Unfortunately, you have to create the following, even though we won’t use it.
  • Click Go to credentials button.
  • Select Web Browser (Javascript) in the Where will you be calling the API from?
  • Select User Data under What data will you be accessing?
  • Click What credentials do I need? button
  • Click Create client ID button
  • Enter a Product name
  • Click Continue button.
  • Click Done button.
Now to create the credential we need for this to work.
  • Click the Create credentials button.
  • Select API key.
  • Select Browser key.
  • Give it a name and click the Create button.
  • Copy the API key and paste it into the BrowserAPIKey parameter.

Setting Up Your PBIX File for Use

Once you receive your PBIT file, do the following.
  • You must have Power BI Desktop installed prior to performing this procedure.
  • In File Explorer, double-click on the Google Spreadsheet Template – Final.pbit file.
  • Power BI Desktop will open and you will be presented with this dialog.
  • Fill in the values and click the OK button.
  • The model will refresh and it should load your Google data.

Setting Up Scheduled Refresh on PowerBI.com

Once you have saved the model, verified the data and built your reports, you can publish this model to PowerBI.com. Once there, you can set it up to automatically refresh the data so that any reports and dashboards are up to date.

Procedure for Scheduled Refresh

  • In Power BI Desktop, click File, Save to save the model
  • Click Publish
  • If you aren’t signed into Power BI, you’ll be prompted to do so.
  • You may be prompted for the location to publish. My Workspace is the default
  • Once done, go to PowerBI.com.
  • Follow the procedure in the video below.
  • Navigate to the Datasets in the left navigation to start the process.
  • Note, the API key you entered earlier in the model is your login. This is why it is set to anonymous in PowerBI.com.
Send me the data model!

Chaos Management and the Cubicle Hero

When asked, “What do you want to be when you grow up?” you may have replied, “Firefighter.” If you did, I’m sure you meant one of the awesome individuals who provide medical services, rescues and ride the fire trucks. While, most of us never realized that dream, there are days at the office where you probably feel that “Fire Fighter” should be your job title.

Welcome to the wonderful world of the Cubicle Hero, where fighting fires is part of your job!

Perhaps you ask yourself at the end of each day, “How did I get here?” Many feel stuck in these roles without a way out and are puzzled as to how it happened. I talked about the True Cost of the Cubicle Hero in this previous article, so let’s look at how Cubicle Heroes form.

One reason Cubicle Heroes arise is due to a work environment that isn’t structured to respond well to chaos. If there are no processes for reacting to chaos in a controlled manner, the result is a crisis, which requires some brave person to step in to address. This person is caught in that role going forward, thus evolving into the Cubicle Hero. Chaos is ever present and needed for the organization to evolve and remain competitive. The organization is going to run out of Heroes unless a systemic way of reacting is created.

Internal efforts such as implementing a new HR system creates short-term chaos and long-term impact the organization. If your organization doesn’t have a formal project transition process to production, Cubicle Heroes usually form from the project’s team members who hold the detailed knowledge about the project’s deliverables . A problem related to the project arises. This leads to a project team member solving the issue and then becoming the Hero going forward.

Ad hoc project transformation process creates “human hard drives” out of the project team members, where they must store and retrieve organizational knowledge as needed. This restricts the ability of team members to grow their skills as letting go of that knowledge results in a loss to the organization. A formal transformation process ensures relevant information is captured so that it can be widely used within the organization, freeing the team members to move on.

External events such as a large client with a new, immediate need or a viral photo of a dress of indeterminate color are also chaos sources. Does your company treat these requests as fire drills  or do they have a way to manage them?

The best companies have a deep respect for chaos and put practices in place to manage it and to learn from it. New products and services are sometimes rooted in chaos learnings. Successful chaos management becomes a source of positive change within an organization, as it provides opportunities for people to learn new skills and encounter new situations. As discussed in the earlier article, these new skills and experiences prepare these individuals to be the Explorers that we need.

If your company grows Cubicle Heroes, then the first step in the solution is to address the underlying cultural issues. Adding tools too soon will simply result in chaos at light speed. Addressing this issue is especially problematic in organizations where management has built their careers on their firefighting abilities. Cubicle Heroes tend to prosper in environments which lack visibility into cause and effect. One of my Project Management Office  tool implementations came to a grinding halt when the sponsor, who was a master Cubicle Hero, realized the system would also show that he was also the company’s biggest fire starter .

Your company’s reaction to chaos is a key process necessary to maximizing your long term competiveness and productivity. One way to address chaos is to create processes for categories of chaos. Categories help keep the process manageable without having to address each specific and unique possibility.

One category should also be “other,” as the truly unexpected will happen. One example where this was successful is an organization who assigned a team member to work the “other” category, thereby sparing the rest of the team from being randomized by the unexpected.

I’ll write more on this topic in the weeks to come. For other articles, please visit my blog at http://www.tumbleroad.com/blog.

Project Tasks are Your Lowest Priority

Project tasks are the lowest priority work you have on any given day. Wait, what?

It’s true! Strategically, we know project work is the most important future investment for the company. When you break down what you do every day, you’ll see that you are fitting in project work around the other work you have to do. It’s frustrating. You know you could be doing more. It’s frustrating because someone thought you had the time to get this work done.

If you don’t believe the premise, imagine the following scenario. You are staying late at the office to get some project work completed. Your manager’s manager sees that you are in the office, comes over, and asks you to do a task for tomorrow morning. If your answer is “I’m sorry, but I can’t because I really need to get this project work completed.”, their response will determine the relative priority of project work in your environment. For some, rejecting the task would be a career-limiting move.

Perhaps then, we are asking the wrong question when it comes to resource capacity management. Instead of asking whether this resource has free capacity to do the work, shouldn’t we be asking if the resource has enough consolidated free time to work on project work? If they do not, what can we do to remedy this situation?

In my “Done in 40” webinar, we discussed recent research by time-tracking software companies that identified how the top 10% of productive employees work in an agile fashion. These employees typically work 52 minutes and take a 17 minute break away from the work.  This is coherent with ultradian body rhythms studies from the 90’s and 00’s that showed your focus naturally waxes and wanes on a 1.5-2 hour schedule. These work sprints can make you very productive and help reduce mistakes and rework.

I’ve personally tried the sprint approach and I can say, it works well for me. I use a timer app on my Pebble watch to monitor my sprints. Fifty minutes is roughly the time where the mind starts wandering to “Did Joe ever respond to my email?” or “Is there coffee?”. Three sprints enable the top three daily tasks to get done easily.

The catch is you need to have 69 uninterrupted minutes to complete a personal sprint. This leads us back to the question of does a resource have consolidated availability? Yes, they have 3 hours available that day but if it’s in 15 minute increments, that’s not usable.

When a client with project throughput issues engages my services, I find it’s usually not a project management issue. Many times, the lack of consolidated availability is preventing the project work from happening. If you are interrupted every 10 minutes, as are most office workers in the United States, it’s very hard to get work done. If you are having issues getting projects through the pipe, perhaps it’s time to look beyond your projects and to your operational work processes.

We spend the majority of our energy providing oversight and processes to projects, which are a minority of the work instead of doing the same for operational work. McKinsey released a white paper recently that showed most of the operational spend goes to keeping the company running. New projects are a small portion of the overall effort. Yet, we don’t monitor operational work holistically the way we do projects. Perhaps, its time we start.

Project management processes are very helpful and needed. We’ve worked out how to anticipate and reduce risk and how to deliver the reward. We need to apply these approaches to how we manage all work. It’s the operational work that provides the overall context within which we do our project work. If improperly managed, it also constricts our ability to get our project work done. Operational work management improvements could yield the biggest benefit by enabling the consolidation of availability, yielding more usable time for project work.

If you are interested in finding out more about the specific techniques and how to use Microsoft Project to support this need, sign up here and get the recording link to the full “Done In 40” webinar.