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.

March’s #ResponsiveResolution

Recap:

February’s #ResponsiveResolution was not as successful as January’s, actually it was an utter failure. It started off great and I was journaling every day, but then other responsibilities started to creep up. Journaling fell further and further down my priorities list to the point that I wasn’t journaling at all.

Lessons Learned:

The best thing about failing is you can learn from your failures and use it to make you a stronger and better person. I realized that I needed to block out time to get journaling done and set reminders for myself to do it. This has carried over to my work life where I’m now scheduling out blocks of time for a specific topic and using that time to focus solely on that topic. I’ve also realized that I need to keep it simple. I have a tendency to over complicate and over plan. Originally, I was answering a set of four questions, but that was too much. I’ve trimmed it down to just one, “What did I accomplish today?” Four days into March and I’m still journaling.

This month’s #ResponsiveResolution:

The last two months have been focused on doing things to keep me mentally healthy. In January, I focused on not putting things off and I’m feeling more in control of my “to do’s” and other tasks. In February, I focused on journaling so I can better keep track of what I’ve accomplished. This month I’m focusing on my body and doing a physical #ResponsiveResolution.  

February’s #ResponsiveResolution

Recap:

I’ve have to say that my January #ResponsiveResolution was a resounding success. I stuck to my mantra to “not put off to tomorrow, what I can do today.” This meant everything from putting away the dishes after dinner to doing what my wife asked me rather than waiting until later. I’ve found that I’m feeling a lot more control and not as anxious for the next day. What really helped with this is letting my fellow #ResponsiveResolution brethren in on my objectives. @espnguyen was great in that he would check in on me periodically on how things were going. His keeping me accountable helped motivate me to see the month through. But, honestly, I think this month has been a little too successful.

Lessons Learned:

After spending an entire month of not putting things off, I get really anxious when I do. My family is just coming back from a weekend getaway to the Virgin Islands courtesy of my wife’s company. Late Saturday night, we found out that United cancelled our flight back to New York due to the impending snow storm. Rather than taking care of the issues right then and there (what I wanted to do), we waited until the next morning (what she wanted to do). I found that this caused the situation to creep into my subconscious. That evening, I was having dreams (or nightmares, as I saw them) about not having our travel situation resolved. Fears of being stuck in paradise long into the week to having to settle for the most roundabout itinerary just to get back to New York, danced in my head. The next morning as we waited to hear what others in our group were going to do, I kept a straight face. Internally, it was another story, I was twisted, wanting to just forget the others and get things squared away for us. Minus this recent situation, I’m very happy with the results and  feel like this resolution has laid the foundation for the rest of my 2015 #ResponsiveResolutions.

This month’s #ResponsiveResolution:

@espnguyen recently introduced me to an interesting new podcast called “The Productivity Show” by Asian Efficiency. Anyone interested in GTD, as well as, productivity hacks will find the show really interesting. One of their first topics was the idea of “Journaling.” I found this one to be intriguing as I’m finding the my days, weeks, months, have started blending together and as I look back, struggle to see what I’ve really accomplished. So for the month of February, my #ResponsiveResolution is to practice what they call, structured journaling, where each day I answer a set of questions to recap how my day has gone.

2015 NFL Divisional Playoffs

It’s almost 2 a.m. and I can’t sleep.

Less than a week removed from my beloved Baltimore Ravens upsetting the Pittsburgh Steelers, 30-17, they’ve got another tall task ahead of them, the top-seeded New England Patriots. Many of the pundits are picking the Pats to take care of business and move on to face either the Colts or Broncos in the AFC Championship game. But just in case the Ravens pull off a second stunner in a row, there have been countless articles of how Flacco has out-dueled Brady and this is the one match-up that the Pats did not want. One thing’s for sure, both will be ready to play and this is shaping up to be a classic.

Now on to my predictions:

Ravens at Patriots: My pick, Ravens.

Panthers at Seahawks: My pick, Seahawks.

Cowboys at Packers: My pick (blech!), Cowboys.

Colts at Broncos: My pick, Colts.

I was very tempted to pick the Panthers to complete the road team sweep, but the Seahawks are just too good at home.

 

#ResponsiveResolution

Inspired by @espnguyen and his post, I’ve decided to join his #ResponsiveResolution’s movement and take a month-to-month approach to resolutions in 2015. The goal is to adapt your resolutions as the year goes along. I’ll be tweeting and tracking my progress against these goals here. Feel free to tweet me (@PSAgustin) to help me stay accountable. If you want to join, tweet about it and tag it with #responsiveresolution.

January 2015 #responsiveresolution

One of the things I’m most guilty of is leaving dishes in the sink and waiting until the next day to clean them up or vacuuming and leaving the vacuum out. By the end of the month, my behavior will be changed so that I’m more proactive in everything that I do, whether it’s in my home life or my work life (I’m looking at you expense reports!).