SQL Saturday Madrid 2015 statistics with PowerPivot and PowerQuery

#46: SQL Saturday Madrid 2015 statistics with PowerPivot and PowerQuery

On 21-11-2015, so tomorrow, Saturday, I go to this event: SQL Saturday Madrid 2015, #SQLSatMadrid, where several Microsoft SQL Server and Business Intelligence (BI) MVP´s give some talks, for more information see:

http://www.sqlsaturday.com/459/eventhome.aspx

and for the time-schedule:

http://www.sqlsaturday.com/459/sessions/schedule.aspx


As you can see, there is a total of 27 courses, in 7 time-blocks of 1 hour, in 4 rooms, and for each course the schedule shows this data:

• Course name
• MVP name
• Level (Beginner, Intermediate, Advanced)
• Theme (Big Data, BI, Cloud, DBA), indicated by cell-color
• Language (most are in Spanish but when the course-name is in English , I guess the course is in English)

To ´warm up´ for the sessions about PowerBI, I converted this schedule in a pivot-table and graph, using Power Pivot and Power Query, see fig.1 for the end-result.

 fig.1 Pivot table Courses SQLSat.



To make this Excel, first I copied the Schedule (PDF) to Excel (export to Excel in the tool Nitro PDF) and did some cleaning (delete rows with non-schedule data and 'undo' of cell-merges and delete empty columns), see file 'SQLSaturday459_Madrid2015_Schedule_20151120_v2.xlsx' in Downloads below.
And then I made a 2nd Excel with the statistics, and with PowerQuery I loaded the schedule data from Excel-1, see file 'SQLSaturday459_Madrid2015_Schedule_Statistics' in Downloads and fig.2-3 for some of the steps in the Power Query formula language 'M'. An example of an 'M-formula':

= Table.AddColumn(#"Replaced Value3", "Level_1", each Text.Contains([Value], "Beginner"))

which looks in Excel-1 if in a cell there is the word 'Beginner' (level of course) and if so, sets the value in the Level-1 column to True (and if not to False), for more details of this formula, see:

https://msdn.microsoft.com/en-us/library/mt253324.aspx

fig.2: Power Query import

 fig.3 Power Query formula to Unpivot all columns (Room 1-4') except 1st column ('Time')

And after the Power Query import I did some other things in Excel-2 like adding a Time-block column and Course-Level-column, hiding come columns etc.) to facilitate creating the final pivot-table.
BTW: The Course-Level column I made manually because this information in Excel-1 is not part of the cell (string) value, but color-coded. But probably this could also be automated (so the conversion from cell-color to a (string-)value, see e.g.:

http://excelribbon.tips.net/T010780_Colors_in_an_IF_Function.html

I keep it short now, tomorrow I have to get up early, the event starts at 08:30, and that for a Saturday..


Downloads:

https://goo.gl/xVUdoo