Free ‘DIY BI’ e-Books

Today I wanted to just make a quick announcement that we are currently working on a series of free 'DIY BI' e-Books.

Free 'DIY BI' e-Books? Tell me more!

Over the past few years of working with Excel an Power BI, I've obviously picked up a few different methods, tips and tricks for working with the software.  And looking at how successful our free e-Book "Magic Tricks for Data Wizards" has been through the Power Query Training site, I thought it would be nice to so something similar for Excelguru readers.

One of the cool things about the Excelguru audience at this site is the diversity.  A lot of people originally came here for Excel, but we've been exploring Power Query, Power Pivot and Power BI for the past few years as well.  The one thing that ties us all together is that we are building "Do it Yourself Business Intelligence" or "DIY BI".

My original plan was to release one e-Book with 20 different tips, tricks and techniques; 5 each for Excel, Power Query, Power Pivot and Power BI.  After getting started, however, I realized that it was going to take me a bit longer to get that all done than I wanted.  But since I want to get information out to our readers, I've decided to break this down into four separate e-Books which will be collected under the umbrella of "DIY BI Tips, Tricks and Techniques".  Each e-Book will focus on one specific area of the DIY BI story.

What will the free 'DIY BI' e-Books include?

Well… tips, tricks and techniques, of course.  Smile  Okay, seriously, each is fully illustrated and written to give you some great examples and ideas that I hope will help you in your DIY BI journey.

Here is what is covered in DIY BI Tips, Tricks and Techniques for Excel:

  • The easiest formula to return the end of the month
  • Show a message when cells are hidden
  • Quick alignment of objects
  • Easy to read variances
  • Show a message if your Pivot data is stale

Sample image from DIY BI Tips, Tricks & Techniques for Excel

What areas will the free 'DIY BI' e-Books cover (and when will they be released)?

Those e-Books will be released in the following order:

  • DIY BI Tips, Tricks and Techniques for Excel
  • DIY BI Tips, Tricks and Techniques for Power Query
  • DIY BI Tips, Tricks and Techniques for Power Pivot
  • DIY BI Tips, Tricks and Techniques for Power BI

The first is already written, we just need to lay it out and make it look a bit more awesome.  Our target is to get it released by the end of next week.

With regards to the rest, I'll go as fast as I can on them, but as you can imagine, doing things right does take time.  I would expect that each will take 2-3 weeks to build out properly, but if I can get them out faster I most certainly will.

Am I going to need Excel 2016 to get value from the free 'DIY BI' e-Books?

No.  While I highly advocate being on a subscription version of Excel 2016, you'll find content in each of the first three e-Books which can be used in prior versions of Excel.

How do I receive the free 'DIY BI' e-Books?

You sign up for the Excelguru newsletter.  It's just that easy.  As soon as each e-Book is finished, we'll be emailing it to everyone who is currently subscribed to our newsletter.

And in the mean time, you also get a monthly email from us which now includes news about the latest updates to both Excel and Power BI.

Longer term, once all four e-Books are written, any new subscribers will receive the first e-Book upon signup, and then the next in the series will arrive every couple of days until you have the full set.

So what are you waiting for?  Sign up right here and don't miss out on free DIY BI Tips, Tricks and Techniques for your work!

Subscribe to our mailing list

* indicates required

Calculate Start and End Dates

I got an email from a reader this morning who asked how to calculate start and end dates for a given employee when they have had multiple terms of employment.  Since it's been a while since we've had a technical post on the blog, I thought that this would be a good one to cover.

The Challenge

In this case (which you can download here) we are given the table shown below on the left, and we need to create the table shown on the right:


As you can see, John's start date needs to be listed as Jan 1, 2013 and his end date needs to be listed based on the last date he worked here; Oct 31, 2016.

How to Calculate Start and End Dates using Power Query

My first thought was "we'll need a custom function to do this", but as it turns out, there is a a MUCH easier way to accomplish this, and it's 100% user interface driven as well.  I'm virtually certain that the performance will also be much better over larger data sets as well (although I haven't specifically tested this.)

Let's take a look:

Step 1: Connect to the data

This is pretty easy, just select the table and use Power Query to connect to the data:

  • Excel 2010/2013:  Power Query --> From Table
  • Excel 2016: Data --> From Table/Range

We'll be launched into Power Query and will be looking at our short little table:


Step 2: Calculate Start and End Dates via Grouping

The trick here is to actually use Power Query's Grouping feature to calculate the start and end dates.  To do this:

  • Go to Transform --> Group By

The dialog will open and is already offering to group by Name, which is what we need.  Now we just need to select the grouping levels.  The first is going to be our Start Date, so we'll rename it as such and change to calculate a Min of the From column:


The effect here is that this will provide the lowest value from the "From" column for each employee.  A perfect start.

Next, we need to add a new grouping level to get the End Date.  To do that:

  • Click Add Aggregation
  • Configure the new column as follows:
    • New column name: End Date
    • Operation: Max
    • Column: To

It should look as follows:


And believe it or not, you're done!


How this works

The key here is that the grouping dialog in Power Query works for all records in the group.  This is really important, as the first column has no bearing on subsequent columns… if it did, we'd get the max for the first record, which is not at all what we'd be looking for.  Instead, the Group By will restrict to find all records for John, then will pull the Min and Max out of the remaining three rows, returning those as the values.

The other thing that is worth noting here is that the order of the source data is irrelevant.  We could have provided either of these options and the answers would still have been calculated correctly:


Final Notes

It's also worth mentioning that this technique to calculate start and end dates will also work in both Excel and in Power BI, as the feature set is identical between the two products.

Sometimes things that look hard, are actually really easy when we have the right tools in our hands, and this happens to be one of those situations.  Smile

Using Power BI When You’re Not a Data Person

This week's blog post on using Power BI when you're not a data person is a guest post by Rebekah Sax of Excelguru Consulting Inc.

Last week I participated Excelguru's first ever "Data to Dashboards" Power BI Boot Camp. Seventeen of us lucky attendess were introduced to the incredible power of Power BI. Just to be clear, I am NOT a data person. My background is in marketing and communications, which is the main focus of my role here at Excelguru. So I was a bit nervous about being the only participant that was not in finance/accounting, an Excel jockey or a data analyst of some sort. Would I be able to keep up with the class and learn this brand new platform? How could someone go about  using Power BI when you're not a data person who usually works with numbers?

Welcome to Power BI

The boot camp consisted of three jam-packed days of hands-on exercises led by Ken Puls. We started off nice and easy with creating some basic visuals we are all familiar with, such as map, bar and column charts. By the end of the first day, we had learned how to build reports and dashboards using multiple data sources, and how to publish and share them. Later, we began writing DAX measures for filtering and shaping our data. This is where I learned that I CAN CODE! However, there is also a great feature called Q&A. This is a "query interface" which allows you to pull insights from your data using regular English, no coding required. For example, you can type in "number of locations" or "total sales in May" and Power BI will build you a basic visual that shows the information, pulled from your source data table.

For the last part of the day, we had fun playing around with cool custom visuals from the Power BI Custom Visuals Library. These are a great way to tell a story with your data by making it more visually interesting and engaging. Some of the options are a little whimsical. I mean, I don't know why I would ever need to represent data as fish swimming in a tank, but I could if I wanted to.

Power BI Custom Visuals Library

The number of custom visuals available in the Power BI library is really impressive, and many of them are free.

Cleaning and Working with Data

Our second day began by looking at the ways Power BI gets, transforms and loads our data using Power Query. This amazing tool is also used in Excel, so the techniques we learned are transportable between Excel and Power BI. I'm glad I had been previously introduced to Power Query, but some things were still challenging. For example, it took a while to wrap my head around Power BI's six different types of joins for merging two tables together. Luckily, I can use the copies of the example slides to use as a reference later on.

Inner Join

The "Inner" join type in Power BI. This join gathers only records which exist in both tables.

It was interesting to learn different techniques for breaking apart and recombining data from various sources into one cohesive data set. We also dove into formulas using M code (the Power Query language) and learning intermediate DAX measures (yikes, more coding!).

The Loaded Pencil

What really blew me away was when we started applying these measures to sales data from a fictional restaurant called the Loaded Pencil. First, we set up a calendar table and used the Calculate measure to start pulling out key metrics such as sales for a particular period. Another measure allowed us to compare to sales for the same period the previous year, quarter or month. Using different visuals, we easily filtered the data to view specific sales details. Our report even allowed us to look at the broad categories and then drill down right into specific items. We then set up cool visuals displaying food and alcohol sales as compared to budgeted goals. Now this was something I could sink my teeth into from a marketing perspective. I began to see real-life applications for using Power BI when you're not a data person!

The Loaded Pencil Sales Report

Sample visuals looking at the sales data from our fictional restaurant, The Loaded Pencil.

Taking It Mobile, RLS and Developing Like a SQL Pro

The first part of day three was really fascinating, where we focused on making our reports and dashboards mobile-friendly. Essentially, we built a mobile app displaying the outlets of a well-known coffee shop chain. It showed all the locations around BC and even let us target specific regions or cities, so you could find where to get your next caffeine fix. (Unfortunately, star points aren't tracked on this app, so I'll keep using the official one.) I could really the possibilities of mobile-accessible reports for a team with members regularly out of the office on site visits, client meetings, sales calls, etc.

Making Data Mobile

An example of our coffee shop Power BI Desktop report and the mobile-friendly version. Note that on the mobile version, the data set has been filtered to show just the Lower Mainland locations.

While the topic of Row Level Security are not something I need to worry about in my role, it was interesting to see the different methods that they can be created and applied to restrict the information shown to only that which a reader is entitled to see.

Using Power BI When You're Not a Data Person

One of the things that sticks out the most for me was chatting with another participant at the beginning of day two. She was so excited that she was already able to start using some of the stuff we learned the day before to her job. I have to say, my head was full to bursting by the end of the boot camp, but I did gain a few big insights:

  1. Using Power BI when you're not a data person to create a report can make it easier to present and understand the key information. This is especially true if you are like me and get overwhelmed by row upon row of numbers as on a traditional spreadsheet.
  2. Because you can move from the "big picture" down into specific detail with just a few clicks, you can make better connections and insights. You may even be able to pick up on something that has gone unnoticed but is having an impact.
  3. Being able to access precise, up-to-date information while on the go is huge. Imagine being able to answer a specific question right on site, without having to go back to the office and calling or emailing your client later.

Just remember, you don't have to already be an Excel expert to be able to use Power BI. It's certainly complex and can seem daunting, but the instruction here was fantastic and - if there's stuff that wasn't covered - it's great to know that there is also passionate community always willing to help someone trying to learn.