If you are starting to learn SQL Server administration, or just need to rehash on your backup and restore fundamentals, here’s a great ebook to read:
Month: November 2014
Power Query 101: Get NHL Standings Data with Power Query
Last week at the PASS Summit 2014, I attended Matt Masson and Tessa Palmer’s session on Power Query Deep Dive. One of Tessa Palmer’s demo was a shout out to the Vancouver Canucks and how she has kept track of the standings of the Canucks by using Power Query.
That demo really piqued my interest (I like the Canucks too, but they keep on breaking my heart), so I wanted to try it out myself. This is my first stab at it, so I will probably look back at this post a few months from now and see where I could improve the process.
1.First thing to do, after installing and setting up Power Query, is to get the URL for the NHL standings site. The division standings can be found in: http://www.nhl.com/ice/standings.htm?type=DIV#&navid=nav-stn-div
The data is deeply embedded and formatted in HTML tables, as can be seen in the screenshot below. Getting this data pre-Power Query would have been possible, but would have required some nifty programming 
Hour of Code, Girls in I.T., Women In Technology
I was browsing through the code.org site to learn more about the upcoming Hour of Code event that is happening on December 8-14, 2014.
Through the Hour of Code event, code.org attempts to increase students’ awareness of computer science as not just another course, but as a life skill. code.org believes:
Every student should have the opportunity to learn computer science. It helps nurture problem-solving skills, logic and creativity. By starting early, students will have a foundation for success in any 21st-century career path.
In addition, code.org:
is dedicated to expanding participation in computer science by making it available in more schools, and increasing participation by women and underrepresented students of color.
This resonates very well to the Women in Technology luncheon I attended last week at the PASS Summit 2014. Many companies have partnered with code.org for this event and for many other projects, including Microsoft.
I came across this infographic about Girls in IT from NCWIT (National Center for Women in Technology) which echoes a lot of what Kimberly Bryant mentioned in her talk. (Get the Full Report).
Getting Started with Microsoft Power Query
I always enjoy the PASS Summits, and most of the time there is always a handful of things that I get excited about. I don’t even wait ‘til I get back to work to try these out, I usually try it out right after the session or the night after the session.
Lately I have been more involved in data warehousing, ETL and visualization/dashboarding/analytics. I have not had the chance to look more closely at some of the new packages or tools from any vendor for that matter. At this week’s PASS summit, I attended many of the Power BI and Power Query sessions. After I saw the first session on Power Query, I was hooked. There were some data sets that I’ve been trying to scrape with other tools – SSIS, PowerShell etc – that seemed to have been scraped and cleaned much, much easier (or should I say magically) by Power Query. Power Query – you had me at “hello”.
What is Power Query?
Let me quote Microsoft on this definition.
Microsoft Power Query for Excel, is a new add-in that provides a seamless experience for data discovery, data transformation and enrichment for Information Workers, BI professionals and other Excel users
To put it differently, it is the desktop ETL tool you wish you had, which you now do. I am no Excel guru, and I usually don’t look to Excel if I need to do any kind of data dump, cleanup and transform. Before even thinking of Excel, I would probably think of ways to do the cleanup and transform in T-SQL, SSIS, PowerShell, C# (sometimes in this order, sometimes not). After seeing Power Query, my list and order of preference for these tools have just changed.
So what do you need to start your Power Query adventure?
Download and Install
First off, you will need to download the Power Query add-in. You can download it from http://www.microsoft.com/en-us/download/details.aspx?id=39379. There is an Excel 2010 and Excel 2013 version, and there is also a 32 and a 62 bit version. So just make sure you know which Excel version you have. Should you make a mistake, the installer will tell you anyway, so no harm done.
Thoughts on #Summit14 PASSWIT (PASS Women in Technology) Luncheon
This year’s PASS Women in Technology luncheon takes a different format. In previous years, there is always a panel of women who give their thoughts and discuss trends, issues, opportunities for women in technology. This year, there is one guest – Kimberly Bryant (@6Gems) – who is the founder of BlackGirlsCode.com.
sqlbelle 