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

Statistics result 10-km run Corre por el niño 2015

#45 Statistics result 10-km run Corre por el niño 2015

UPDATE 8-12-2015: I added another Excel with a boxplot with the finishing-times per category (men/women). In par. Downloads below, I marked this Excel with 'V2'.

UPDATE 16-11-2015: I added another Excel with the ranking per category (men/women).

On Sun. 8 Nov. I participated in the 5th edition of the 10K run "Corre por el Niño 2015" (#CorrePorElNiño), this year with over 8000 participants (10km, 4km and 1 km for kids).  This run is organized by hospital Niño Jesús (in Madrid), to raise funds for investigation of severe diseases with childeren, like e.g. cancer. For more info about this road-race, see:

http://www.correporelnino.com/

http://correporelnino.wordpress.com

https://twitter.com/CorrePorElNino

https://www.facebook.com/Carrera-Popular-Hospital-Ni%C3%B1o-Jes%C3%BAs-212488895455880/

For  some of my photos of the race (and that of 2013), see:

https://picasaweb.google.com/103278159654062440102/CarreraDelNino2013Madrid

In this run the bruto (gun) time and netto (chip) time are measured, and the bruto-time is the oficial time.

I also participated in this run, and, like last year, I made an Excel with the statistics of the result, see fig. 1 for the result. For more info about this Excel with the 2015-results was made,which is almost the same as the Excel with the 2014 result, see my 2014 blog-post about this 10K run:

http://worktimesheet2014.blogspot.com/2014/11/statistics-result-10-km-run-corre-por.html

And here are 2 others sites with statistics and all finish-times of #CarreraPopular #CorrePorElNiño Madrid 2015:

http://observon.blogspot.com.es/2015/11/carrera-popular-corre-por-el-nino-2015.html

http://www.runedia.com/cursa/201526160/carrera-popular-corre-por-el-nino-10k/2015/

(this last site used the netto-time in their statistics, although the oficial finish-time is the bruto-time).

The data from this Excel comes (like last year) from this site:

http://www.cronococa.com/ResultadosRunning.aspx

I copied the data from their site to my Excel and added some check-columns (which are hidden), which showed some errors (red cells):
- in the man/women category field ("Sexo") their was one row with value "Masculina" (in stead of "Masculino")
- for some rows, the netto-time was higher then the netto-time
For the result of the 'import' of the time-date of the website into Excel, see fig.2.

This year there were no chips for the people who registered on the day of the run, so my name is not on the list (sheet 'Data'). My time was something between 47 and 48 min. (I made a short stop during the race to say hello to my sister-in-law and niece who were supporting the runners and I didn´t stop my stop-watch during this break), so as you can see in sheet 'Data', this time corresponds to about ranking 240 (of the 1642 10K runners) and percentile 17. That is in the 'All-category' (Men and Women). And to know my percentile in the Men-category, I copied the Excel and deleted in the Data-sheet all rows with finish-time of category Women (and X - Unknow). The array-formula to calculate the Frequency-colums used 'named ranges' so these were automatically (re-)calculated. The array-formula looks like this:

{=FRECUENCIA(R_Time2;R_Bin1)}

with:
- R_Time2 = range on Time-Bruto column on Data-sheet
- R_Bin1 = range on Time-column on Stats-sheet

And I also made an Excel for the Women-category, and a final one with all Freq.values (so for categories All, Man, Women) with a graph which shows the Percentile-ranks for these 3 datasets, see fig.3, in which you can see e.g that after 55 min. about 50% of the men had finished, and about 20% of the women. And in the Excel for the Men-category I saw my percentile was about 18%.

And in fig.4 you can see a boxplot with the finishing-times per category (Men/Women). This is a stacked barchart based on the differences of the '5 number summary' (min, Q1, Q2 (median), Q3, max.). To create the boxplot, in menu Desgin (of chart) I selected 'Switch rows and columns' and replaced the part of the stached chart for the Min. and Max for the 'whiskers' (implemented by error-bars in chart) of the boxplot. For a more detailed explanation, see:

https://youtu.be/ZFbPnwKwVWk

In the boxplot you can e.g. easily see that 75% of the men (Q3) finished before 50% of the women (Q2, median).

It´s a pity in this run they don´t have more categories using also 'dimension' age-group, e.g. Men-Junior (until 30), Men-Senior A (between 30 and 50), Men-Senior B(older then 50) etc., so you could compare your finishing-time better (with more or less equals (same gender and age-group).

And to conclude, I want to thank hospital Niño Jesús and all the volunteers for making this run a really nice experience and the isotonic drinks and bananas they gave us after the race almost make you forget  the suffering in the last part of the run with some 'nice' slopes...

fig.1: statistics result 10K run "Corre por el Niño 2015" 

fig.2: Imported Time-data

fig.3: Percentile-Rank per Category (Man/Women)

--

fig.4: Boxplot with finishing-times per Category (Man/Women)

Downloads:

#Mirror 1: Scribd.com (PDF file):

https://es.scribd.com/doc/290102349/Statistics-10K-run-CorrePorElNino-Madrid-2015-with-MS-Excel


#Mirror 2: Microsoft Onedrive (Excel files):
NB: this site has 'Excel-Online', so you can view my Excel-doc if you don´t have MS Excel on your PC


http://1drv.ms/1j6TMLY

http://1drv.ms/1S3qqKw


#Mirror 3: Google Drive (zip files with Excel and PDF files):


https://goo.gl/frAsZF

https://goo.gl/yTzcIu

V2:
https://goo.gl/XBXbFg

Month-calendar for November 2015 in Excel

#44 Month-calendar for November 2015 in Excel


In this post the new month-calendar for November 2015, see fig.1. The Excel also has the year-calendar for 2015 and a 2015-year-planner. Theme of this month is the Science Week, 'la Semana de la Ciencia', in Madrid (2/11-15/11), where universities etc. open their doors to the public, see:


http://www.madrimasd.org/semanaciencia/2015/


Last year one of the activites of the Science Week was a guided walk in a garden with Bonsai-trees, for some photos I made, see:

https://picasaweb.google.com/103278159654062440102/Bozai

And if your are interested in some science about data-visualization and the important role of Exel in this, as I have tried to show in several posts in this blog (with label: 'Data visualization'), see:

https://blogs.scientificamerican.com/sa-visual/the-science-of-visualization/

--fig.1: Nov. 2015 Calendar

Download:

https://goo.gl/pJdDcd

Month-calendar for October 2015 in Excel

#43 Month-calendar for October 2015 in Excel


In this post the new month-calendar for October 2015, see fig.1. The Excel also has the year-calendar for 2015 and a 2015-year-planner. Theme of this month is the Worldcup Rugby (until 31/10), see:


http://www.rugbyworldcup.com

The quote of this month, comparing soccer and rugby, I found on this poster:

https://www.facebook.com/veitchyonsport/photos/a.566695656700484.1073741828.203262656377121/692113720825343/?type=1&theater


It will be interesting to see how the teams who play against New Zealand will respond to their intimidating Maori war-dance, the famous 'Haka' (which until now worked well for them: in the Worldcup the 'All Blacks' currently are leading in their pool).  So this Worldcup no 'Waka Waka', but the 'Haka', for some nice videos, see :

https://youtu.be/GyZEUeAoFKE

https://www.youtube.com/watch?v=yiKFYTFJ_kw

https://youtu.be/bUZJyZldy10

fig.1 month-calendar Oct.2015

Downloads:

https://goo.gl/J6Uuzx 

Month-calendar for September 2015 in Excel

#42 Month-calendar for September 2015 in Excel

Note 10/9/2015: I updated this post, with a new Excel which includes a 2015/16-schoolyear-calendar, see fig.2 and dowload-url V2 below.

In this post the new month-calendar for September 2015, see fig.1. The Excel also has the year-calendar for 2015 and a 2015-year-planner. Theme of this month is (again) "La Vuelta Ciclista a España - Cycling Tour of Spain 2015", which is held from 20 Aug. to 13 Sept.,  for more info, see:

http://www.lavuelta.com/15pr/en/index.html

The reason to repeat the theme (la Vuelta was also theme of month in Aug.) was to write about a Dutch surprise in this edition of La Vuelta, Tom Dumoulin, who won yesterday in a magnificant race the 9th stage (in Valencia), and is now also leading in the general ranking.  For a video of (part of) the race, see e.g.:

https://youtu.be/Ko-Hix712Ck
https://youtu.be/zj3r4apKNhY

I hope to see Dumoulin finish here in Madrid 13 Sept in the red jersey ( the Spanish yellow jersey).

Fig.1: month-calendar

fig.2: schoolyear-calendar


Downloads:

V1:
https://drive.google.com/file/d/0BywxxSJoaUYxaWUxSVh4QUhBY1U/view?usp=sharing

V2 (zip with Excel and PDF-version):
https://goo.gl/XoCvaG

Dashboard for Messi´s worldrecord of 91 goals in one year with Excel + PowerView

#41 Dashboard for Messi´s worldrecord of 91 goals in one year with Excel + PowerView

4/1/2021:
Today I made a new live Messi Goal Tracker dashboard, see:

https://worktimesheet2014.blogspot.com/2021/01/messi-goal-tracker-dashboard-in-google.html

Note 9/12/2016: 
See for a new version (with also new charts) made with Power BI, my new post: 
http://worktimesheet2014.blogspot.com/2016/12/powerbi-dashboard-for-messis.html


Next week starts the Spanish soccer League and I thought that it would be a nice moment to make a Excel PowerView dashboard for the best player of Barcelona (and the world and of all times), Lionel (Leo) Messi, based on this dashboard I once saw:

http://tblsft.com/public/gallery/messi039s-86-goals

or:

http://canchallena.lanacion.com.ar/1535469-gerd-muller-rompio-el-silencio-espero-que-su-record-dure-otros-40-anos

This dashboard was made with BI-tool Tableau, at the moment that Messi broke Gerd Mueller's 40-year record "most goals scored by a single player in a calendar year", on 9/12/2012, which was until then 85 goals, see:

http://www.guinnessworldrecords.com/news/2012/12/barcelona-star-lionel-messi-sets-new-goalscoring-record-46285/

For my Excel-dashboard I used the Tableau data-set and added 5 records for the goals Messi scored in the rest of Dec. 2012, so Messi´s world-record is 91 goals (made with Barcelona and Argentina ('Seleccion'). Actually I made 2 versions of the dashboard:

v1: report has summary-data (e.g. total goals per competition) and detail-data (match-details like date, opponent-team etc) (see fig.1b)

v2: report only has summary-data, no detail-data (see fig.1a)

As always, the download-URL´s for these Excel files can be found at the bottom of the blog-post.

            fig.1a PowerView report-v2 in PDF (exported from Excel) with Messi´s world record

           fig.1b PowerView report-v1 in PDF (exported from Excel) with Messi´s world record




To make this dashboard in Exel I used PowerView,  a (free) add-in for Excel 2013 Pro Plus, a Business Intelligence (BI)-tool for ad-hoc reporting. I copied the (Spanish) data-set with all Messi´s goals in 2012  from the Tableau-doc. to a worksheet  in Excel (and I added English comments to the Spanish column-headers), see fig.2

     fig.2: data-table with all Messi´s goals in 2012 (in pic. filtered for 1 opponent-team)

 Then I added this data to the datamodel in PowerPivot, another Excel add-in to perform data analysis using e.g. DAX-formulas like this:

Suma de Goles:=SUM([Goles])

, see fig.3 (formula-bar).

      fig.3: datamodel in PowerPivot, with DAX-formula


For sorting the goals by month ('Mes') in 'time-order' (so not in alphabetic-order), I added in the datamodel a column 'MesId' (1= Jan., 2= Feb etc), and used this DAX-formulas:

mes: =FORMAT([Fecha2];"MMM")

mesdID =MONTH([Fecha2])

see fig.4

        fig.4: datamodel with calculated field for month and month-ID, for sorting in report.

Once created the data-model in Power Pivot, I  opened Power View and created the elements in the report, which are (from top to bottom, left to right):

Report-V1:

1. pie-chart: goals per score-mode (left-foot, penalty etc)
2. stacked bar-chart: goals per month and score-mode
3. tile (filter): goals per team
4. total goals, 'Suma de Goles', calculated field from the datamodel
5. total matches, calculated field from the datamodel with DAX-formula: Total Matches:=DISTINCTCOUNT([Fecha]) (fecha = 'date'). NB: field´s value is # matches in which Messi scored = 46, so not all matches which Messi played in 2012 = 69.
6. slicer (filter): competitions
7. pivot-table, goals per match (with match-details like date, score-mode etc)

So in report-v1 you can see the 3 ways in which you can filter data in PowerView: in the Filter-View pane (to the right of the report-canvas), with a Slicer or with Tiles.


Report-V2:
 as V1, except for bottom report, which contains a table in matrix-format and chart with goals per team and competition. NB: this matrix is like a pivot-table in Excel, see fig.5.

     fig.5: Pivot-table and chart : goals per team and competition

Creating the report-elements is just a matter of dragging and dropping the columns of the datamodel on the report-canvas and selecting the 'presentation-mode', e.g. Table or Chart (the matrix-table and chart in report-v2 are created in the same way (have the same fields), only the visualization-mode is different.
Note that the report-elements are connected: if you  use in report-v1 the Slicer to filter the competition 'Champions League' (CL), all report-elements only show the goal-totals of the CL-competition, see fig.6.

     fig.6: Slicer, Competition = 'Champions League' (CL)

Or if you filter in the chart with the goals per month = Dec., all report elements are filtered for Dec., see fig.7.

   fig.7: Filter in bar-chart Goals per month = Dec.


Or if you use the filter in the filter-pane and filter on an opponent team, e.g. "Rayo Vallecano", all report-elements only show goals Messi made against this team (Rayo), see fig.8.

     fig.8: Filter in filter-view pane, opponent-team = Rayo.

When I finished the report, I exported (published) it to a PDF-file (also included in downloads below).

So with this post I wanted to show you the reporting-possibilities of Excel and PowerView. For another example, see my post about the Sochi Winter Games:

http://worktimesheet2014.blogspot.com/2014/05/excel-2013-and-business-intelligence.html


Interesting Links
 
On my research for this post I found some interesting links, including videos with the 91 goals from Messi, from which you should see at least one of the first 2. Or if you don´t have so much time, watch only the 3d video, with maybe Messi´s best goal ever, which he made when he was only 19.

Videos Messi:

*1
Lionel Messi - All 91 Goals in 2012 with Commentary

*2
Los 91 Goles De Leo Messi En 2012

*3
Lionel Messi Goal vs Getafe

*4
https://youtu.be/8o_Lionel Messi - EL D10S - Short Movie |HD

*5
Lionel Messi - The Magician - 2015 ● Skills ,Goals ,Dribbles , Assists |HD


Infographics Messi´s 91 goals:

*1 positions field from where Messi scored
http://i.imgur.com/ewq4Y.jpg 
http://www.headingfortheexits.com/messi91/

*2
Messi vs Mueller

 *3
http://www.fcbarcelona.com/football/first-team/messi-91-goals-2012-world-record-infographic

 *4
 Statistics career Messi with Barcelona

*5
http://bleacherreport.com/articles/1456355-breaking-down-lionel-messis-magical-91-goal-year-for-barcelona-and-argentina


Business Intelligence with Excel and Powerview

*1
Getting Started With Power View In Excel 2013 

*2
Creating KPI dashboard using Excel 2013 PowerPivot

*3
From Data to Insight & Impact: Building a Sales Summary with Power View

*4
Excel 2013 Power BI Tools Part 17 - Filters in Power View


Downloads:

#Mirror 1: Scribd.com (PDF file report-V2):

https://es.scribd.com/doc/274861437/GoalsMessi2012-v2-R1-PDF

#Mirror 2: MS Onedrive (Excel file report-V2):
NB: this site has 'Excel-Online', so you can view my Excel-doc if you don´t have MS Excel on your PC

http://1drv.ms/1hiNhFP

#Mirror 3: Google Drive (1 zip file with Excel and PDF files report V1 and V2):
https://goo.gl/Ttkzmz

Month-calendar for August 2015 in Excel

#40 Month-calendar for August 2015 in Excel

In this post the new month-calendar for August 2015, see fig.1. The Excel also has the year-calendar for 2015 and a 2015-year-planner. Theme of this month is "La Vuelta Ciclista a España - Tour of Spain 2015", which starts 20 August, for more info, see:

http://www.lavuelta.com/15pr/en/index.html

This cycling Grand Tour also passed through the mountains Picos de Europa (Asturia), a very nice part of Spain where I spent several holidays, for some photos, see:

https://www.flickr.com/photos/46731978@N00/7573362742/in/photolist-c: eqYW-cxereq-cxerEL-cxestW-cxesTG-cxes2m-cxetdw-cxetNb-cxeu37-cxeuvE/

In my calendar I used a clipart from:
http://www.chungkong.nl/wp-content/uploads/2015/04/MY-VUELTA-A-ESPANA-MINIMAL-POSTER-2015-2-720px.jpg

-- fig.1

Downloads (Excel and PDF):

https://goo.gl/JZ1fte