MailChimp and Power BI

This week I was playing around with MailChimp and Power BI.  If you’re not familiar with MailChimp, it’s the service that we use to send out our Monthly-ish newsletter.  I thought I’d show how incredibly easy it is to get a dashboard from this service.

Software as a Service

MailChimp is what we refer to as a “Software as a Service” (SAS) setup.  Basically what this means is that it is a subscription model where I have no need to set up my own server or host anything myself.  I simply subscribed, set up the forms on my website for you to fill out, and then use their services to build and send out the newsletter.  It’s pretty slick and they don’t even charge me until I hit 2,000 subscribers or want to set up auto replies and such.  I’m a huge fan of this model, as it lets you do a “try before you buy” test, only upgrading when you want to.

What do you need?

There are only two requirements here:

  1. A MailChimp account of your own
  2. A account which you can create for free (seriously, no Pro Subscription required)

Getting Started with MailChimp and Power BI

Step 1: Connect to MailChimp

So the process begins with signing in to your Power account.  Once there, you look for this button in the bottom left corner of your screen:


Click that, and you’ll be taken to another screen that looks like this:


Click Get on the Services tab.

Now, to be fair this is still pretty new, so there’s a lot of sources that you would “hope” would be here (like Survey Monkey) which currently aren’t.  Despite this, the easiest way to find what you’re looking for is to start typing the name of the SAS source in the search pane in the top right.  It auto-filters the app list live.  Shown below is the results of “Ma” today:


So click the Get button at the bottom of the MailChimp.

You’ll now be asked to sign in.  If you have multiple domains under your MailChimp profile, just select one for now.  What you’ll see is that it takes you back to the portal and lets you know that it’s loading your data.

Step 2: Wait

Seriously?  Yes.  When you’ve chosen the account to log in, you’ll see that you get a new entry in the Dataset, Report and Dashboard sections of the Power BI portal:


See how the source is greyed out and has a star?  The star means it’s a new item, but the greyed out status means it’s loading.  It takes a while, and you won’t be able to do anything with it until it’s finished the initial load.  So go get a coffee and wait for the chimp to finish it’s trek to your portal.

Step 3: Explore the Dashboard

Wait, what?  I don’t need to build the dashboard myself?

No!  It’s already built for you.  MailChimp and Power BI together in a few easy clicks.  Check it out!


All fully clickable, and Q&A works like a charm:


What about Scheduled Refresh?

Right, I hear you.  That data set took a long time to load, so you don’t really want to have to come in and refresh it every day.  (I mean, unless you need an excuse to go for coffee…)

Well check this out.  This is shot from an account which only has a Power BI Free subscription.  I access this by clicking the … next to the data set then choosing Schedule Refresh (normally a Power BI Pro feature only):


Oh… and did I mention that this was created on Oct 27?  This report has already refreshed, and is scheduled to do it again with no configuration or pro license needed.  Ah the beauty of connecting to a pre-built SAS dashboard!

What about my other MailChimp Accounts?

If you have more than one domain in your MailChimp account, you might be surprised to see that when you try to add another MailChimp dashboard it just creates the same one again without prompting you to log in.  This is because it uses the cached credentials.  So how do you make this work?

Basically, you create the same report again then, once it’s finished loading, you go and edit the data source credentials on the Dataset for the second report.

To do this:

  • Go to Datasets –> Ellipses –> Schedule Refresh
  • Expand Data source credentials
  • Click Edit credentials
  • When prompted for oAuth2 credentials, click Sign In
  • Insert the username & password
  • Then choose the new data set when prompted


I’d also recommend going to each of the datasets, reports and dashboards and renaming them using the ellipses as well, just to keep it clear which is which.

Final Thoughts

The one thing I can’t do here is download the pbix file that was used to create this dashboard.  I’m sure it’s heavily parameterized (how else would they deploy for whomever adds it), but I’d sure love to do that.  Why?  So I could connect directly to MailChimp easily from PowerBI Desktop or Excel… maybe so that I could merge other data in with it.  (Once it’s published, I cannot get in and examine or change the M code in any way.)

Having said that, this is still pretty darned cool stuff!  I really hope other vendors jump on this train as well.  Building dashboards can be hard, and this just makes it super easy.  I’d love to see one for my Facebook account, LinkedIn and other SAS sources.

For reference, Google DOES provide a dashboard too, and it’s just as easy to set up and auto refreshes like this too.

Power BI Slicers

For those coming from the Excel 2013 world, you’ll surely want to create filters using the Power BI Slicers.  After all, you know that Slicers and Timelines are two of the sexiest filters we have for controlling filter context in Power Pivot models.  In this post we’re going to explore the slicer visual, and how to get closer to what you’re used to in Excel.

The Goal

I’m going to fall back to my last project here, and have created a single visual on a blank report page.  You probably remember this one, it’s the map of where I’ve stayed so far this year:


What I’d like to do now is add my slicers and timelines.  I’d like a slicers for Country and Province.  Should be easy, no?  Err… no.  Sad smile

The default Power BI Slicers

Now, Power BI Desktop has a Slicer visualization, as you can see here:


So what’s wrong with it?  I’ll build two to show you why I’m less than satisfied…  Here’s what I did:

  • Created a Slicer visual
  • Added the Country field
  • Created another Slicer visual
  • Added the Province field

Do these look like Excel slicers to you?  They look a lot more like check boxes to me, not slicers…


Now don’t get me wrong, Power BI Slicers certainly work, as you can see here where I’ve drilled in to USA:


So let’s look at the difference between the Power BI Slicers and the Excel slicers that my expectations are based upon:

  • The Province field hides all irrelevant items by default, unlike Excel.  I could not find a configuration option to change this.
  • The checkbox thing drives me crazy.  I hate checkboxes in my Windows files list, I don’t like them here, and again it is inconsistent with Excel.  I could not find a way to turn those off.
  • Originally I wanted to show the provinces in a two column slicer, like I can in Excel.  I could not figure out how to make this happen either.
  • Finally, I wanted to show the bubbles like an Excel slicer.  The closest I could get was the image below (WARNING! SUPER UGLY GRAPHIC AHEAD!)  Should you feel the need to create this abomination you need to select the slicer, go to the Formatting roller –> Items –> Outline –> Frame.


Gross.  That is just gross.  Honestly, I really don’t understand why the slicer is so different from Excel’s.  That slicer is pretty, and people are used to it.

Not happy with these, I deleted both slicers.

Is all hope lost for attractive Power BI Slicers?

Thankfully, the answer is no.  The Power BI team has given developers the ability to create and distribute their own visuals into the Power BI custom visuals gallery.  So let’s go and pull in a couple of those to fill this gap.

Locating the Custom Visuals Gallery

To be fair, the steps for this could be MUCH easier.  To get here the first time you can either just click this link or follow these steps:

  • Click the ellipses in the Visualizations gallery to import a custom visualization


  • Choose to Import a Custom Visual
  • Click the Learn More link


  • To be fair, you should probably read the page you’re taken to, as it talks about all the risks of using a custom visual.  (Remember not all custom visuals are provided by Microsoft, many are provided by 3rd parties.)
  • I scrolled straight to the bottom and clicked the link in the See Also section to go to the Power BI custom visuals gallery

You’ll be taken to the gallery, which has a lot of pretty visuals that can be imported into your project.

To make it easier to find custom visuals, I’d recommend you do a couple of things here:

  1. Bookmark this page (making it a bit easier to get back to it.)
  2. Choose to sort the gallery by Name rather than by Most Recent (which is the default)

When you click on a visual it will offer to download a pbiviz file that you can store in a folder.  You’ll want to remember the location, as you’ll need to import the visuals into every new PBI file you create.

I downloaded a specific visual here: the Chiclet Slicer which, ironically, is published by Microsoft.

Importing the Chiclet Slicer

When I returned to Power BI desktop, it’s still sitting at the Import Custom Visual dialog, which is convenient.  So I was able to just click the big yellow Import button, and select the ChicletSlicer file.  Doing so adds a new option to the Visualizations gallery:


I created two new Chiclet slicers, one for Country and one for Province, and was pleased to end up with the following:


Now that’s more like it!  Certainly needs some tweaking, but better than the past iteration.  So let’s get to that tweaking…  I changed the formatting options for each of the slicers as follows:

  • The Country slicer
    • General –> Columns –> 1
    • Header –> Off
    • Chiclets
      • Unselected Color = very light grey
    • Title –> On
      • Text = Country, Font Color = Black, Alignment = Center, Text Size = 10
  • The Province slicer
    • General –> Columns –> 2
    • Header –> Off
    • Chiclets
      • Unselected Color = very light grey
    • Title –> On
      • Text = Country, Font Color = Black, Alignment = Center, Text Size = 10

And, as you can see, the results are pretty good:


A couple of things that I couldn’t figure out here though:

  • I wanted to align the text in my “chiclets” to the left, like in Excel.  Can’t seem to find an option for that.
  • There is a tantalizing option in the “General” section to show disabled items “Inplace”, and an option in the chiclets to set the colour for those items.  I would have expected it to be equivalent to Excel’s “Show Disabled”, but it doesn’t seem to do that.  I have not figured out how to replicate that effect.

Final Thoughts

To be fair, there are a ton of configuration options for the Chiclet slicer, much more than I’m going to cover.  Why this slicer isn’t part of Power BI’s default install is beyond me… especially since it’s published by Microsoft.

Values Become Text After UnPivoting Other Columns

Have you ever set up a nice query to UnPivot other columns, only to find that the query data types change when you add new columns?  This post will cover why values become text after unpivoting other columns.


We’ve got a nice little table called “Data” showing here.  Nothing special, it just summarizes sales by region by month, and our goal is to unpivot this so that we can use it in future Pivot Tables.  (You can download the source file here.)


Now, you will notice that April’s sales are outside the table. This is by design, and we’ll pull it in to the table later when we want to break things.  Smile

UnPivoting Other Columns – The Hopeful Start

If you’ve been following my blog for any period of time, you’ve seen this, but let’s quickly go over how to unpivot this:

  • Select a cell in the table
  • Go to Power Query (or Data in Excel 2016) –> From Table

We’re now looking at the Power Query preview of the table:


Great, now to unpivot…

  • Hold down the Shift key and select the Country and Prov/State column
  • Right click the header of either of the selected columns and choose Unpivot Other Columns
  • Right click the headers of the two new columns and rename them as follows:
    • Attribute –> Month
    • Value –> Sales

Re-Pivoting from the Data Model

With the table complete, I’m going to load this to the data model and create a Pivot Table:

  • Go to Home –> Close & Load –> Close & Load To…
  • Choose to Load to the Data Model

The steps to create the Pivot depend on your version of Excel:

  • Excel 2013: Go in to Power Pivot –> Home –> PivotTable and choose a location to create it
  • Excel 2016: Click any blank cell and go to Insert –> PivotTable.  As you have no data source selected, it will default to using the data model as your source:


With the PivotTable created, I’ve configured it as follows:

  • Rows:  Country, Prov/State
  • Columns:  Month
  • Values:  Sales

And that gives me a nice Pivot like this:


Let’s Break This…

Okay, so all is good so far, what’s the issue?  Now we’re going to break things.  To do that, we’re going to go back to our original data table and expand the range:


In the picture above, I’ve left clicked and dragged the tiny little widget in the bottom right corner of the table to the right.  The table frame is expanding, and when I let go the Apr column turns blue, indicating that it is now in the boundaries of the table.

With that done, I’m going to right click and refresh my Pivot Table, leaving me with this:


Huh?  Why was the sales measure removed?  And if I drag it back to the table, I get a COUNT, not a SUM of the values?  And even worse, when I try and flip it back to SUM, I’m told that you can’t?  What the heck is going on here?


Importance of Power Query Step Order

To cut to the chase, the issue here is that when we first created the table in the data model, the Sales column was passed as values.  But when we updated the data to include the new column, then Sales column was then passed entirely as text, not values.  Naturally, Power Pivot freaks out when you ask for the SUM of textual columns.

The big question though, is why.  So let’s look back at our query.

Our original data set

If we edit our query, we see that the steps look like this:


To review this quickly, here’s what happened originally

  • Source is the connection that streams in the source data with the following columns:


  • Changed Type set the data type for all the columns.  In this case the Country and Prov/State fields were set to text, and the Jan, Feb & Mar columns were set to whole number.  We can see this by looking at the icons in the header:


Note that if you don’t have these icons, you should download a newer version of Power Query, as this feature is available to you and is SUPER handy


  • We then selected the Country and Prov/State columns and chose to Unpivot Other Columns.  Doing so returned a table with the following headers


Notice that the first three columns are all textual, but Sales is showing a numeric format?  Interestingly, it’s showing a decimal format now, but it shows the numeric format because all unpivoted columns had explicitly defined numeric formats already.

The final steps we did was to rename our columns and load to the data model, but the data types have been defined, so they were sent to the data model with Sales being a numeric type.

Why Values Become Text After UnPivoting Other Columns

Okay, so now that we know what happened, let’s look at what we get when we step through the updated data set.

  • First we pulled in all the columns.  We can plainly see that we have the new Apr column:


  • The Changed Type step is then applied:


Hmm… do you see that last data type?  Something is off here…

So when we originally created this query, Power Query helpfully pulled in the data and applied data types to all the existing columns.  The problem here is two-fold:  First, the Apr column didn’t exist at the time.  The second problem is that Power Query’s M language uses hard coded names when it sets the data types.  The end effect is that upon refresh, only the original columns have data types defined, leaving the new columns with a data type of “any” (or undefined if you prefer).

  • We then unpivoted the data, but now we see a difference in the output


Check out that Value column.  Previously this was a decimal number, now it’s an “any” data type.  Why?  Because there were multiple data types across the columns to be unpvioted, so Power Query doesn’t know which was the correct one.  If one was legitimately text and Power Query forced a numeric format on it you’d get errors, so they err on the side of caution here.  The problem is that this has a serious effect on the end load to Power Pivot…

  • Finally, we renamed the last two columns… which works nicely, but it doesn’t change the data type:


Okay, so who cares, right?  There is still a number in the “any” format, so what gives?

What you get here depends on where you load your data.  If you load it to the Excel worksheet, these will all be interpreted as values.  But Power Pivot is a totally different case.  Power Pivot defaults any column defined as “any” to a Text data type, resulting in the problems we’ve already seen.

Fixing the Issue

For as long as we’ve been teaching our Power Query Workshop, we’ve advocated defining data types as the last step you should do in your query, and this is exactly the reason why.  In fact, you don’t even need to define your data types in the mid point of this one, that’s just Power Query trying to be helpful.  To fix this query, here’s what I would recommend doing:

  • Delete the existing Changed Type step
  • Select the final step in the query (Renamed Columns)
  • Set the data type for each column to Text except the Sales column, which should be Decimal Number (or currency if you prefer)


When this is re-loaded to the Data Model, you’ll again be able to get the values showing on the Pivot Table as Sum of Sales.

Avoiding the Issue

Now, if you don’t want Power Query automatically choosing data types for you, there is a setting to toggle this.  The only problem is that it is controlled at a Workbook level, not at a global Excel level.  So if you don’t mind setting it for every new workbook, you can do so under the Power Query settings:


Is Changed Type Designed in the Correct Way?

It’s a tough call to figure out the best way to handle this.  Should the data types be automatically hard coded each time you add a new column?  If the UnPivot command had injected a Changed Type step automatically, we wouldn’t have seen this issue happen.  On the other hand, if a textual value did creep in there, we’d get an error, which would show up as a blank value when loaded to Power Pivot.  Maybe that’s fine in this case, but I can certainly see where that might not be desirable.

Personally, I’d prefer to get a prompt when leaving a query if my final step wasn’t defining data types.  Something along the lines of “We noticed your final step doesn’t declare data types.  Would you like me to do this for you now (recommended)” or something similar.  I do see this as an alternate to the up-front data type declaration, but to be honest, I think it would be a more logical place.

October News and Events

It’s a busy month here at Excelguru. Instead of a technical post we wanted to catch everyone up on our October news and events!

Live Course: Master Your Excel Data October News and Events

Ken is teaching a LIVE, hands on course in Victoria, BC on Friday, October 21 from 9:00am-4:30pm. This session is great for anyone who has to import and clean up data in Excel and will change the way you work with data forever! Ken will teach you how to use Excel Tables, Pivot Tables and Power Query. Space is limited to only 20 attendees, so don't miss out on your chance to sign up. For full details and to register for the session, visit:

October News and Events: Power BI Meet-up

The next Vancouver Power BI User Group meet-up is happening on Thursday, October 13 from 5:30-7:00pm. Scott Stauffer, Microsoft Data Platform MVP, will be presenting on How to Operationalize Power BI. Together we’ll look at some solutions that might help pass your Power BI solution over to IT to manage enterprise-wide. Dinner and soft drinks will be provided. View the full details and sign up to attend at:

Microsoft MVP Award Received

For the 11th straight year, Ken has received the 2016 Most Valuable Professional Award from Microsoft! The previous 10 years, Ken’s award has been in the Excel category, but this year’s award is in the Data Platform category. The new category reflects the work he’s been doing this past year with Power Query and Power BI. Congratulations Ken, your guru status remains assured.mvp_horizontal_fullcolor

Our Team Has Grown

As we mentioned the other day, Rebekah Sax has recently joined the Excelguru team. She brings with her a wealth of experience in marketing, communications, event planning and administration. Please join us in welcoming Rebekah as she helps us make new connections and continue to grow.

New team member

I’m pretty stoked to announce a big milestone for Excelguru.  That’s right, we’ve added a new team member to our company!

We’re pleased to announce that Rebekah Sax has joined our team and will be helping us with our marketing efforts.  She spent the last 15 years working at Fairwinds in a variety of roles from marketing to event planning (and more), and her broad skill set is just what we needed in order to fill some pretty big gaps in our practices. In fact, you can already see the effect.  If you remember the Excel Courses Calendar I set up on my website ages ago… it’s actually got courses listed now!

Sharing Power BI Dashboards

This is the fifth and final installment of this post series, and will demonstrate the process for publishing and sharing Power BI Dashboards (as compared to Reports.)  Key things we’ll explore here are the additional benefits of Power BI Dashboards and how to share them without embedding them in a publicly accessible environment.

Series Table of Contents

This is a portion of a series of blog posts, which build on each other to create an overall solution from Data to a Power BI dashboard.  You’ll want to follow along in order, so here’s the table of contents for the series:

Public vs Private?

So as cool as the last post was, the begin question that comes up is how to share your report with specific people, not the entire world.  The data could be confidential at some level, so this is a pretty big requirement to be able to solve.

The biggest piece of advice I would give is this area is DO NOT CREATE THE EMBED CODE as I showed in my last post.  If you do, it’s accessible if anyone discovers the URL.  We need to share in a totally different way.

Power BI Reports vs Power BI Dashboards

The secret here is that you can’t share Reports with internal users, you share Dashboards instead.  But actually that’s okay, as it adds a couple of nice things to the report when you turn it into a proper dashboard.  One is Q&A, and the other is that it stops showing the selection handles around the visuals every time you select them.  Smile

So how do you do it?

Creating a Power BI Dashboard

The answer is that you go back to your report and – up by that Refresh button that you’re never going to use – you click the “Pin Live Page” button.  That takes you to this interface:


Notice that I have said I want to create a brand new Dashboard, and I’ve even given it a totally different name than the original report.  Once I click the Pin Live button, it creates a new dashboard that shows up in the Dashboard area.  And when you go there, you see this:


Notice that I’ve drilled in to Canada, and it still cross filters, but this time it doesn’t surround the chart with the little frame.  Hmm… wait… that’s actually a problem, isn’t it?  Back in the post where I showed how to create the column chart, I set it up to drill in, and now you can’t.  The good news is that you can click in the upper left where is says “Hotel Stays Page 1” and it will take you directly to the report where you CAN drill in.  (So these two things work together.)

So that is a note to keep back of mind.  Dashboards are filterable at the top level, but if you want to drill in you need to go back to the underlying Report.


I’m not generally a big fan of noise and flashing lights in anything I encounter, but if there were one feature that should have them, this is it.  Just above the visuals in the dashboard is a little area that says “Ask a question about your data”.  It’s subtle, and you could easily use Power BI for ages without ever playing with this:


So what’s it good for?  Natural language queries is what.  Check out what happens when I just type in “shows stays last month”:


Woah… pretty cool.  I never set up any calendar to do that, and it just lists the transaction listing for the previous month.  Not impressed?  How about this one:


I want to be really clear here… my data set does not have a field for Quarter.  In fact, it doesn’t actually even have a field for Month.

You’ll also notice that I never once in this series of posts blogged about how to set up the data set to use this feature.  That’s because I didn’t need to.  It just works out of the box once you pin your report to a dashboard.

This is one of the coolest things in this product.  Imagine pushing a data set like this to your boss and they can just query the data set to find out the questions they probably would have asked you.  Nice.  Smile  (Now, to be fair, really complicated questions are probably still going to need intervention, but still… pretty amazing.)

One more…


Oh, and I should also mention… once you find a really cool visual like this?  There is a pin icon in the upper right that will let you just pin it to an existing or new dashboard.

As awesome as this is though… it’s actually not the topic of this blog post, it’s just a side benefit of what we need to do in order to set up our sharing.  So with a bit of a tear in my eye, I’m going to click the Exit Q&A button and get back to it.  Winking smile

Sharing Power BI Dashboards Within the Company

When I refer to those “within the company”, I’m talking about users who are part of the same Office365 tenant.  The best indication of this is that they have the same email domain as you do.  But what I’m about to describe is equally true if the email domain is different, but the domain is registered under the same Office365 tenant.

So to share a dashboard with someone in your company, you select the dashboard, then go to the top right corner of your screen and click the Share icon:


That will pop up a window where you can type in the email, and even an optional message.  Since my email is all hosted in Office365, it is able to convert the email to a username for Deanna as shown below:


Notice also that I’m warned about her data access, and I can also disable her ability to share it with others.  This is nice, as it allows us some kind of control over what our users can actually see and do with our data.

Once I click Share, Deanna gets and email notification in her inbox and, when she logs in to Power BI, she now has my dashboard listed in her Dashboards section:


Interestingly enough, I have been able to verify that with the Share option unchecked:

  • She cannot share the dashboard with anyone else
  • Changes made to the original dashboard update in the shared version
  • The dashboard is listed in her Dashboards section for easy access
  • All reports are drillable, and she can drill into the Report if she clicks the “Hotel Stays - Page 1” part near the top left of the dashboard.  In fact, if she wants to drill in to the country chart, she MUST do this.

Now, what about security of the raw data itself?  Well, as you can see in the image above, nothing is listed in the DataSets section for her.  This means a couple of things:

  • She cannot access the data model to see the linkage
  • There is no way to download the complete raw data set

Having said that, Q&A is enabled and – if you ask the right questions – you’ll be able to pull up the underlying data.  The right question is essentially “show <table name” and it will show you all the records for the given table.  Obviously, the bigger the data set and the more tables it has, the harder it is to make sense of it, but you CAN query it all at a raw record level, so you do want to be a bit selective about what data you include in the data set.  Now, to be fair, you can enable row level security to restrict what the user has access to, but that’s a topic for another day.

Sharing Power BI Dashboards With an External Tenant

Now, even though I covered external sharing in my last post, it was targeted at public sharing.  What about when you want to share your dashboard with specific people outside your organization.  We can totally do this, although I find the experience a bit… wanting.

Now we can certainly share a dashboard with someone external to our company.  We do it in the exact same manner as we would with someone inside the company.  Namely we do the following:

  • Select the Dashboard
  • Click the Share icon
  • Enter the email address
  • Select the sharing options as desired


We are helpfully warned that this user is outside our tenant, which is great.

Now, what happens next is the user is sent an email which looks like this:


And when we click the link, we must sign in to get access to the dashboard.  All good so far.  The dashboard cross filters and, like when shared inside the company, you can click the Hotel Stays Page 1 portion to get to the report for further drilling.  So far nothing seems off.

But now check this out… if I look at the top left corner of the screen, I see this:


Okay, so it’s not about what I see, but rather what I don’t.  Notice the Power BI logo?  It’s directly about the title.  Where is the little icon to get to the rest of your Power BI reports?  It’s not there at all.  “No problem”, you’re thinking, “ just click the logo”.  Sure thing…

And you’re taken to a page that has this navigation structure.  Notice anything missing?  Um… like maybe the dashboard that was shared with me?  (To be clear, any reports owned by this user from within their own company would show up here.)


This is a known thing, as documented in this article by Microsoft.  And to save you the trouble of reading this, here’s the pertinent part:

When you share with people outside your organization, they get an email with a link to the shared dashboard. They have to sign in to Power BI to see the dashboard. If they don't have a Power BI account, they can create one after clicking the link.

After they sign in, they see the shared dashboard in its own browser window without the left navigation pane, not in their usual Power BI portal. They have to bookmark the link to access this dashboard in the future.

To be fair, it is an improvement over the original state where you couldn’t share with an external tenant at all.  Having said that, it’s still unfinished and hopefully it wont’ be long until Microsoft gets back to dealing with this and plumbs externally shared dashboards into the Power BI portal properly.

Final Thoughts

Listen, I complain about the things that don’t make sense to me, but overall, let’s circle back on how incredible this is overall.  Over the last five blog posts I’ve built a system which:

  • Has a spreadsheet back end to capture my hotel stays
  • Can be updated from the Excel app on my iPhone (or whatever phone you have)
  • Automatically updates
  • Is published to a public facing dashboard on the web
  • Is shared with Q&A to users both inside and outside my company

That is freaking stellar.  Period.

Sharing Power BI Reports (for External Users)

This is the fourth installment of this post series, and will demonstrate the process for publishing and sharing Power BI Reports.  Key things we’ll look at here are how to publish the dashboard, keep it up to date, and share it with users outside our company.  (We will look at sharing with internal users the next post.)

Series Table of Contents

This is a portion of a series of blog posts, which build on each other to create an overall solution from Data to a Power BI dashboard.  You’ll want to follow along in order, so here’s the table of contents for the series:

Subscription Levels

In order to make this whole solution work, I’m assuming the following:

  • The person publishing the dashboard has a Power BI Pro subscription.  This is needed to enable the feature to schedule refresh.
  • All other internal users have a Power BI Free subscription… because I’m cheap like that. Smile
  • External users may or may not have a Power BI subscription at all.

So basically, to sum this up really quick, if you only have one person authoring and publishing your dashboards in your company, you only need one Power BI Pro license, and the rest of your team can be on the free version.

The good news is that it’s totally free to sign up for Power BI, you just need a “work” email.  (I.e. it won’t let you sign up with your hotmail, gmail, or yahoo address, but if it’s some kind of custom domain, you should be fine.)  In fact, you can most likely even sign up even if your IT department hasn’t yet.  If you want to try it, head over to the Power BI site.

Publishing the Report to Power BI

The first thing we need before we can start sharing our reports is publish them.  Doing that is simple once we have our account set up.  We start by going to Power BI Desktop and opening our Power BI Desktop file.

Now, if you haven’t signed in to Power BI, you’ll want to do that first:

  • Go to File –> Sign In
  • Enter the email and credentials to your Power BI account and commit them

Once you have successfully logged in, that will make things easier, as it won’t prompt you when you’re publishing.  So assuming you’ve done this, you can now:

  • Go to File –> Publish –> Publish to Power BI.

Because my company uses Office 365 Unified Groups, I actually get a long list of locations where I can publish my report, but I’m going to choose the very top one call “My Workspace”.  (You may not have or get any groups, but everyone has a the My Workspace area.)

A few seconds later I’m given a happy little message about how it was successful:


And that’s it for Power BI Desktop.  You can click the link or dismiss the message and log in at  No matter which, you can shut down Power BI Desktop and move to your web browser.

Exploring the Power BI Interface

On the left, you’ll see that you’ve got a few areas that you can navigate into, as shown below (yours may be expanded, I collapsed them for easier reading):


The important pieces for us here are the Reports and Datasets areas of the navigation pane.  The data for our file is separated from our report, so we need to talk to both portions.  But first, I’m just going to take a look at the report in the Power BI web interface.  To do that II :

  • Selected reports on the left (only required if it is collapsed)
  • Selected the Hotel Stays report

And, as you can see, I’ve got a nice report that looks just like it did in Power BI desktop.  It even cross filters the same:


So this is kind of cool.  It’s no longer on my PC, it’s stored in the cloud, as is the data set.  So in theory, I could update my Excel file with a new hotel, and it should refresh since that file is stored in my OneDrive for Business, right?

Sorry, but not yet.  See the Refresh button the red arrow is pointing at?  That will refresh the report from the data set (although I believe you need to refresh the data set first).  So manual, so let’s fix that first.

Keeping the Reports up to date with Scheduled Refresh

Again, in order to set up scheduled refresh of our reports, we must have a Power BI Pro subscription.  If you don’t… well… you can still use the solution, but you’ll need to come in and manually click the button to refresh it (and seriously, who wants to do that?)  I’m going to assume you do have a Power BI pro subscription, and we’ll carry on here.

So to set this up, I clicked on the Datasets button in the navigation window on the left, then selected the Hotel Stays data set.  That took me to a blank page that has a very Power BI Desktop like experience, including a Refresh button in the top right:


This area can be used to manually force the data set to update, and can even be used to build additional reports.  But neither is what we want.  What we need is a way to schedule the refresh, which is found by clicking the … icon beside the data set name:


Clicking Shedule Refresh takes me to a page and – after about a 1 second delay – gives me some other important info:


Okay, so I need to confirm my password.  I’m not going to cover this in much detail, as Chris Webb recently covered this part in detail.  I will point out that OneDrive for Business is, in fact, SharePoint (just in case you’re wondering why we see a reference to SharePoint there.)  So all we need to do is click the Edit Credentials link and re-login to the Office 365 account.  Just make sure you change it from Anonymous to oAuth2 before trying to sign in.


Once done, you can expand the Schedule Refresh area, flip the flag to Yes, and you should be good to go:


A few of key points to be aware of here:

  1. Sometimes Power BI seems to forget that it has been authorized.  You may need to come back in and re-login.  That’s only happened to me once so far, but I’ve seen people mention that it happens to them more frequently.
  2. The above is why that last checkbox is SUPER IMPORTANT.  Microsoft will email you whenever a scheduled refresh fails, so that you are aware and can fix it.  This is huge, and I’d recommend you leave that box checked.
  3. You can add more frequent refreshes although – in my experience – you’re limited to about 10 times per day.  I really don’t need more than daily here, but for other dashboards I’ve set them to refresh every 2 hours during business hours.

At any rate, with this set, we click Apply and forget about manual until Microsoft emails us to let us know something stopped working.

Sharing Power BI Reports Externally

Now, the data is being kept up to date, and now we want to share the report with others externally.  How?

  • Click Reports and drill into the report you want (Hotel Stays for me)
  • On the report, go to File –> Publish to Web


  • Click Create Embed Code then Publish

This will give you a nice dialog that provides two things:


Here’s a direct link to the email-able URL, which you can see renders nicely in the online app.

And here is what happens when you embed the iframe in your site.  (Yes, it’s fully clickable!)

So that’s pretty cool and pretty easy.  The only caveat (which works fine for me for my purposes) is that this is 100% public.  While the end audience can’t see any of the underlying data, anyone on the internet who gets this url can see and play with the dashboard.

Final Thoughts

So this is a pretty cool thing so far.  We’ve got a solution where the data set can be access from OneDrive for Business via the Excel app on my phone.  I can add new records there and they are saved, then automatically refreshed in the Power BI report that lives in my Office 365 tenant.  And better yet, I have been able to publish a fully interactive report to the web that will update over time and that you can click and drill into.  Neat stuff for a pretty low cost.

But I know what you’re thinking… you’re thinking that this is all great, but what if we want to share it with users INSIDE the company, and we don’t want to just publish it as a public facing page.  Well hang tight, because that is the focus of the next post in the series.

Creating Power BI Card Visuals

In this post I’m going to continue the process of creating a Power BI dashboard built off the data I sourced from OneDrive for Business.  This time I’m going to look specifically at creating Power BI Card Visuals.

Series Table of Contents

This is a portion of a series of blog posts, which build on each other to create an overall solution from Data to a Power BI dashboard.  You’ll want to follow along in order, so here’s the table of contents for the series:

Today’s Goals – Adding Power BI Card Visuals and another Chart

Let’s just circle back on the wireframe I presented in my last post:


I’ve already made the Map and the Stays by Country chart.  Now I need to add my Key Stats and the Stays by Hotel Brand chart.

Showing Key Stats with Power BI Card Visuals

Ultimately, what I want to show on my dashboard is some key indicators that filter as I select other visuals on the dashboard.  Something that looks like this, which is reacting to my filtering to only stays in Canada:


Creating DAX Measures for my Power BI Card Visuals

So the first thing I want to do is think about the data that I want to display.  I’m tracking nights away from home, which I call (Hotel) “Stays”.  There’s 3 main measures that I’m after:

  • Stays (Selected): This is base measure which was created in the last post in the series.
  • Stays (All): This value should always show the total nights I’ve been away from home.
  • Stays (YTD): This value should show the total nights I’ve been away from home for the current year only.

As a reminder, the Stays (Selected) measure was defined as:

Stays (Selected) = COUNTROWS(Stays)

Creating the next two measures is just as easy as the previous one.  To do this I selected the Modeling tab –> New Measure, and created the following measure formulas:

Stays (All) = CALCULATE(Stays[Stays (Selected)],ALL(Stays))

Stays (YTD) = CALCULATE(Stays[Stays (All)],Year(Stays[Date])=Year(TODAY()))

Each was set with a whole number format with commas and zero decimal places:


Now, to be fair, you don’t have to set up all your DAX measures up front.  You can (and I frequently do) create DAX measures as you need them.  I had already visualized what I wanted, so knew I’d need these ones.

Multi-Row Power BI Card Visuals or Single Cards?

So now that I have my DAX measures built, I’m want to get these into the cards.  My first instinct was to grab a multi-row Power BI card visual and just check all 3 measures.  The problem is that it gives me this:


Now, I can get rid of the bar on the left (Formatting –> Card –> Show Bar = off), and I can change the size of the values (Formatting –> Data Labels –> Text Size).  What I can’t do, however, is centre the values over the text like I can with individual cards.  (At least not as of the latest build of Power BI Desktop.)  It’s always the little things that get me, but it looks like we’re going to need to go to individual cards for what I want.

Creating my Power BI Card Visuals

Creating 3 visuals instead of one is a little bit painful, but certainly do-able.  Here’s how I did it.

  • I went to the Visualizations gallery –> Card
  • Then went to Fields –> Stays (Selected)


Which gave me this:


Perfect.  So then I just created the other two.  The hardest part, honestly, was lining them up.  Rather than dragging and drop I set the properties manually, as I could then ensure that my card heights and widths and top position were identical.  I then only hard to play around with the X position for each card.  (You can find this menu by selecting the card, then clicking the format icon (under the visualizations gallery).  Once you’ve done this, expand the General section.


One thing that does bug me about this is that as soon as you select the next visual, the General section will collapse on you again.  I really wish it stayed expanded, as it would make it much easier to quickly compare values.

Regardless, I ended up with the visuals I wanted and they react properly when I click Canada in my Country column chart:


Or Australia:


Creating the Stays by Hotel Brand chart

With the key stats done, we can now focus on creating a Power BI Bar Chart to show the Stays by Hotel Brand.  There’s no major secret to this one, in fact it’s even easier than the column chart I created in the last post, only because it doesn’t have any drill down levels.  Here’s what I did to create this one.

  • I started by clicking on the blank canvas (to clear selections so I don’t accidentally turn a card into a chart!)
  • I went to the Visualizations gallery and selected the top left icon for the Bar Chart
  • I checked Hotel Brand – automatically adding it to the Axis
  • I checked Stays (Selected) – automatically adding it to the Values area


That seemed pretty easy so far, but it could use some tweaking.  So I clicked the formatting roller and made a few more adjustments to it:

  • X-Axis = Off
  • Data Labels = On
  • Title
    • Title Text = Nights Away From Home by Hotel Brand
    • Font color = Black
    • Alignment = Centre
    • Text Size = 12 pt

And then I resized the chart to show the full width of the hotel brand names.


There are a couple of options I’d love to see here that don’t exist today, mainly revolving around the Y axis.

  1. While I have the option to show the axis on the right side, I don’t have any ability to align the text to the left.  Personally I can’t stand the fact that the text is not aligned with every word started at the same horizontal position.  It’s just weird.
  2. In order to show all the text, I have to expand the entire chart, making it super wide.  Why?  Because I don’t have the ability to set the axis width independently, nor do I have the ability to control the font size for the axis.

At any rate, it looks pretty good for my purposes right now, and I’m sure the font size and alignment (if not the axis column width) will be added in future.

Most importantly, we can see that by filtering my Nights Away from Home chart, it still cross filters this chart.  The view below shows just how brand loyal I’m not when filtered to Canada:


Final Thoughts

With a couple of additional text boxes added to the page, my dashboard development is complete, returning something that I can easily cross filter and explore:


Note that I started this post a while ago, so the data has a few more nights in this version of the screenshot.

Next Post

In the next post I’ll look at publishing this to Power BI, creating an interactive view for the public, and schedule the refresh to keep it up to date.

Creating Power BI Visuals

Last week I grabbed some data from OneDrive for Business.  This week I’m going to start the process of creating Power BI visuals to build a dashboard which will be hosted in the cloud.

Series Table of Contents

This is a portion of a series of blog posts, which build on each other to create an overall solution from Data to a Power BI dashboard.  You’ll want to follow along in order, so here’s the table of contents for the series:

Dashboard Wireframe

My friend Jon Peltier exposed me to the concept of creating a wireframe for a dashboard, which is essentially a quick design layout.  (It’s more complicated than that, but for my purpose I know my key stats, so am looking at the layout now.)  So here’s the draft of what and how I want to place my visuals:


Now, I’m not going to build all of this in this one post.  (I’m only going to build the left side in this post.)

A little DAX – Creating a measure for Selected Stays

Before we get too deep into this, we need to quickly talk about about the key metric is that we want to display in the dashboard visuals.  All maps and charts show key values of some kind, in my case I’d like that to be the number of nights that I’ve been away from my home.  As I mainly stay in hotels when I’m away, I’m going to call the measure “Stays (Selected)”.  (The reason for the “Selected” component will become clear in the next post.)

To create this measure, I needed to go to the Modeling tab and choose New Measure.  In Power BI Desktop, you provide both the measure name in the formula bar, as well as the actual measure itself.  So here’s what I went with:

Stays (Selected) = COUNTROWS(Stays)

The measure name comes first, followed by the = character.  (In Power Pivot for Excel, we’d see := instead.) The measure itself uses the same DAX formula as we would in Excel, which is a simple count of all unfiltered rows remaining in the table.  This is good, as it will allow my reports to cross filter based on selections made in other visuals.

The final thing I did after creating the measure was to tweak the formatting a little bit.  I set it to Whole Number, ensuring that it had 0 decimal places and used a comma format (for when my hotel nights creep over 1,000!)


Power BI Visuals – Map

With the DAX measure built, I’m ready to finally start using my data.  And I really want to start with something visual, so I’m going to start with the map.

There are two Power BI visuals for maps in Power BI Desktop; the map and the filled map.  I’m going to use the first one.  So here’s exactly what I did:

  • I clicked the Power BI Visual for Map
  • I selected the Full Address field
  • I selected the Stays (Selected) field
  • I wondered why nothing showed up on my map

Oh yes, this was a serious WTF moment, honestly.  Here’s what I saw (with arrows added to prove out the mapping):


I recreated this map twice and added other visuals before I figured out what was going on here… as it happens, my data was actually too wide for the default map size.  Look what happens when I widen the map:


So Power BI team, if you’re reading this, the default size of the map is just plain wrong.  Give us one that shows the entire world!

At any rate, now that this I know my data is actually there, I can clean up the chart by giving it a better title.  To do that I:

  • Selected the map visual
  • Clicked the format icon under the Visualizations gallery (now looks like a little paint roller)
  • Expanded the Title field
  • Change the Title Text to:  Nights Away From Home by Location
  • Changed the Font color to black
  • Changed the Alignment to Center
  • Set the Text Size to 12

And the result is as follows:


Power BI Visuals – A Basic Column Chart

With the map done, it’s now time to move on to the next Power BI Visual: a column chart.

To create this, I did the following:

  • Clicked the blank canvas outside my map.  (I can’t tell you how many times I’ve pressed CTRL+Z to undo changing a visual from one type to another!)
  • Clicked the Column Chart (second icon in the Visualizations gallery)
  • Selected Country from the fields list
  • Selected Stays (Selected) from the fields list

Not only do I get my cool chart, but clicking on one the countries drills in to all the stays I’ve had, and zooms in the map as well.  No extra connections or configuration needed.  In this image I’ve drilled in to USA, and you can see that I worked my way down the Oregon coast this summer, as well as the stays I had in the Seattle and San Jose areas for conferences:


Using the chart is pretty easy at this stage: just click a bar.  Click a different bar and it cross filters to those selections, click the same bar again and it un-filters.  Hold down your CTRL key as you’re making selections and you can even get multiple countries like this view of Canada and USA:


As cool as that is, I’d still like to customize the chart a little.  To do so, I selected the chart and clicked the formatting icon below the visualization gallery again.  The changes I made were:

  • Y-Axis
    • Off
  • Data Labels
    • On
    • All other options left as defaults
  • Title
    • Title Text: Nights Away From Home by Region (Drillable)
    • Font Color: Black
    • Alignment: Centre
    • Text Size: 12

So when all is complete, it looks like this:


To me that is much cleaner.

(Note that according to my wireframe, the column chart will live under the map.  I’m only leaving it next to the map for now to make better use of space in the blog post.)

Power BI Visuals – A Drillable Column Chart

So the column chart is cool and all, but I wanted to be able to use it to drill down into a finer level of detail, like Province/State.  Unfortunately just typing that in the title won’t do it, we need to make a change.

So far everything I’ve built has just worked nicely.  I’ve been able to single click everything, and it’s just gone right into the correct place.  But when I click the Province field in the Fields pane, I get this disaster:


This plainly is not what I want!

The issue here is that Power BI Desktop makes the assumption that the Province field should move to the Legend.  We need to move it to slide into the Axis, just below country.  As you drag it, you’ll see a solid yellow line snap in just under the Country field:


And when you let it go, your chart looks like it’s reverted to what you had before.. but there is a difference.  Near the top of the chart, we see a few new icons:


And here’s what they do:

  • The first (currently shaded) icon is “drill out” to go back up one level.  (It’s shaded, as I’m at the top level)
  • The second (with the double down arrows) drills in to the maximum level.  This would show nonsense data for me, as it shows every Province/State in all countries, without their parent country.
  • The third icon (single down arrow) is a toggle to turn on/off drill down mode.  It’s a bit awkward, as you’ll see, but is very effective for my purposes.
  • The funky little square near the end just sends your chart into full screen mode.

So let’s take a look at my USA stays.

We’ve already seen above what happens if I click the bar for USA; it filters both the map and the chart.  So let’s drill in.  I click the single down arrow on the right, then click USA.  My data re-plots as shown below, and the single down arrow turns black to show it is in drill down mode:


So this tells me that so far I’ve spent the same amount of nights in California, Oregon and Washington.  (That was actually a surprise, but fair enough.)  What’s odd to me here though, is that it removes all filters from the map.  I personally don’t think that this should be the case if I’ve selected something then click the drill in arrow.

So now I want to drill in to just Oregon.  So I click the OR bar, and nothing happens.  And this is exactly what I meant when I said it’s a bit awkward.  If you had more levels below this (maybe a city field), you’d drill in there.  If you want to filter the chart to just Oregon, you need to click the down arrow again to clear drill down mode, so that you can go back into filter mode.

So after clicking the black arrow, we can then select the Oregon bar:


Or Washington:


To get back to BC, I need to drill up to get back to my Country selection.  I can do that by clicking the up arrow in the top left.  That will take me back to the Country choice.

Now, based on what I’ve said earlier, you’d expect that you would need to do the following to drill into BC:

  • Click the Drill Down arrow
  • Click on Canada
  • Click the Drill Down arrow again to clear it
  • Click BC

And that’s EXACTLY what you’d have to do if you were using a touch screen to consume your dashboard.  For PC users (which you probably are as the author), you also have this option.

From the Country view

  • Leave the Drill Down arrow un-selected
  • Right click the Canada bar –> Drill Down
  • Left click BC

And it works the same as the touch version with 2 less clicks:


For the record, Drill Up works the same way when you are in a drill down level, as you can see here:


Final Thoughts

At this point, I’m going to slide the column chart down under my map, as per my original wire frame.

I should probably also mention here that you’re not stuck to only one level of drill down.  If your data is properly hierarchical and has multiple levels, add them to the axis.  It can make for a pretty powerful experience when a user is able to keep drilling in.

My only complaint with this feature is really around the un-filtering of other visuals in the report when I drill in.  Personally, I think the other visuals should be filtered to that level.  (I.e. if I drill in to USA, I shouldn’t be seeing Australian cities in my map until I click a State name.)

Next Post

The next post - Building Power BI Card Visuals - will focus on building the cards, as well as the final chart.

Job Opening for a Power BI Analyst in Florida

I received an email from a reader who has a job opening for a Power BI analyst in Florida.

A quick summary is that they are looking for someone with an excellent skill level in Power BI, DAX, M and scorecard/dashboard development.  They are willing to provide relocation to Florida or, if you are truly exceptional, they may also be able to let you work remotely or from a company office nearby (if one exists.)

If that sounds like you, and you either live in or are willing to relocate to Florida, you should check out and apply to their job posting here.