Another look at PASS Summit 2019 sessions using Power BI Desktop and “Get Data From Web”

Last week I published a post on how I used Power BI Desktop to import PASS Summit 2019 session schedule and build a few reports for myself to help me sort my sessions of interest by day/time/room and decide which ones to attend.

In that post, I had initially tried to import the schedule with “Get Data From Web” within Power BI Desktop but was not able to do so because I ran into the following error and did not know how to get around it:

Unable to Connect: We encountered an error while trying to connect. Details:”The server committed a protocol violation. Section=ResponseHeader Detail=CR must be followed by LF”

It looks like something on this page is not following the expected HTML pattern so “Get Data From Web” is not able to pull its data directly.

Thankfully, Reza Rad showed me an alternative way to get data from a web site at SQL Saturday Atlanta. I was able to use this method that I will explain later in this post to pull the sessions schedule directly from PASS’s website and bypass the manual export to PDF/Excel and import into Power B Desktop steps. This makes it a lot easier to keep the report up to date with changes in the schedule as they can sometimes happen at the last minute.

You can find the new pbix file here: “PASS Summit 2019 Sessions From Web Live.pbix“.

Sessions Summary

In this Power BI Desktop report, in addition to the session categories I had used previously, I added some Word Cloud and Q&A visuals.

The Q&A feature is pretty cool. You can ask questions such as “sessions on Power BI” or any other topic and get a list of the sessions. This works out of the box without any more configurations. You can also ask questions such as which speaker has the most session count? (Hint: The answer is Hamish Watson as seen in the Speaker Word Cloud visual. Check out all the Speaker Idol sessions).

The following shows the “Power BI” report section which is one of the session categories I am interested in.

One of my favorite reports is the “Room Schedule” report. It looks like if you don’t like walking and want to stay at one room/building, any of the TCC rooms will have plenty of Power BI/Azure/AI sessions.

Get Data From Web and PASS 2019 schedule

If you try “Get Data From Web” to pull data from PASS Summit 2019 schedule page, you get an error. This method works for a lot of pages but for some reason it does not work for this page.

You get this error:

Unable to Connect: We encountered an error while trying to connect. Details:”The server committed a protocol violation. Section=ResponseHeader Detail=CR must be followed by LF”

One way to get around this, is to start with “Edit Queries” from scratch and use the M language function Web.Page(Web.Contents()) to pull the data. Instead of doing this, you can connect to a different web page (Rotten Tomatoes Top Movies) that Power BI is able to read from and edit the web address later in the Query Editor. That’s what I did.

Start with Get Data From Web:

Type in the web address of a page that has any kind of a list in it, for example: https://www.rottentomatoes.com/top/

Once the page contents show in the Navigator window, choose any Table from the page and click on Transform Data.

When the Query Editor opens, under Applied Steps, remove “Changed Type” and “Navigation” steps. Click on the “Source” step:

This step shows the M function Web.Page(Web.Contents()) that is used to pull the page data:

= Web.Page(Web.Contents(“https://www.rottentomatoes.com/top/”))

Change the web address to https://www.pass.org/summit/2019/Learn/Schedule.aspx

Once the contents load, you can see that each day of conference has its own Table. Click on each Table to add it to the model.

You can download the pbix file shown above from here.

Once you have this file, it is easy to do some clean up and end up at the pbix file that I used to look at the sessions.

Hint: You can get one of the days cleaned up to the form you want, duplicate it and change the navigation step to a different day by changing the number in “Source{xx}[Data]” as highlighted in the picture above,

The final pbix file is available here:

PASS Summit 2019 Sessions From Web Live.pbix

PASS Summit 2019 sessions review: What sessions to attend?

PASS Summit is only 2 weeks away. It is time to review the sessions and decide which ones to attend. You can find the published schedule here. This is a great at-a-glance look. There is also a searchable version of the schedule published here. You can use this one to search by keyword or track and you can add sessions to your own schedule if you are logged in. There is also an app that you can use. Either way, you will see that there are plenty of great sessions available to choose from.

While I find both links useful, I was looking for a better list for my own use that would make it easier for me to choose sessions. I wanted to come up with a list of sessions I am interested in based on topic and sorted by day/time along with the room number. To do this, I built a Power BI report!

I downloaded the PDF version of the schedule from the bottom of the schedule page. Then I exported that to Excel using Adobe. Once I had the schedule in Excel, I imported it into Power BI Desktop.

Note: I have posted an updated version of this post where I was able to user Get Date From Web to directly pull the data into Power BI Desktop. See the newer post here. The live report is also available from here.

The rest was easy. Power BI is amazing at quickly cleaning data and shaping it into a usable format. I parsed the session names and came up with categories that I was interested in such as Power BI, Power Shell, PowerApps, Power Platform, Azure, Speaker Idol and Others. These categories are Power Platform/Cloud focused.

You can download the Power BI file from here and create other categories by editing the M expression that creates the Type column.

Disclaimer: The sessions schedule is subject to change and if that happens, this list may not be up to date. For the latest information, always check the main published schedule. Rooms/times can change even on the day of a session so make sure you download the conference’s app on your cell phone or check the web page for changes.

Session Categories

Having a BI background, I am interested on Power BI and Azure sessions. I was happy to find a good number of sessions in these categories. The following shows the other categories that I am interested in. These are my own categories that I came up by parsing the session names. These are not official PASS categories so they may not correspond completely with what is on the official schedule.

.   

Session Levels

As expected, most sessions are level 200 followed by 300. I think this is a good distribution.

Power BI Sessions

There are 17 Power BI sessions that I wished I could attend all. Unfortunately, some are at the same time slot. At least there are 11 time slots.

PowerShell sessions

I am also very interested in PowerShell as I use it on a regular basis to manage servers. There are 3 PowerShell sessions.

Azure Sessions

An amazing selection of Azure sessions are available this year. It is going to be hard to choose from this list!

Power Platform/PowerApps Sessions

These 3 sessions all sound very interesting. Again, hard choice here.

Speaker Idol Sessions

Speaker Idol is a competition between community speakers for the coveted prize of a session at the next PASS Summit. Each session will have 4 or 5 speakers with different topics, each speaking for 5 to 10 minutes. You can learn a great deal from each speaker. Aside from learning cool short topics, these sessions are both entertaining and educational if you are interested in public speaking or even making presentations for work. See my blog post from 2016 when I competed.

Room Schedule

Let’s say you like a room so much that you decide to spend your entire day/conference in that room1. You can use the room tab in the PBI file to find out what kind of education you will receive if you stay put. This is only for fun!

Really high-level sessions (Level 400 and 500)

There are 13 high-level sessions available. Great sessions, potentially tough on the brain.

So many choices…so little time.

To get the best possible learning experience, you should have an idea of what sessions to attend before you get to the conference. In addition to the regular sessions, make sure to leave enough time to explore the community zone, exhibit hall, Microsoft clinics, and many more learning/networking opportunities that will be available.

A great way to keep up with all the events is to follow up @sqlpass on Twitter.