End-to-end analysis of participants race Cross de Invierno 2023 with Microsoft Fabric (and Power BI)

 #91 End-to-end analysis of participants race Cross de Invierno 2023 with Microsoft Fabric (and Power BI)

In this old post I wrote about the run Cross de Invierno 2023 in Madrid I which I participated, which included an analysis of the finish-times, in Excel.
In this new post I used the data of the this run again, but this time for an analysis of the participants of schools and athletic-clubs, and now I used Microsoft Fabric. 
Fabric is Microsoft's quite new SAAS data analytics platform (it went GA in Nov.2023). It's a suite of tools to facilitate end-to-end analysis of data, and includes Power BI, One Lake, Data Factory (Data Pipeline, Dataflow), Synapse Warehouse and Lakehouse, Spark Notebooks etc. Fabric is made for 'personas' as Data Engineer, Data Scientist, Business Analyst etc., for which there are different built-in workloads ('experiences').
For more info about Fabric, see e.g:

https://learn.microsoft.com/en-in/fabric/get-started/microsoft-fabric-overview

https://youtu.be/J4i5lcROJcs?si=ZPo-mgnmQx8eRPg2
NB: in min. 19:16 there's a good picture of the Fabric architecture.

Evolutionary History of Microsoft Fabric - Spreadsheets to Lakehouse

 

The Power BI report that I made has these race-participants stats:  

The results of the race are published in several PDF-files, and for my analysis, I used 2 files, with the results of all men and women, so all adults (categories senior and junior). I converted these PDF-files to Excel and uploaded them in my Onedrive. I then used a Data Pipeline with 'activity' Dataflow Gen2 (with PowerQuery) to load the data from the Excel-files into a (Synapse) Lakehouse:

A Lakehouse is a data asset of OneLake, the central storage place of all data loaded in Fabric. 
All data in OneLake is stored in the Delta Parquet format, an open data format that can be read by all engines in Fabric (e.g. Power BI, using Direct Lake mode), which looks like this :

https://parquetreader.com/ 

NB: 

-note that the files are in the OneLake-folder, the 'OneDrive for data', see:
https://youtu.be/4NbuG1JBq60?feature=shared

-for more info about Onelake, see:
https://radacad.com/what-is-onelake-in-microsoft-fabric-and-why-you-should-care

-for more info about Delta Parquet format, see:
https://data-mozart.com/parquet-file-format-everything-you-need-to-know/

I made a Notebook to query the tables that I created in Onelake (Lakehouse), using the pyspark Python-library:  

And another notebook with the sempy-Python library. with which a PowerBI semantic model can be queried, using DAX: 

I created in a Fabric Warehouse the view RunnersAll, to combine the data from the 2 tables MenAll and WomenAll, with this SQL (run in the LakeHouse's SQL-endpoint):

 

In Fabric there's a Visual Query Editor (PowerQuery) that can generate the SQL for you, here an example to get a list of all teams of both men and women runners: 

 

And the generated SQL:

Note that in this (generated) SQL "DISTINCT .. UNION ALL" is used, where I would have used UNION (which filters out the duplicates, no separate DISTINCT needed).
 

I made some other (dimension-)tables that I then added in this Power BI Semantic Model (star-schema):

And this model was the source for this Power BI report:

The table in this dashboard (PBIX-report) with the details of the runners is a long list to scroll through.
A better way to show (long) lists are paginated reports (RDL-report). There are 2 ways to make a paginated report: 
 

*1: in the Power BI Service, using the semantic model, and a query in DAX: 

*2: in Power BI Reportbuilder, using the SQL-endpoint of Fabric Lakehouse/Warehouse, and a query in SQL (report shows top 3 runners per category):

The query for the dataset in this report can be made in SQL or the Query Designer (that generates SQL):

NB: for a good post about PBIX-reports ('dashboards') vs RDL-reports ('classic' paginated reports), see:

https://sqlserverbi.blog/2022/02/04/when-to-use-paginated-reports-vs-interactive-power-bi-reports/  

To see if the schools that participated in the run were all coming from my neighbourhood (Ciudad de los Poetas) where the race was held (park Dehesa de la Villa),  I made this PBIX-report:

MS Fabric supports version-control in Git, see column Git-status 'Synced' : 

I worked on this project in Dec.2023 and Jan.2024 (with a free Fabric trial license), so shortly after 
MS Fabric went 'GA', although actually it was still a beta-version back then... But what is the status now, in Jun.2025, is MS Fabric 'production-ready' now? It looks it still isn't:

https://www.brentozar.com/archive/2025/05/fabric-is-just-plain-unreliable-and-microsofts-hiding-it/ 

https://www.linkedin.com/posts/gregdeckler_mshgqm-is-microsoft-hiding-fabrics-flaws-activity-7331365183698698243--9iE/

Microsoft Hates Greg - MS Fabric Performance & login issues

Microsoft Fabric: 10 Reasons It’s Still Not the Right Choice in 2025 

And is Microsoft's idea to bundle all their data-tools (as Power BI and Azure Synapse) in one suite (Fabric), a good idea? Some more critical opinions:

https://www.linkedin.com/pulse/microsoft-fabric-repeating-sins-past-greg-deckler-mrrae

Curbal - I can explain Microsoft Fabric with ONE WORD

I googled with "MS Fabric (customer) success stories 2025" and "MS Fabric market adoption 2025", and found e.g.: 

https://www.reddit.com/r/MicrosoftFabric/comments/1jz4dxz/microsoft_fabric_success_stories/ 

https://news.microsoft.com/en-au/features/melbourne-airport-is-driving-innovation-across-operations-with-microsoft-fabric-for-data-analytics-and-reporting/

https://www.microsoft.com/en/customers/story/23991-lumen-microsoft-fabric 

https://www.reddit.com/r/MicrosoftFabric/comments/1fne380/seeking_insights_on_microsoft_fabric_adoption/

https://powerbi.microsoft.com/en-us/blog/microsoft-named-a-leader-in-the-2024-gartner-magic-quadrant-for-analytics-and-bi-platforms/ 

MSHGMQ - Microsoft Fabric and the Uncanny Valley 

As they say in this post:

https://www.red-gate.com/simple-talk/databases/sql-server/bi-sql-server/microsoft-fabric-the-land-of-wizards-and-trolls/?utm_source=pocket_shared 

 "Only time will tell if Fabric stands the test of time against competitive technologies.[e.g. Databricks or Snowflake]." 

 

If you enjoyed this post and want to make a small donation, you can do this with the Paypal Donate button (at the top right of this post) or via BuyMeAcoffee .

Follow me on LinkedIn

Downloads

PowerBI Report Runners-count 

PowerBI report Schools of runners 

Paginated report Runners 

Excel-export Paginated report Runners 

Notebook pyspark

Notebook sempy

Results run Cross de Invierno A.D. Ciudad de los Poetas 2023 Madrid

 

#88 Results run Cross de Invierno A.D. Ciudad de los Poetas 2023 Madrid

 

Last Sunday 17/12/2023 was the yearly run 'Cross de Invierno' organized by A.D. Ciudad de los Poetas, in the nice park in my neighbourhood Dehesa de la Villa, Madrid.
For more information about this race, see e.g:

http://adcpoetas.blogspot.com/2023/12/xxxix-cross-de-invierno-clasifiaciones.html

https://youtu.be/WMnQjA7h1oE?si=8O8uilxO4RrOw32O

https://runedia.mundodeportivo.com/carrera/cross-de-invierno-ciudad-de-los-poetas-2023/20233350/ 

or my previous blog-posts about earlier editions of this run, e.g.:

https://worktimesheet2014.blogspot.com/2018/12/data-analysis-for-finishing-times-of.html

For this blog post I made an Excel with the finish-times of the category that I participated in (male, Veterans B), based on this doc/PDF:

https://drive.google.com/file/d/1hcRWlKO9PAoV70GVajWZ79Wn7_YwzCQx/view?usp=drive_link

NB: the table-headers in this doc are in Spanish and abbreviated, but in my Excel, in sheet 'Fields' you can find the English descriptions.

This is my Excel (in desktop version):

 

I've also uploaded this Excel to One Drive and generated the iframe HTML-code to embed this Excel (online version) in this blog (see the bottom of this post).
How-to: https://www.youtube.com/watch?v=uvA-U9FKgPw

In the right bottom histogram-chart you can see that my finish time (00:32:52) is somewhere in the middle, in bin '31:33-33:08' (rank #64 of the in total 113 Veterans B runners).

Steps to make this histogram:

*step 1: calculate finish-time in # seconds, see column O (sheet 'MenAll')

*step  2: for the finish-times (in sec.), generate the bins of the histogram with the 'Data Analysis' function.

How-to: https://www.upwork.com/resources/how-to-make-histogram-in-excel

*step  3: create the bin-labels to use in the histogram (see blue cells in pic above)

How-to:

https://youtu.be/QQGkrYzRbm8?si=t7FeHecvY4Hvyt-M

https://stackoverflow.com/questions/220672/convert-time-fields-to-strings-in-excel

 
*step  4: insert (clustered) column chart (select blue cells in pic above)

On the website of A.D. Poetas you can also find all the photos that were made during the race,
which are a lot (6000). But luckily it has the 'image-search' feature (as e.g. Google Photos), and so I could find easily this nice photo of the run of my kids in this album:

 

or of my race:
https://frutocfotos.barrel.cloud/en-en/album/photo/c731b368-9da4-41d5-b1ed-8bf57adc3734

Thanks A.D. Poetas for the perfect organization again and giving us a nice sporty Sunday morning.

If you enjoyed this post and want to make a small donation, you can do this with the Paypal Donate button (at the top right of this post) or via BuyMeAcoffee .

Downloads

#1: Excel on One Drive (best option, as both are Microsoft products)

#2: Excel on G-drive

 

 Embedded Excel

--

 

--

Google Sheets to plan and track a Strava challenge of running 100 km per month

 

 #85: Google Sheets to plan and track a Strava challenge of running 100 km per month

 
Visma Labs Spain, the company I work for, organized in December (2022) an event for our Strava Running Club: for every km ran/walked (and registered in Strava), the company would donate 0.5 euro to the Madrid Food Bank , with a max. of 1000 euro. So to get to this 1000 euro, we should run in total 2000 km. So if 20 people of our Strava Running Club would participate, that would mean 100 km per runner. So I set for myself this goal: run 100 km in Dec. I saw in Strava there was also this challenge "December Running Challenge, 100 km of running in one month", in which I also participated.
Soon in December, it was clear from the Strava weekly totals from our Strava Running Club, that we would not make the 2000 km in Dec., and it was decided to give us another month, January, for which I also set then a 100 km goal. In January we also did not reach the 2000 km, but in the last extension in  February, we reached the goal, so we got the 1000 euro for the Food Bank. Great work, fellow-runners!

For this 2x 100 km  challenge, I made a spreadsheet in Google Sheet 2 that has time series charts: one  for the planned cumulative distance (with approx. 3 km a day) and another with the real cumulative distance, so it would be easily visible if I was still on track. Here an example with the status of my 100 km challege on 15/1/2023:

NB:

-yellow line: planned cumulative distance

-red line: real cumulative distance, which is on 15/1/2023 under the red line, so I was on track.

-blue line: real distance per day

- the pie-chart shows % distance left (red) vs distance run (blue).
NB: % and km ran/left are same values as goal-distance = 100 km.
 
Here the sheet/tab in which I entered each day the km's of each run (in red):

 

The G-Sheets version for Jan.2023 has also data for a 2nd Strava-challenge in which I participated, which goal was not distance related (100 km in a month), but time-related (230 minutes of activity in 3 weeks). This data is stored in columns I,  J etc. and the related sheets/tabs have name '..goal2'

 

After completing of the 2x 100 km challenge, I made also another G-Sheets char to see the total km's of these 2 months:

This G-Sheets imports the data from the Dec.22 and Jan.23 G-Sheets by using function IMPORTRANGE
and combines the data of these 2 month (sheets) by using the function QUERY. For more info about these 2 functions, see:

https://support.google.com/a/users/answer/9308940?hl=en 

https://blog.coupler.io/combine-sheets-into-one/#Combine_sheets_into_one_using_QUERY_Google_Sheets

When I run the function Explore (that uses Machine Learning to help to get insights in the data), it gave me this result/answer:

So it says that my median distance was higher in Jan. than in Dec., which is correct, because in Dec. there were days when I did several runs on a day, each run of a short distance (which I did not do in Jan.).

 

To make the 100 km challenge of Jan.2023, I did one big run at then end of the month, 15 km, in  the city where I grew up, Zwijndrecht (Netherlands), which I also uploaded on this nice Google Maps mashup Wikiloc (a Spanish product that has now 11M members sharing routes):

https://www.wikiloc.com/running-trails/rondje-zwijndrecht-15k-run-125423358

And 2 other specials runs where these 2 races (see also references R1,2):

*1
XXXVIII Cross de invierno A.D. Ciudad de los Poetas 2022, Parque Dehesa de la Villa, Madrid, 6K run in a park close to where I live:

https://www.strava.com/activities/8261806442

*2
San Silvestre Vallecana 10k 2022 31 Dec. Madrid, 10K run, the best christmas-gift from my work (who paid the registration-fee and also for the children of employees who wanted to run the
San Silvestre Mini):

https://www.strava.com/activities/8318453460

This race is not just for fun, it also supports foundations that fight agains childhood cancer (Unoentrecienmil) and childhood obesity (Gasol Foundation):

https://www.sansilvestrevallecana.com/dorsal_solidario_en.php

Thanks to this post on Wikipedia I learned that this 'last race of the year' tradition is not just something in Madrid, but also in other countries as Brazil (where it has its roots), Portugal and Italy.
And in this article of Runnersworld I saw that also in the Netherlands (Soest) there is a Sylvester by Night run/cross.

The goal of running 100 km a month was challenging, but knowing it was a good cause (the food-bank), I was determined to complete it. As I heard Jordan Peterson saying in this talk How to Set Goals the Smart Way (min.9:35): 

"He who has a why can bare almost any how", a line from Nietzsche. I checked for the original phrase (in German) which has a nice addition:

"Hat man sein warum? des Lebens, so verträgt man sich fast mit jedem wie? - Der Mensch strebt nicht nach Glück; nur der Engländer tut das."

https://beruhmte-zitate.de/zitate/123955-friedrich-nietzsche-hat-man-sein-warum-des-lebens-so-vertragt-man-si/

 

If my spreadsheet is usefull for you, it would be nice if you could share how you used it in a (non-anonymous *) comment below.
* I ask for non-anonymous comments because when I allowed also anonymous comments, I got a lot of spam.

 

                                                            source pic: https://rb.gy/mqqxl2

Credits

The runner-icon that I used in my G-Sheets is from:
https://www.flaticon.com/free-icon/running_233064?related_id=233064&origin=search

 

Downloads

NB:

I made the G-Sheets on the G-Drive of my work (to share it with other collegue-runners), but unfortunatley it was not possible to copy the file to my personal G-Drive in the original format (.gsheet). When downloading it, it was converted to Excel, and when I then uploaded to my personal G-Drive and saved it back to G-Sheets format, some things were not exactly the same as in the original G-Sheets (e.g. pivot-charts).

G-Sheet Dec.2022

G-Sheet Jan.2023

NB: see also "Embeded G-Sheets" below, and for more info on how to publish a G-Sheets (generated the embedded code/i-frame), see:
https://www.youtube.com/watch?v=cHXpCaZA7Bw

References

[R1]

Race Dehesa de la Villa:

http://adcpoetas.blogspot.com/p/xxxi-cross.html

https://www.flickr.com/photos/adcpoetas/52571583846/in/album-72177720304540380/

https://www.flickr.com/photos/adcpoetas/52580444869/in/album-72177720304677763/ 

https://sportmaniacs.com/es/races/xxxviii-cross-ciudad-de-los-poetas-2022/639f02a1-36bc-4cca-8f89-7c95ac1f25e6/results/athlete/199/results

 

[R2] 

Race San Silvestre

https://www.sansilvestrevallecana.com/popular_en.php

https://www.marca.com/atletismo/san-silvestre-vallecana/resultados/carrera-popular.html?utm_source=pocket_saves 

https://www.facebook.com/sansilvestrevallecana/videos/565301878370053/      

https://www.facebook.com/100064108532446/videos/3079732918986087/?__so__=permalink              

https://www.flickr.com/photos/158376798@N03/52601367997/in/album-72177720304924953/

https://www.flickr.com/photos/158376798@N03/52602372703/in/album-72177720304924953/

https://www.marca.com/atletismo/san-silvestre-vallecana/2022/12/28/63ac03d1268e3e39138b45ad.html 

https://www.sansilvestrevallecana.com/diploma22/imprimir.php?id=0caca9a1-fa2a-5c46-a0ed-32f8aa3987e9

[R3]
https://www.chasetheladder.com/

The free version of Strava keeps the total km-run per week, so on Monday you start at 0 again.
But this nice Strava add-on solves that, it adds to each activity (run) some stats as 4-week summary.

[R4]

SMART goal setting

https://www.mindtools.com/a4wo118/smart-goals

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

[R5]

Running 100 km a month

https://www.asinglestep.co.uk/resources-and-inspiration/running-100km-in-30-days/

https://medium.com/@ikemoobioha/6-life-lessons-i-learned-from-running-100k-in-a-month-29e75a900aa8

 

Embedded G-Sheets

Results 6K run Dehesa de la Villa 2019, Men Veteran B

#64 Results 6K run/cross Dehesa de la Villa 2019, category Men Veteran B

Last week (15/12/2019) I participated in the running race "XXXVII (37th edition) Cross de Invierno Ciudad de los Poetas - Memorial Javier Martínez Morales", a 6 km race in the beautiful park Dehesa de la Villa in Madrid, organised by the local running club Agrupación Deportiva Ciudad de los Poetas (A.D. Ciudad de los Poetas).

For map-details, see: https://runedia.mundodeportivo.com/carrera/cross-de-invierno-ciudad-de-los-poetas-2019/20193350/

For the analysis of the results with Excel, I used the same method as I did last year, see for detailed description:
https://worktimesheet2014.blogspot.com/2018/12/data-analysis-for-finishing-times-of.html
NB: for download-details of the Excel, see end of blog. I also uploaded it to Excel Online (on Microsoft Onedrive) so you can also look into it even if you don't have Excel installed.

Besides Excel, I used this time also Flourish for data-vizualization, see e.g. fig.1 for an example of a chart made with this tool. The Vega-Lite charts that Flourish uses have some nice features, e.g:
- online editor Flourish Studio, to which you can upload an Excel with your data
- charts are described in JSON (very compact), see fig.2a-b
-  animation-effect when a chart is loaded
- you can create a 'story' to group charts which can be embedded in a web-page and 'played' (aka Powerpoint) , as the one I made and you can see below fig.1.

fig.1:  Flourish / Vega Lite Boxplot-chart finish-times byclub

..

My Flourish story (click on below arrow to go through 4 charts of this story):

The Flourish/Vega Lite charts are also responsive, so they adapt to the resolution of the device (laptop, smartphone, tablet), see fig.2c


fig.2a: Flourish Studio

https://app.flourish.studio/visualisation/1132053

fig.2b: Flourish Studio

fig.2c: Flourish responsive charts

I also used Excel to analyze the data and my finish-time (30:42 min., so 1842 sec, rank #83) using the Data Analysis menu with options "Descriptive Statistics' and "Rank and Percentile", see fig.3-4 for the result. And in Excel 2016 it is also quite easy to make Histogram- and Boxplot charts, see fig.5-6.


fig.3: Excel 'Data Analysis'-menu,  option "Descriptive Statistics'

fig.4: Exel 'Data Analysis'- menu,  option "Rank and Percentile"

fig.5: Excel Histogram-chart Finish-times (in sec)

fig.6: Excel Boxplot-chart Finish-times (in sec)

And to conclude I wanted to to thank AD Ciudad de los Poetas and all its volunteers for organizing this race, and also for giving all runners a nice memory of the race with all the photos they made and uploaded to Flickr, like this one of me, in white shirt, which I got with another race , of hospital Nino Jesus, and which I also made an analysis for using Power BI as dataviz.tool, see:
http://worktimesheet2014.blogspot.com/2017/11/statistics-result-10-km-run-corre-por.html
What I liked about Flourish is that it is 100%cloud and there is a free edition. When I looked for the opinion of others on Flourish, I found this interesting article in which various dataviz-tools are demonstrated, among which Flouris (and Power BI, Excel and more):

http://www.storytellingwithdata.com/blog/2019/1/24/new-year-new-tools




source: 
https://www.flickr.com/photos/adcpoetas/49241485423/in/album-72157712270066027/


Downloads:

#Mirror 1: Google Drive

https://cutt.ly/freIKwU


#Mirror 2: Microsoft OneDrive

https://cutt.ly/oreI1cX

--