November 2024 competition - transform footie data in Power BI

This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

November 2024 competition - transform footie data in Power BI
Think you're good at Power BI Desktop data manipulation? See if you can present our Excel football results as a final table (showing games played, goals scored/conceded and points won) to have a chance of winning this month's £50 prize.

Posted by Andy Brown on 01 November 2024

The 2024 WOOF (Wise Owl Official Football - thank you for asking) league has finished, and you've been sent the results in this Excel spreadsheet:

So for example Squelch City lost to Owl Academicals 0-3 as shown in cell G5 (and also in D8 which shows the same results from Owl Academicals' pont of view).

If an over-zealous virus manager prevents you from downloading this Excel workbook let us know, and we will email a copy to you.

Your mission is to show this in a table looking something like this in a Power BI report (this isn't a formatting competition, so it doesn't matter what this looks like as long as the data is correct):

Python Athletic are champions!

This table should be based on data looking something like this:

Note that you may choose to create intermediary queries. Remember that you can right-click on these to prevent them from loading so that they don't clutter up the above Data view.

There are a few rules you must follow however!

Rule	Notes
Power BI transforms only	You have to load the data directly from the provided Excel spreadsheet in Power BI and use Query Editor to do all of the necessary data manipulation to get at the columns shown in the table above.
No DAX	You can't use any calculated columns or measures - everything must be done in Query Editor.
No M	You can't create any custom columns using M formulae - everything must be done using the standard transforms available in the Query Editor ribbons.

I found it useful to begin with an Unpivot transform, and also needed a Pivot transform at one stage, but the fun of setting competitions like this is that people find all sorts of different ways to solve the same problem!

If you get this working, please send proof to the usual competition email address:

The email address to use.

Your proof can be a complete Power BI report or just an explanation of the query steps you used. The first correct entry drawn out of the (now slightly worn) Wise Owl Sorting Hat will win a £50 voucher and the fame concomitant with a mention in the next issue of our newsletter.

This blog has 0 threads

Add a new post