Studio711.com – Ben Martens

Movie Draft With Power Query

I know I’ve been making a lot of Power Query posts lately, but once you get into it, you start seeing data everywhere! The latest example is a movie draft that I participate in with some friends. I won’t explain what a movie draft is except to say that it’s like fantasy football for movies. We participate in a huge league run by Brian Brushwood and friends. They post a spreadsheet with all the data here: http://draft.diamondclub.tv/

I pulled those spreadsheets into Power Query, cleaned up the data, and voila, now we have an easy way to track just the people in our little group! You can see it at http://bit.ly/excelmovies We get interesting views like a leaderboard just for our group, and a list of the movies left for each one of us. I might add some more data views later, but these are the basics.

So if you’re in our movie group, how can you use it? You can use it passively by just visiting that bitly link every once in a while. I’ll update the data periodically for your enjoyment.

If you want to update the data yourself, add your own friends to the list of names to watch, and/or learn how the workbook was built, then you’ll need to do a little extra work.

  1. Install Office 2010 or 2013
  2. Install Power Query. If it fails then you don’t have the the right version of Excel. Sorry, you’ll have to stop here. (I continue to tell management that I think this is a bad decision.)
  3. Go to the bitly link and click Edit Workbook > Edit in Excel
  4. Save the file as a local copy.
  5. Click Data > Refresh All and wait a bit. When the status bar along the bottom of Excel completes then you should be seeing new data.
  6. You can see the Power Query queries that make up the workbook by clicking the Power Query ribbon and then clicking Workbook. You’ll see a side pane appear and you can double click any of those queries to start digging in.

Unfortunately I think Step 2 is going to stop a lot of you, but at least you can view the online link. And if Power Query doesn’t work with your version of Office, please feel free to complain in our forums!

benmartens April 14, 2014 Geek

What If I Don’t?

Last September, I didn’t step foot in the office. I was on paternity leave the whole month. It was a wonderful time to spend at home with my family and connect with Tyla and Elijah. When I got back to work, I took stock of the changes. There was a long list of work that I had been doing on a regular basis that hadn’t happened for a month… and for many of those things, nobody cared! It was a big revelation for me. As I spend time in the same job, my list of tasks is always finding new additions. I rarely cull the list so it just grows. But does it all really need to be done? Obviously not if nobody cared that it hadn’t happened in a long tie. Some things, of course, need to happen whether people care or not but that’s an exception to the rule.

As my free time at home gets squeezed more and more (parenting takes a lot of time!), I’ve been trying hard to apply this at home too. I regularly ask myself “What if I don’t do X?” What if I don’t mow the lawn or clean the furnace filter? Well that’s clearly not good. But what if I don’t watch that television series or watch that movie or spend a day doing a hobby? It takes some practice to answer that question honestly because there are many activities that I think make me who I am. But once I forced myself to find a good answer, I’ve been able to remove a ton of old habits from my life. And you know what? I don’t miss them! In fact, it feels liberating to have all this time handed back to me.

Having a child was a forcing function to make this happen since it put such a huge constraint on my available free time, but it feels like a very healthy experience. Instead of trying to schedule in a bunch of habits, I now schedule in time to just “veg” and do some random activity that used to be a time-sucking habit. It’s a good balance for me right now and lets me focus on things that I really do consider important and high priority.

benmartens April 11, 2014 Commentary

Four Years

It seems like a short chunk of time
Since I married my partner in crime
But now there’s a son
Who can nearly run
And my thoughts are all metered and rhymed

I can’t end a line with your name
No rhyme exists I claim
Four years ago
You married your beau
And my heart is still aflame

benmartens April 10, 2014 Commentary

Windows 8.1 Update 1

If you’re running Windows 8, you probably got a pretty big update this week. The Windows team has been listening to the feedback and has made some pretty key tweaks to improve your user experience. PCWorld says “This digital love letter should woo over all but the most obstinate of the PC faithful.” So what’s included?

The user experience is tailored depending on the type of device you’re using. If it’s a traditional mouse and keyboard desktop computer, you’ll boot to the desktop and media files get opened in the desktop version of the apps instead of the full screen Modern App experience. Additionally, you can right click on tiles on the start screen and there’s a bar at the top of the screen for full screen apps with common controls like minimize, close, etc. Those full screen Modern Apps will now show up in your taskbar on the desktop just like any other app.

There are a couple other tweaks, but those are probably the biggest things you’ll notice after you apply the update. Go forth and enjoy! And keep giving feedback not just on Windows but on all your favorite Microsoft products. They listen!

benmartens April 9, 2014 Geek

Home Improvements For Baby

When I look back at all the things I did around the house before our son was born, I’m amazed at all the free time I had. But there are a few home improvements that stick out and continue to pay benefits. If you’re having a baby and you’re a little bit handy, consider these projects:

  • Dimmer switches: Put one in the nursery so you don’t blind your child or stumble over toys when he’s crying in the middle of the night. If your child is going to spend some time in your bedroom, put one there too. The nursery can be a regular dimmer switch, but for the master bedroom, consider splurging a little on a dimmer switch with a remote control. I think that’s Tyla single favorite project that I’ve ever done. It’s so handy to be able to shut off the lights without getting out of bed or to turn them on a tiny amount in the middle of the night.
  • Tamper resistant outlets: You’ll probably want to buy these at Home Depot or Lowes instead of Amazon, but they will remove the need to cover all your outlets with those plastic plugs. I replaced every single outlet in our house with these, mostly because we had some flaky old outlets, but it’s paying off now.
  • Black out drapes and blinds: I added black out drapes and blinds to the nursery. His windows will catch the sun in the summer when it’s up the latest and you can’t make a nursery too dark. We might even have to add a bit more covering to catch the light.

There’s lots of other things that could go on the list but those are the big three that I recommend. Feel free to comment if you have anything to add to the list!

benmartens April 8, 2014 Uncategorized

March Madness Brackets

The tournament isn’t even over and already our bracket pool has been decided. Nobody even has a shot at getting the final game correct. I don’t think that has ever happened in our pool before and I also think this is one of the lowest scoring pools. But all that aside, congrats to AndyB who beat out second place Logan by one point and third place Jay by two points!

Jay and Logan have both won in the previous two years. Following up with second and third is a pretty good showing.

Now we can all enjoy the final game without wondering who is going to win the pool. And when I say “we” I mean “you” because most likely I’ll be giving a baby a bath and putting him to bed. I’ve watched approximately 7 minutes of the entire tournament this year.

benmartens April 7, 2014 Fantasy Sports

Website Traffic

I used our nifty new Power Query tool to connect to the database that contains all of the information for this blog to see what I could learn from the piles of data. I found a table that showed how many visitors I had each day for the last few years. Sure this is available from Google Analytics but this is the raw data straight from my blog management program so it should be about as accurate as you can get. It looks like traffic was growing for a while and then fairly abruptly dropped to a pretty constant level near the end of 2011. I’m not sure what that change represents, but the overall chart does reflect my expectations. This is never going to be a blockbuster site. It’s for friends and family to catch up on some of my ramblings and that’s about it.

Oh and don’t think that 500-1000 people are reading my site every day. This counts everything that hits my page so a lot of it is explained by spam bots and search engine crawlers.

benmartens April 4, 2014 Site News

Package Camera

A while back I wrote a post talking about the cameras we’ve set up around the house and the software that manages it all (BlueIris.) There’s nothing terribly new in this post except to give a long term test update and say that I’m LOVING this setup. The garage cam lets me easily check to make sure that I remembered to put the garage door home or see if my family is home so I can ask Tyla to defrost some meat for dinner. And since we don’t regularly use our front door, the camera there is a great way to know that we have a package waiting or that someone left a flyer in our door. I even get an email showing a couple images and a 5-second video of whoever was at our door. All of the cameras are great but those are the two that I use the most regularly and I’m eager to add some more coverage.

If you ever want help with a similar setup for your house, let me know. I’m happy to share info. You can get going with one camera and the software for around $100-$125 and then each additional camera after that is about $65. You technically can skip the software but it makes life a lot easier.

benmartens April 2, 2014 Geek

What If: Amazon Stock

In yesterday’s post about all my Amazon orders, JonathanC posted an interesting comment: “How about this one as a Power BI challenge: current value of the portfolio if you had purchased stock of Amazon instead of the product.” … Challenge accepted!

I already had a list of all my Amazon orders. I think headed to Yahoo Finance and downloaded a daily history of Amazon stock stretching back to 2001 when I made my first product purchase. That came as a CSV file too. Both files got loaded into Power Query. I did a merge on the date column, removed the columns that I didn’t need, and added a custom column that was the money spent divided by the closing share price that day. That told me how many shares I could have purchased. A quick sum over that column divided by a sum of the money spent revealed that I would have made an 81% return on my money! But wait a minute, transactions aren’t free. If I was pay $8 per transaction then I would have only made a 37% return. But that’s silly since I probably wouldn’t have bought $10 of stock at a time like I do with Amazon orders.

benmartens April 1, 2014 Geek