Microsoft Technologies, Power BI, Power Query

Using Power Query to Transform Website Data with Multiple Rows Per Entity

My colleague Hope Foley and I both enjoy a good craft beer. She has a great presentation on spatial data in SQL Server, which contains data about breweries. She mentioned she was gathering new data to add to her brewery database and showed me the brewery listing on the Brewery Collectibles Club of America website. This web page presents a challenge because of the way the data is laid out. Rather than having a table with one row per brewery, the web page uses one table per brewery with two rows in each table.
I typically use Microsoft Power Query to scrape data from websites for further analysis. Although this requires a bit of creativity (and some M), I knew Power Query was up to the task. And it gave me a chance to use my favorite Power Query function: Unpivot.

I opened up Excel and established my data source in Power Query. I chose From Web and entered the url: Once the data source loaded, I could see that there were over 3,000 tables within the page. pqbeertablemenu

Choosing table 0 showed me the columns headings for the overall table on the page.


I could see the headings for the 6 fields, arranged in 2 rows as we see on the webpage.  I decided to figure out how to transform this table into the one row I needed and then figure out how to automate my process so I could reproduce the results for each subsequent table. I used the Power Query GUI to transform my table into 1 row with the following steps.

  1. On the Add Column tab, choose Add Index Column -> From 0.
  2. Select the Index column. On the Transform Tab, choose Unpivot Columns -> Unpivot Other Columns. This creates a table with 3 columns: Index, Attribute, and Value.
  3. Select the Index and Attribute columns. On the Home tab, choose Remove Columns -> Remove Columns.
  4. On the Transform tab, choose Transpose.


This creates the following M code, which you can see by clicking on Advanced Editor on the View tab.

 Source = Web.Page(Web.Contents("")),
 Data0 = Source{0}[Data],
 #"Changed Type" = Table.TransformColumnTypes(Data0,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}}),
 #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
 #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Index"}, "Attribute", "Value"),
 #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Index", "Attribute"}),
 #"Transposed Table" = Table.Transpose(#"Removed Columns")
 #"Transposed Table"

Next I used that query to create a function by changing just two lines.

(gettable) =>
 Source = Web.Page(Web.Contents("")),
 Data0 = Source{(gettable)}[Data],
 #"Changed Type" = Table.TransformColumnTypes(Data0,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}}),
 #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
 #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Index"}, "Attribute", "Value"),
 #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Index", "Attribute"}),
 #"Transposed Table" = Table.Transpose(#"Removed Columns")
 #"Transposed Table"

I can see the number of tables available in my data source, so I can create a query that invokes the function for the required number of tables, in my case 3744. As a final touch, I used the first row as headers.

Source = Table.Combine(List.Transform({0..3744}, Query1)),
#"First Row as Header" = Table.PromoteHeaders(Source)
#"First Row as Header"

Et voilà!


I now have a single table of data with one row per brewery.

Personal, Telecommuting

Eight Things I’ve Learned in My First 3 Months of Telecommuting

A few months ago, I started a new job in which I telecommute. I was worried about whether I would enjoy it and whether I would be productive, and I wasn’t quite sure what to expect. I began my job by being as prepared as I could be and just going with the flow to get everything (laptop, online accounts, office furniture and equipment) set up with few expectations of how things would work. Now three months in, I can tell you that I love working from home. I can see why it wouldn’t be for everyone, but it’s great for me at this point in my life and career.

I will note that my company is a virtual company, which might make things easier than having some remote employees while others work together in a physical office. Almost everyone works from home (and many of us travel a bit).  Since we are all telecommuting, we don’t have the problem of some remote workers feeling like they are left out of office activities and banter with employees who are physically in the office. It feels like we are all in this together, communicating online and by phone as needed. Occasionally we meet up in person if we are in the same city or working together on a project.

Another advantage for my situation is that no one except my bulldog is in my house during the day to distract me. I can understand that it would be more difficult to work from home with a spouse, a roommate, or a child at home. Luckily, my office mate mostly sleeps all day.

So now that I have explained how things are going,  I would like to share with you some of the things I have learned thus far, working from home for a virtual company.

1.  I love the productivity and ability to concentrate that comes with working from home and having a flexible schedule.

I love that I frequently get 2 – 3 continuous hours in a day without interruptions where I am able to concentrate and make a lot of progress or delve deeply into learning a new technology.  My previous job was in a physical office that had the “cool” open floor plan, and my schedule had many meetings and several developers and project managers needing my help throughout the day. So this uninterrupted work time was a welcome change for me. On top of that, my company allows us to have flexible work hours as long as we are delivering good results and are generally available during common work hours when coworkers or clients need us. This may not be true for all remote workers, but my company trusts employees to set our own schedule.  This means I can go to the gym over lunch or take my office mate (bulldog) out for a walk in the afternoon and not feel guilty about it.  Ultimately, I have been able to arrange my schedule to achieve better work/life balance and increased productivity.

2.  Docking Stations are worth it.

I worked for a month just plugging everything into my laptop and unplugging when I needed to travel or just move to a different room. I finally picked up a HooToo Universal Docking Station for a decent price on Amazon, and it was well worth the money. Now my monitors, mouse, keyboard, camera, and headset all plug into the docking station, so it’s quite easy to unplug the one USB cord and wander off somewhere else with my laptop. The model I have works well with my Win 8.1 laptop so it quickly recognizes my connection and all peripherals when I plug back in.

3.  Ensure the dog has his supplies available in the office, too.

Each morning before I start work, I make sure I have my breakfast and coffee and any supplies I need for work that are not already in my office. I also do the same for the dog. I think my dog is cute, but not cute enough to get a starring role in my conference calls. My dog has a bed, some treats, and a toy in my office at all times. If he wakes up and decides he is bored or hungry or whiny while I’m on a call, I am prepared to quickly remedy the situation, at least until I can get off the call and let him out or figure out what he wants.

4. Amazon Prime has lots of good free music.

My office is a relatively quiet place, and sometimes I need some music to add a bit of background noise to work to when I’m taking care of tedious tasks (or when I have an earworm I just can’t get out of my head).  I already had a Amazon Prime subscription because I like free shipping, so the music was free for me to explore. Prime Music allows me to add certain songs, albums, or playlists to my library for free. I can then listen to them online or download them to a device for offline listening.  In addition, Prime Music also offers stations that allow you to rate the songs to customize the content (similar to Pandora). I like the selection offered, and new music is frequently added. Your mileage may vary based upon musical taste.

5.  Invest the time to become proficient in the use of your online meeting software.

When most of your meetings are online, and most people spend anywhere from a few minutes to half of every online meeting fumbling with settings in Lync or Webex (or whatever application you use), you can imagine how much of your time is spent just trying to get the meeting started and the appropriate content shared. I’m still working on this one, but things definitely go better when you are more prepared and knowledgeable in this area.  Once you’ve got it down you can help others learn the settings and help meetings around the world be more productive (or at least end sooner).

6.  It takes more effort to communicate with people when you rarely see them in person.

This may not be true for you, but it definitely is for me: I naturally interact better with people when I’m face to face with them. I was also used to getting a lot of information and context in informal conversations that occurred in the hallways in the office. Since there are no hallway conversations with people now, I have to make more of an effort to communicate with others. I had to get over any concern about bothering them and ask questions via Lync, phone, or email when I needed to do so (but not to the point of disregarding a busy status). My communication style over Lync has also changed. Where I was somewhat lax about spelling and grammar and was very brief/to the point, I now try to pay more attention to how I type and make sure I provide enough context for the conversation (which many times means switching from instant message to a Lync call). For many coworkers, their only interaction with me is our Lync or Yammer conversations, so I try to be friendly, make a good impression, and show a little of my personality while respecting their time and making sure they get what they need from me in the conversation. I’ve been at companies that had online message boards and used Lync a lot, but things change when these are your main methods of communication.

7.  Some (but not all) UPS stores will verify I-9 forms.

Form I-9 is used for verifying the identity and employment authorization of individuals hired for employment in the United States. In order to complete the form, the employer must physically review and verify the information and documents provided by the employee. This is very standard and something we all do when we start a new job in the United States. It gets interesting when you start a new job as a virtual employee that does not live close (within a 5 hour drive) to any other employees, and the company’s lawyer has provided guidance that this verification should not be done over video chat. This is how I learned that some UPS stores offer a service where they have a notary do the physical review and verification and sign off on it. Then you can send the form back to your company with the notary’s signature indicating the review is complete. Not all notaries will do this, and not all UPS stores that employ notaries offer the service.  I was warned in advance to call ahead and check, so I did and found one not too far from me that got the job done. This was more just an interesting intricacy of telecommuting with a geographically distributed company, but maybe it will be helpful to you.

 8.  I’m not lonely, and I didn’t become a hermit.

This is what I worried about most before starting my job. Although I am an introvert, I’m a fairly social person. If you aren’t aware, introverts are not always shy; they tend to prefer to spend more time alone or interact with small groups of familiar people rather than larger crowds or new people. I wondered if I would really like a job where I didn’t physically interact with people on a daily basis. I wondered if I would just never leave my house because of lack of work/life balance or laziness. So far none of those fears have been realized. If anything, because I have a flexible schedule and better work/life balance, the activities I do outside of my house feel more meaningful. I leave the house to be productive or enjoy time with friends and family rather than just because it’s Wednesday and I have to go to the office to work. I don’t have to worry about running home at lunch or after work to let the dog out because he was with me all day and I let him out right before I left the house. I have 30-45 minutes back in my day that I used to spend on my commute that I can now spend on something more enjoyable. And because I’ve been home all day, I’m a bit more motivated to go out and do things. On top of that, I have friends and coworkers that I can chat with during the day. So if I just need some interaction, I know who to turn to that will gladly chat for a few minutes.

Further Reading

Here are some good materials to read if you are considering telecommuting:
The 10 Best Articles on Managing a Remote Team
Telecommuting, Month 9
Why I Don’t Look for a Telecommuting Job
Eight Things I Have Learned While Telecommuting
A Desk Too Far?: The Case for Remote Working
How to Telecommute: Staying Motivated
How to Telecommute: Getting Things Done
What I Know For Sure…After One Year at SQLskills