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 PowerBI.com 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 BI.com account.  Once there, you look for this button in the bottom left corner of your screen:

image

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

image

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:

image

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:

image

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!

image

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

image

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):

image

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

image

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:

image

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:

image

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…

image

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

image

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.

image

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

image

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

image

  • 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:

image

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

image

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:

image

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.

Background

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.)

SNAGHTML552a2a7

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:

image

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:

Iimage

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:

image

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:

image

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:

image

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?

image

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:

image

To review this quickly, here’s what happened originally

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

image

  • 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:

image

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

image

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:

image

  • The Changed Type step is then applied:

image

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

image

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:

image

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)

image

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:

image

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: http://www.excelguru.ca/content.php?291-Live-Course-Master-Your-Excel-Data.

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: http://www.meetup.com/Vancouver-Power-BI-User-Group/events/234126999/.

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!