Archive for PASS BA Conference

Power BI Personal Gateway Explained

One of the many excellent sessions I attend this week at the PASS Business Analytics Conference in San Jose was a session titled “Get Latest Insights by connecting your data using Power BI Content Packs and PBI Gateways”.  The title was interesting but the content presented by Dimah Zaidalkilani and Theresa Palmer-Boroski (both Program Managers on the Power BI team at Microsoft) was truly excellent.  I am going to share here what I learnt about the Power BI Personal Gateway, to help you understand if you need it, and if so how to get it installed and running.

There are actually 2 gateways that Microsoft currently has available for Power BI, the other being the Enterprise Gateway (for companies and the like).  I will not be covering the Enterprise Gateway in this post.  You would generally look to use the Enterprise Gateway if you refresh from work from a corporate network and need to connect through the firewall.

What is Power BI Personal Gateway?

personal gatewayAs the name suggests, this gateway is for “personal” use. To use it you simply install it on your own laptop, desktop computer or even a server if you want.  “Personal” does not mean you can’t use it for business if you want to, it is just a term to distinguish from the other gateway.  What the Personal Gateway does is create a “gateway” or connection between your computer (that has the software installed) and the Power BI Service in the cloud.  It is kind of like a dedicated VPN designed just to allow Power BI to talk to the data on your computer.

Why do I need it?

Well you probably don’t “need” it, but you certainly might “want” it.  Once you have installed and configured the Personal Gateway on your computer, you are then able to refresh your Power BI datasets directly in the cloud.  You have the option to either set up an automated schedule (up to 8 times per day) or you can trigger the refresh manually on demand.

When you refresh a Power BI Desktop file on your PC, Power BI Desktop connects to all the data sources it needs to refresh the report. Data sources can include things such as SQL Server, local Excel files, CSV files, or what ever you have set as your data sources for your reports.  The Power BI Personal Gateway simply allows to access those same data sources that are visible on your PC transparently over the Internet.

The Regular Refresh Process

If you are anything like me, your standard refresh process for Power BI workbooks will go like this.

  1. Open the Power BI Workbook on your PC via Power BI Desktop
  2. Click on the refresh buttonimage
  3. Wait for 1 to 10 minutes for the workbook to refresh while doing something else.  30 minutes later you realise the refresh finished 20 minutes ago.
  4. Save and then “Publish” the workbook to via the menu shown below.image
  5. You then get 1 or more prompts about where to load the workbook and also “do you want to replace the existing version” (seriously – what else would I want to do?).image
  6. Repeat process steps 1 through 5 over and over again for each Workbook you have developed.

The Power BI Personal Gateway Process

Option 1:

  1. Configure a daily or weekly refresh once and then do nothing but sit back and relax.

Option 2:

  1. Log on to the Power BI Service
  2. Click on the ellipsis next to each data source and click “refresh” for each workbook you need to refresh.  There is no waiting and the refreshes can be triggered in parallel (whether you should or not will depend on your computer and Internet connection.

Things You Should Know

There are a couple of things you should be aware of.

  1. Gateways are part of the paid subscription to  If you currently don’t have a paid subscription then you can’t use it.  Everyone has to decided for themselves if it is worth what it costs.  Keep in mind there are other benefits of the paid subscription, not least of which are “content packs”.  I will come back and talk about that another time.  Also keep in mind that Microsoft is currently not invoicing personal users for the professional version unless they are part of some broader corporate licencing agreement.  In my experience you will be given a 60 day free trial, and after 60 days you will be given a further 60 days free.  I guess this will end one day, but I haven’t seen the end yet. If it still isn’t right for you, consider purchasing Power Update as a local refresh tool.
  2. Your computer needs to be turned on and connected to the Internet at the time of the refresh if you want it to work.  Computer servers are “by design” always on and hence it is not a problem.  But your laptop or personal computer may not be on all the time and hence you need to keep this in mind.

How to Install the Power BI Personal Gateway

Installation couldn’t be easier.  Go to and find the Personal Gateway download.  There is a link to the downloads page on the  home page before you log in.


Launch the software then follow the steps in the install wizard and close then you are done.

Set your Data Source Credentials

The only thing that wasn’t obvious to me when I started using the Personal Gateway was that you need to manually set the data source credentials for each dataset you want to refresh through the gateway.  The first time I realised this needed to be done was when I got the following error message.


Clicking through the error took me to the settings section.  You can get to this session at any time from the regular “cog” settings menu.


For each data source in your datasets, you will have to “edit credentials” and then tell Power BI what the connection authentication method will be.


Here is an example of the dialog from one of my workbooks that has a SQL connection.


The dialog for regular PC files is very similar


Once it is done, you should see a confirmation message something like this for each connection.


Time to Refresh the Datasets

In, click on the ellipsis next to one of your datasets, and then you can either set up your Scheduled Refresh settings (2 below) or manually refresh the report on demand (3 below).


If you select Schedule Refresh above, you will be taken to the settings panel (also clickable from the cog in the top right of the page).  From there you can set up the refresh schedule for all of your loaded datasets.  You may choose to stagger the refresh times across the data sets so that they don’t all hit your computer at once.  You can have up to 8 refresh events each day per dataset.


I hope you have found this article interesting.  I would to hear from different readers as to why they have decided to use or “not use” the personal gateway, so please share your story in the comments section below.

I you are looking for more detailed information, you can read about it at the official Power BI Site.

PASS Business Analytics Conference 2016 – San Jose CA.

I am writing my blog post this week from San Jose, California USA live from the 2016 PASS Business Analytics Conference.  For the benefit of those of you that are not familiar, PASS is the Professional Association for SQL Server.  Each year PASS holds a Business Analytics Conference – it is the premier conference for Data Analyst Professionals (as opposed to IT Professionals who are catered for at other events).  So if you are an Excel or Power BI professional, then this is the PASS conference that was created just for you.

Jer Thorp Keynote Speech

I really enjoyed the Keynote from Jer Thorp today.  Jer is a data artist – he specialises in turning boring data into meaningful visualisations.


There were a lot of gold nuggets that I took away from this session that really resonated with me and that I want to share.  Here is a list from my notes with some comments from me:

  • A great definition of Data is the “measurement of something“.

I love the idea of “Measurement of Something” as a way to describe data.  I get asked all the time what I do, and this description of data and analytics is really useful to simplify a complex topic.

  • Don’t think about the printed page when thinking of visualisations.

This is a lot harder to execute than it is to say.  Jer showed some fabulous examples of engaging graphics using custom developed visual tools.  For most of us we are practically tied to generally available tools like Power BI and Excel to create our visuals.  Thankfully these tools are developing lots of new visualisations (particularly Power BI) which opens up new opportunities for communicating.  But our job as Analysts is to seek out these tools and use them.

  • Visualisations should try to create an Ooh/Aah response
    • Catch attention => Ooh
    • Tell them something they didn’t know => Aah

This is really helpful to me as I sometimes wonder about the merits of “flashy” demos.  With the lens of “trying to get attention and then tell them something they didn’t know”, it makes the purpose and value of the whole exercise a lot clearer to me.

  • Data Visualisation should strive for Reduction or Revelation.
    • Reduction example: Blood pressure is a reduction of a person’s health into a simple 2 number measurement.
    • Revelation example: X-rays expand the available data to provide new insights that are not otherwise visible.

Reduction or Revelation is a simple concept that is easy to remember and will help any analyst focus on the task at hand.

  • Data analysis can be Confirmatory or Exploratory
    • Confirmatory maps to Satisfaction.
    • Exploratory maps to Joy.

Another simple concept to help focus you on the task at hand.

  • Good visualisations help you to ask new questions – i.e. it is question farming. You create new questions you didn’t think to ask before.

Like I said above, I have often questioned the value of “flashy” visualisations with moving bubbles etc.  But if such visualisations help people ask new questions, then they have achieved something great – I never thought about it that way before.

  • Upstream data. The closer you get to the source of your data, the better you will understand it.

I think I have always intuitively known this one.  I spent my career working in the retail industry and everyone there knows that the answers can’t be found in the office, but in “the trade” (in the retail outlets).  You need to get out and look if you want to understand your data.

Why not start planning for next year’s PASS BACON now.

For some reason business analysts don’t seem to hold attending conferences at the same level of importance as IT professionals.  I think this is a shame as there is a lot to learn and a lot of networking opportunities to be had.  If you are a data analytics professional, I hope you will consider attending a professional development conference at some time in the future.  There is no time like now to start planning for this – make sure you get some money put aside in next year’s budget.

Moving From Excel to Power BI–PASS Presentation

I was recently a presenter at the PASS BA Webinar Marathon – Dec ‘15.  This was an online Webinar with a total of 7 separate presentations from various experts across the industry.  My topic was “Moving from Excel to Power BI – A Business Analyst’s Perspective”.   The session was recorded and is available to view for anyone that is interested.

moving from Excel to Power BI

My objective was not to cover a demo of Power BI – there are plenty of demos in other places across the Web.   Instead I focused on explaining when a Business Analyst may find it appropriate to use Power BI, and what the differences are between Excel and Power BI, along with some demos of the differences.

Here is the session Abstract

Read More

Using SharePoint Enterprise with Power Pivot

As regular readers of my blog would know, I attended the PASS Business Analyst Conference in Santa Clara California this year (April 2015).  Here is the link for next year if you are interested.  While I was there, I had the pleasure of presenting on one of my favourite topics – how to use SharePoint to share Power Pivot workbooks.

SharePoint Enterprise is very powerful, but there are some things that I think you need to do to make it a positive user experience.  My presentation covers what I have learnt over the last few years on this topic.  My objective is always to get away from the generic look and feel (shown on the left below) and instead deliver a optimised web browsing experience (shown on the right below).


I have now made a video of my presentation available online to anyone that would like to watch it. Read More

Solved: Power Query Issues with Power Pivot – Exception from HRESULT: 0x80020009

level: Intermediate

I have written a couple of posts here and here about the problems and dangers of using Power Query with Power Pivot.  Well I am very pleased to say that I now fully understand the problem, how it is caused and how it can be avoided.

Edit: Nov ’15.  Microsoft has released a fix to prevent this problem occurring.

First a Clear Statement of the Problem

You use Power Query to create a table and load it into Power Pivot. You only load a subset of columns to start with.


All is fine for a while and you create a whole lot of DAX formulae – life is sweet.  You add new rows to your Power Query Table and refresh the data into Power Pivot – not a care in the world.


Then one day you need to make a change in Power Query (eg to bring in another column of data that you left out originally).  You go into Power Query and make a change to your table to bring in a new column.

powerquery change

You save, close and load and then BAM – you get the following dreaded error message.  You didn’t even see it coming before it hit you right between the eyes.


Exception occurred. (Exception from HRESULT: 0x80020009 (DISP_E_EXCEPTION))

The only option now is to remove the table from Power Pivot and then reload it.  When you do this you lose all your relationships, all your measures in the table, all your calculated columns you created in the table (not that you should have any of those – right?!)

L2WD banner ad

What causes this?

It is very easy to prevent this happening if you know what causes it.  This is one of the many high value things I learnt at the PASS BA Conference in San Jose in April 2015 (in 2 separate sessions – one by Chris Webb and one by Miguel Llopis) .  So what causes the problem?  The issue is caused when you make structural changes to the table in BOTH Power Query and Power Pivot. Read More

PASS BA Conference Day 2 Wrap

Seriously, this has been the best 3 days of my life. Well except for my wedding day of course (sorry honey). There were many great sessions at the PASS BA Conference – about half of them I attended I would classify as “invaluable”. And there were many other sessions I couldn’t attend due to timing clashes, but then again I have purchased the video pack so I will get to see those in the next few weeks. But probably the best thing of all from this conference was the networking and face to face contact with 600 Excel super dudes that think just like me :-). That was seriously awesome. And then finally there was this T-Shirt – I would have come all this way just to get that!


Day 2 Conference Highlights

Excel 2016

My day started with a Microsoft Excel Breakfast with the Excel Development team. Dany Hoter shared some of the great new features that are coming to Excel. The things I liked most (in addition to the T-Shirt) are: Read More

Day 1 Wrap PASS BA Conference


It is late on day 1 of the PASS BA Conference – quite late actually.  I really enjoyed the first day and wanted to share some of the highlights with those that couldn’t make it.

Keynote Speaker: Carlo Ratti.

This man is living the dream!  Carlo is an MIT Professor working in the Senseble City Lab.  Carlo shared lots of examples of the ground breaking research being done to “invent the future”.  From autonomous vehicles that wont need traffic lights and yet can become “orders of magnitude” more efficient to the point where we could serve current demand for point to point transportation with just 20% of the fleet.  He also spoke of how MIT analysed Flicker photo metadata –  like date/time of photo, country of origin of the photographer to understand how tourists move through cities, where they visit etc.  And also how MIT analysed the colour (specifically how “green”) Flicker photos were as a proxy measure for “drought” in Spain – fascinating stuff!.

Just a few highlights for the sessions I attended

I attended many sessions during the day, but the highlights for me were as follows:

  • I attended a session by Avi Singh on Power View. I have not been a fan of Power View to date, however Avi really made me think again about how I could use this product to add value.
  • I went to a great session by Marco Russo on budgeting in Power Pivot.  For those of you that know of Marco (and Alberto), you will know these guys are “off the planet” when it comes to DAX understanding.  This was a great session about how to take last year’s sales (or some other base line) and then using a range of tools like Power Query and DAX you can come up with a budget apportioned at various levels of granularity. Excellent stuff.   I also then had the privilege of chatting to Marco about the Mastering DAX and also Optimising DAX training sessions in Europe in the middle of this year, both of which I hope to fit into my work schedule.  I have no doubt these will be mega learning opportunities for me (or anyone else that wants to be a DAX Ninja).
  • I sat through a great presentation on Power Query (my number 2 most loved piece of software after Power Pivot).  Miguel shared some of the up coming features including the ability to record Power Query using the VBA Macro Recorder (amongst other things).  I can already see how I will add a macro in my personal.xlsb to instantly and automatically add a Power Query calendar to my workbooks.  I also learnt (and now understand) why Power Query is so flaky with Power Pivot, And I also know how to manage this in the short term thanks to this blog post by Chris Webb.
  • I got to chat with Per Solli from Power ON, and realised I need to learn a lot more about the capability of Power Planner – I’m sure that will be the topic of a future post.
  • Oh, and I bumped into Mr Excel (Bill Jelen) and had a chance to chat.

These are just a few of the awesome things I did today as I invested some “sharpen the saw” time in myself and my company

Looking forward to tomorrow!.

Chandoo is indeed Awesome

Well it is the end of day 0 – the pre-conference sessions – prior to the commencement of the actual PASS Business Analytics Conference here in Santa Clara tomorrow.  It was hard to pick which of the 4 pre-con sessions to attend but I ended up choosing Chandoo’s session titled Building Awesome, Interactive & Advanced Charts with Excel.  My logic was that I spend a lot of my time helping customers get value from Power Pivot, and ultimately the end users will want to visualise the data.   Chandoo’s session promised to focus on broadening the skills and knowledge of the participants so they could get even more out of Excel charts – and he certainly delivered.


After we started, two things struck me immediately – firstly Chandoo is much younger than I had imagined and secondly he said that he would like to learn from the participants too.  This really highlighted to me that EVERYONE can learn from others (even experts like Chandoo), and even the people in the room with between 10 – 30 years experience each.

The full day session was great.  We went through a number of techniques step by step, building from a base concept into a fully functioning interactive chart.  There were lots of pieces that were not at all new, but the way these pieces came together to deliver various interactive charting experiences indeed was awesome.  And then as always at such sessions, there are the little nuggets of gold, be it a single idea, a button that you didn’t know existed, or a new use case for an existing tool.

Like this little button on the “select data source” dialogue for a chart that I never knew existed.


And to cap of a great day, I was sitting next to a fellow participant (David Hoffmeister) who told me how to fix a formatting issue I have with a Power Pivot workbook using conditional formatting. It was great to invest a day of my time just to focus on learning, and I recommend it to anyone as a way to become awesome – like Chandoo.

Do you know the way to… the PASS BA Conference?


I am sitting here in San Jose (Santa Clara actually) ready to attend the PASS Business Analyst Conference.  It was hard to get those lyrics out of my head when I was flying to San Jose, but I am sure I will have a clear head once the action begins (I guess that shows my age – although I was VERY young at the time).  Anyway, I digress.  The PASS BA Conference is the premier annual conference for Business Analysts – those people that actually USE BI tools to add business value in their day to day job.

This year’s conference has a strong Excel focus with lots of Excel experts speaking on their favourite topics.  Tomorrow I am attending Chandoo’s pre-conference session on interactive charts.  I have been a fan of Chandoo’s blog for many years and I can’t wait to see him present in person.  Then I have 2 days packed full of workshop sessions including my own session titled Becoming Team Awesome: How to Create Value and Present Data to Business Executives in SharePoint.

I will be posting updates on my experiences at the conference over the next few days.

It’s time to start planning for next year’s event.

It is never too early to plan to come to one of these events, and it may take some effort to convince your manager to let you come along (as I talked about in this post).  Hopefully you will get a sense of the value from the blog posts I share, and maybe you can make it next year (if you didn’t manage to get here this year).

I’m Speaking at the PASS BA Conference

PASS_BAC15_Speaking_250x250I’m really excited to be speaking at this year’s PASS Business Analyst Conference in San Jose California.  I will be sharing what I have learnt in my time working as BI Director at The Coca-Cola Company about how to share Power Pivot (and other Excel) Workbooks with people that simply want to consume the data.

Part of the problem of many workbook sharing tools is (in my view) the default settings are far too focused on giving a “Full Excel” experience instead of providing a “Web Browsing” experience.  People that want to consume the labours of our Power Pivot Workbooks don’t care about Power Pivot or Excel, they care about the numbers and the insights.

The techniques I have learnt and will share at the conference will show how any Excel user with access to SharePoint Enterprise (or a cloud hosted equivalent) can deliver a positive web browsing experience while still having the full Power Pivot interactive capabilities.  When you configure your shared workbooks for the consumer of your data rather than an Excel User, they can simply focus on the numbers and insights and get on with business.

Why you should attend this year’s conference

Read More