Tag Archives: Excel


Using Power Query to Analyze Your Schedule

I am in a lot of meetings. A LOT of meetings. Double, triple, quad booked. I guess when you get to manager or director level somewhere, that is the definition of “busy”, or maybe everyone just wants you in their meeting, or your opinion, or whatever. In the end “Meetings are Toxic” (from 37signals), but really the are sometimes a necessary evil.

Anyways, do you really know where you spend all your time? Well you can glean the information pretty easily using Excel and Microsoft Power BI (Power Query specifically).

First, the key for me is to “categorize” my meetings. You can create categories in Outlook and then assign them to meetings, you can even color code the categories.

Where does Power Query fit in? Well, you can connect to Exchange as a data source.

power query exchange

Then you can query your calendar “table”, and pull it into Excel.

power query navigator

power query

Then, as with any table, you can Pivot it, and pull over category as the row, and look at the count. With some column work in the Power Query query, you can split out the date/time and get Month/Day/Year and create a semi-hierarchy, to see things over time.


For example, I took over 2 teams in January, and my meetings with them and related projects skyrocketed in January. Now I know what was taking my time up for Q1 2014 :)

meetings over timeAt least the number is going down :)

There is so much more you can do with Power BI and Exchange data, your email, calendars, contacts, etc, this is just the tip of the iceberg, and it should only take you 10 minutes or so to get to this result! Now, if I can just figure out how to get out of the meetings!


How Cold Is It?

With the latest “Polar Vortex” or whatever that is happening, EVERYONE is talking about the weather. Everyone always talks about how it has been this cold many times, etc, etc. “It was colder in my day” – ok. Well prove it!

So I took a look at the NOAA data you can get here http://www.ncdc.noaa.gov/cdo-web/ and got an extract to CSV for my hometown of Chisholm, MN (actually the Hibbing/Chisholm airport since it has data from 1962 to today)

I downloaded the CSV, opened in Excel 2013 and imported into Power Query. I think did some formatting to get the date parts and a date field, and converted the “tenths of a degree of Celsius” to Fahrenheit. Then started analyzing.

I will have to refresh this after this cold spell, because it only has data to 1/1/2014 and these last few days have been cold, but, not the coldest.

Back when I was 16, in 1996, there was a stretch of days in January that were COLD. The data supports this. First I took all the days with a Low temp of UNDER -35 degrees F.

Chisholm Low Temps


You can see, there are a bunch of days in Jan/Feb 1996 that were UNDER -35 Degrees F. So then I copied that pivot and expanded on that date range to see all the days.



Pretty dang cold from 1/19/1996 to 2/4/1996. Lowest day was -50 Degrees F. Average of -31 Degrees F. Of course these are “real” temps, it was even colder with wind chill. These last 3-4 days of -20 to -40 are cold, but not sure they are colder than in Jan 1996. We will see when it is all said and done.

If you can’t remember how cold it was, NOAA, Excel and Power Query can remind you. :)

I have the spreadsheet up on Skydrive. http://sdrv.ms/1gBwMPL

Analyzing ADFS IIS Logs

If you are using Active Directory Federation and you want to see what users are logging in when to what external service, you can analyze the ADFS server IIS logs. It is pretty straightforward since it is just IIS.

First, get to your ADFS box, get to the IIS log directory, usually something like “C:WindowsSystem32LogFilesW3SVC1″ and grab those logs.

Install LogParser on your machine.

Now, you can write sql type queries against your logs. For ADFS logs, we don’t care so much about many of the columns, but primarily username and date, maybe the URI for filtering, maybe the referrer or the user agent to see what browsers your users are using, but to get say, unique logins per day for a given service, we just need the date, username and URI.

Remember the date is probably UTC so you need to use a function to convert, or leave as is if you want, and everything is pretty much all relative depending on how accurate you want things to be. hint: TO_TIMESTAMP(date, time) AS utc-timestamp, TO_LOCALTIME(utc-timestamp) AS local-timestamp

Now, here is the LogParser query:

logparser "SELECT DISTINCT cs-username, date INTO FROM WHERE cs-username NULL and cs-uri-query LIKE '%your service%'"

Note in the statement the output path and your log path, change to what yours are. Also, the LIKE statement. For example, to query for Microsoft Dynamics CRM Online, I used

LIKE ‘%dynamicscrm%’

Run that query, then open the .csv you exported to. Format the data as a table, pivot it by user, pivot by date. Get the unique number of days using a date diff, analyze logins per day, logins per user. Tie to Active Directory (using Power Query) to add some dimension attributes like title or department and very quickly you can analyze what users, departments etc are using your service.


Yamanalysis: Analyzing Yammer and Using PowerPivot on MySQL

I have blogged before about we use Yammer. Some interesting data can be gleaned from the usage of Yammer. One thing though is that the data and usage stats are limited in the Yammer area, but you can get all the data and take a look at things. I ran into Yamanalysis and decided to give it a try.

After getting Ruby, Rails, MySQL, curl/curb, GraphViz, IBM WordCloud and whatever else configured, I finally got it working. (FYI – MySQL 5.0 – you need to run the config wizard as administrator on Windows 7 or it just hangs at the end).

Pretty cool data and analysis from a higher level. Of course after getting everything working, I wanted to hit the data with PowerPivot. This sounds like an easy feat, but yet seemed to be a complicated task.

I first got the ODBC connector 5.1 for MySQL (Since PowerPivot doesn’t natively connect to MySQL,and 5.1 since that is the only one I could find reliably and get to work.), and set up an ODBC source. Tests fine.

In PowerPivot, I would run through the wizard and it would get architecture mismatches, and catastrophic failures, trying to test the connection. Ignoring that and moving forward, running a query would just hang on import forever. I tried different DSN’s, User/System DSNs, etc, to no avail.

What I ended up doing was firing up my local Microsoft SQL instance, and creating a linked server through a system DSN to the MySQL instance, then I could query the data fine from SQL. I opened up PowerPivot, connected to SQL local and then ran the query to MySQL and it work. What a workaround, what a hack, but at least I can hit the data in PowerPivot locally, which was my goal here.

Of course I could take what Yamanalysis is doing and dump to SQL, or do something similar in C# and dump to SQL, that might be a project for another day.

office.live.com – Docs in the Cloud

For a while, there have been offerings from Google (Google Apps/Docs), Zoho, and others, and recently, Microsoft jumped into the “online office” game with office.live.com.

Pretty cool. Word, Excel, PowerPoint and OneNote in the cloud. Limited, but you can create docs, share them, edit them, with *no software* installed on your system. Good in a pinch, good to sync some docs up that you might want to edit, good on a relatives computer that doesn’t have office.

Where have I started to use it more though? OneNote. What is OneNote? Well, before Office 2010, OneNote was installed as a separate application with Office. Kind of like Visio. My biggest problem with OneNote was that I was stuck to my laptop, or whatever. Had to be on the actual box.

Now, you can create a OneNote notebook in the cloud, and edit it in the browser, or on your machine. OneNote also has some cool features, like

“You can now share your notes with other OneNote users in real time by hosting or participating in shared note-taking sessions. Over a live connection, you can work together on a project or share a read-only copy of your notes with an audience.”. It also integrates nicely with tablet/bamboo and mic/text/voice recognition, as well as Outlook and meetings, etc. Integration everywhere.

I actually like OneNote better than Evernote – but Evernote I can get on my computers, web, iPad and iPhone. Yes, there is a OneNote iPhone app (MobileNoter) but you have to install a client, blah blah. Should just work over the cloud.

Anyways, if you need office online in a pinch, try office.live.com, but also check out OneNote and using it with others to share/read notes in real time, really cool features.

Microsoft Silverlight PivotViewer: Getting Started and Business Case

I have been reading about Microsoft’s PivotViewer lately, and decided to try to get it going for myself. What is PivotViewer? Think of it as visual data slicing through a web page.

What you do is take some data, and then tie records to images, and then publish out your “collection” and you can consume it via a webpage using the Silverlight PivotViewer control. One awesome example of this is here http://netflixpivot.cloudapp.net/. But what I have been really trying to wrap my head around is how to use the as a “business” tool. Because, it is easily technically doable, but you have to have a *reason* to do it.

Working with widgets and customers and locations – what do you do? There are two things I could think of quickly. One – peruse your “master data” very fast and visually. The other is looking at some kind of metrics for your widgets, or logos of your customers you might sell too, or ? ..Well, you could..

  • Master Data/Catalog
  • Show pictures of your widgets, and create filters (they call them facets) for things like size, color, weight, model, etc. You have “one” of each and you just want to see what you offer. Almost could be a pretty sweet online catalog browser

  • Sales/Metrics
  • Do the same as a master data catalog but allow filtering by some kind of metric. Shipped items over a given time or something.

  • Something Else I Haven’t Thought Of?

Anyways, the first thing you should do before anything is get some kind of data feed. Run a query, get some data from somewhere. Start small to test. 500-1000 records.

Then the fun begins. Starting from absolute scratch..

Ok, yeah, tons of setup. Biggest thing is in IIS you need to set some MIME types: .cxml, .dzi and .dzc need to be “text/xml”

Once you have all that setup, you can do 2 things.. create your collection, and create your app. Create a blank silverlight project first:

Once you have that, there isn’t a ton you have to do to get things going with PivotViewer.

  1. MainPage.xaml
  2. Add in your MainPage.xaml, a namespace line for Pivot, and add the control

    Your end MainPage.xaml should look like this:

  3. Reference Assemblies
  4. For good measure, just reference them all, located here: C:Program FilesMicrosoft SDKsSilverlightv4.0PivotViewerJun10Bin

  5. Load Collection:
  6. pvWidgets.LoadCollection("http://localhost/SilverlightApplication1.Web/MyWidgets.cxml", null);

Note, you have to make your web part of your project IIS based instead of the build in web browser. Why? Because the .cxml HAS to be hosted on a web server, it just works that way.

Now, you need to create your collection.. you can use a cmd line tool they offer on the PivotViewer site, they also have a c# library for automating things, but it is best to first just do it manually. So I used the tool they have as an add on for excel. It adds a nice little “Pivot Collections” tab

You can use this to put some data in, you probably want to add more columns than what they give you by default. For my test I just used the same image for all records to get started. I have a feeling that the biggest barrier to entry to corporate BI teams and developers is going to be the imagery. You usually don’t have someone on your BI Team that knows how to use photoshop well and do all the high res imagery, so you are handcuffed there.

For testing sake, I Published my collection to the root of my website, with the name “MyWidgets”.

I loaded up my webpage, and I can slice and dice by all my columns I had in my collection, visually.. pretty dang awesome. (Note, I just made some fake data based on attributes I am used to seeing and with an image of a bike to see what it would look like – the goal was figuring out how would this work in conjunction with current BI offerings (cubes/Pivot Tables, SSRS, PowerPivot, etc))

Now, if you think where you could take this. Each “image” is clickable and brings up the image right in front of you. You could have all the specs of that widget there, and a link to “buy”, or deeper analytics for that widget.

Some other things I found out.. using the Excel tool for Pivot Collections is dog slow. Especially with a ton of records. It has to process the images for the “deep zoom” technology and it just takes a while. Like, hours.

There are tons of possibilities here with PivotViewer, both for an external website and also internal corporate business intelligence. It will give people another way to delve into the data and turn it into information.

Exporting Data from SQL Server to Excel Directly

Had a request to export data out from SQL Server to Excel directly. There are a few ways you can do this. BCP is one, another is OPENROWSET. SSIS, etc.  Here is the OPENROWSET method, using Jet (I think this only works on 32bit servers as well)

First you need to enable a setting on SQL, so.. Start->Programs->Microsoft SQL Server 2005->Configuration Tools->SQL Server Surface Area Configuration, then click on the “Surface Area Configuration for Features” at the bottom.

Under database engine, make sure “Enable OPENROWSET and OPENDATASOURCE support” is checked under Ad Hoc Remote Queries option.


Then, you need to create an empty excel workbook, with the columns of the query you want to export out. Create one on C:Testing.xls with “Name” and “Rating” columns

Then run this query:

'Excel 8.0;Database=C:testing.xls;', 
'SELECT Name, Date FROM [Sheet1$]') 
SELECT 'Steve','1'
SELECT 'Joel','2'

And if everything worked, your xls should have 2 rows in it.


Now, if you want output to other spreadsheets, you could dynamically set the filename in the statement, or other trickery!