Getting Started with Calculated Fields

The first two blog posts of this series focused on getting the data in from Yammer’s Data Export into Power Pivot and then how to set up a Date Table to better manipulate the information. This set the foundation for getting data into Power Pivot. Now we’ll get into the fun stuff, calculated fields. Let’s get started:

Let me count the ways…

One of the best ways to use a calculated field is to get a total of certain things, like how many users are in network, how many posts, etc. In this example, we’ll work with Groups table and figure out how many groups we have in our network.

counts

Get a Group Count:

  1. In the Groups table, click into one of the cells below the last visible date point. The area here is where we’ll be entering our various calculated fields.calculated fields
  2. In the formula editor, enter the following:
    • Group Count:=COUNTROWS(Groups)
    • We just used the COUNTROWS function. This is counting the number of rows in the table and returning the total number it finds.

This is great, but as you can see from the table, there are groups that have been deleted. Let’s take a look at how we can count a specific subset of rows from the table.

Count just the Active Groups:

  1. Click into the cell below Group Count.
  2. In the formula editor, enter the following:
    • Active Groups:=COUNTROWS(FILTER(Groups, Groups[deleted]="false"))
    • We’ve now included the FILTER function. The function filters a specific table (Groups) on a specific criteria (where the deleted column is false). The COUNTROWS function then comes in and counts up the number of rows in that subset.

That was fun, but let’s get a little crazy. What if we want to get a count of the number of public groups in our network? How do you think you do this? Before you scroll down and see how, try it yourself!

Count the number of Public Groups:

  1. Click into the cell below Active Groups.
  2. In the formula editor, enter the following:
    • Public Groups Count:=COUNTROWS(FILTER(Groups, Groups[private]="false" && Groups[deleted]="false"))
    • Looking at the Groups table, you probably noticed the private column. If you challenged yourself before looking at the answer, you probably just set Groups[private]=”false” and called it a day. But remember, we also have deleted groups in this table, so we want to take those into account. We can use && logical operator to create an AND condition between two expressions. If you wanted to create an OR condition, you would use || (double pipe symbol). 

You can use the above methods to get a count of how many users are on your network, how many messages, how many threads, etc. Stay tuned for future blog posts where we’ll take a look at calculated columns and do some advanced calculated fields.

 

Creating a Date Table and Linking to PowerPivot Data

Recently, while working on Version 2.0 of Tosilog with Steve N., Steven P., and Noah, we realized that we couldn’t splice the data in Power View by time because each table had its own set of dates. The User Table had its joined_date. The Groups table and Messages table had their own respective created_at date columns. PowerPivot didn’t know that these dates were relative to each other. Luckily there was a solution, create a date table and link it to your other tables! In the second of my Tosilog Blog Series, I’ll be showing you how to do this.

Create a date table in Excel

  1.  In Excel, create a blank worksheet. In cell A1, type a column header name, like DateKey, to identify a range of dates.
  2. In cell A2, type a beginning date. For example, 1/1/2010.Excel Dates
  3. Click the fill handle and drag it down to a row number that includes an ending date. For example, 12/31/2016.Format as Table
  4. Select all rows in the DateKey column (including the header name in call A1).
  5. In the Home tab, select Format as Table, and then select a style.Format as Table 2
  6. In the Format as Table dialog box, click OK
  7. Copy all rows, including the header.

Copy the date table into Power Pivot

  1. In Power Pivot, on the Home tab, click Paste.Paste
  2. In Paste Preview > Table Name type a name like Calendar. Leave Use first row as column headers checked, and then click OK.paste preview

The new date table in Power Pivot looks like this: calendar

Linking the Date Table to other Tables in Power Pivot

  1. Before we can create a relationship between the Date Table and our other tables in the data model, we’ll need to create a Date column of the Date data type to link against.
  2. Right click on the column next to one of your date columns. For example, in the Messages table, right click on the deleted_at column and select Insert Column.
  3. Click into the first cell and give the column name. For example, we’ll call this Created Date.
  4. In the cell below the column name, copy the below DAX Formula. The formula will convert the values in the DateKey column to a date data type and remove the time level precision.
    • =DATE(LEFT(Messages[created_at],4), MID(Messages[created_at],6,2), MID(Messages[created_at],9,2))
  5. Now that we have this Date column, we can create the relationship.created_at
  6. From the Home tab, select Diagram Viewdiagram view
  7. Select and Drag Created Date from the Messages table to the DateKey in the Calendar table. Repeat the above steps for all of the dates that you have your Power Pivot tables. Below is an image where I’ve linked my tables to my Calendar table.diagram

Now when you combine data from these different sources, you can compare them against time using the data in the Calendar table. In future posts, we’ll cover other DAX formulas and how we can extend our Calendar table with Year, Month, and Quarter information.

Notes:

I learned (and copied) most of the above information from the following article on the Office Support site: Understand and create date tables in Power Pivot in Excel 2013.

Importing a Yammer Data Export *.csv file into PowerPivot

About a year ago, my colleague Steve Nguyen and I started a side-project that we called, “Codename: Tosilog.” (I’ll explain the name in another post.) It would take the *.csv files from Yammer’s Data Export and leveraging Microsoft’s Power BI, to create usable and actionable dashboards for community managers and verified admins. The project has grown to be wildly successful and used by organizations across the globe. In an effort to inspire others and continue the work we started, I’ve decided to start a blog series outlining how you can get started with Tosilog and Power BI.

Prerequisites:

  • I’m assuming that you are using Excel 2013 with the PowerPivot add-in enabled. (You could follow these instructions if you are using Excel 2010. I believe the functions are there, just might be in different places).
  • You have access to your Yammer Network’s Data Export. If you are the verified admin, you can run the export yourself, otherwise you will need to contact a verified admin to provide you with the data. See this site for more information on running a data export.

Importing into PowerPivot:

  1. From the PowerPivot ribbon, select Manage.
  2. From the Home ribbon, select From Other Sources. From Other Sources
  3. In the Table Import Wizard, scroll all the way down and select Text File. Select Next. 
    Table Import Wizard
  4. Select the Browse button to find the file from your Yammer Data Export. You’ll need to change the file selector from flat files (*.txt) to comma separated files (*.csv).
    • I recommend storing your Yammer Data Export in a generic folder like C:\Exports. This was if you are sharing your data model. You can have your colleagues set up a similar directory structure so they can use your data model as well.
  5. Back in the Table Import Wizard, make sure you check off “Use first row as column headers.” Table Import Wizard 2
  6. Now select what columns to import into your data model.
    • You will not need every column that is in the data export. For example, the column api_url is handy if you are working with the Yammer API, but serves us no purprose, so we will uncheck it. I’ll cover what columns to import for each data export file in a future blog post.
  7. Select Finish. You’ll repeat the above process for each of the *.csv files that you want to import into the data model.

That’s it!

Stay tuned for future blog posts where I’ll go through how to create a Date Table and link it to your imported *.csv files. I’ll also be covering some of the DAX formulas I’ve used to calculate the data we present in Tosilog.