Squares is a popular game when you get a big group of people to watch football. It’s a game of chance where all the money that goes in is redistributed to the attendees. If you’re unfamiliar with the game, you can find lots of websites like this one with the basic rules.
After people pick their squares, the paper ends up getting passed around all over the place as people wonder if they are in line to win some money. What happens if Team A kicks a field goal? Ooo I’ll win if Team B scores a touchdown before the end of the quarter!
This year I’m going to geek it up a bit (surprise surprise) and do this with Power Query. People will put their names into a grid inside Excel and then we’ll randomly assign 0-9 to each row and column. From there, the magic of Power Query takes over. Here are the basics of how the document is set up:
- The table where people enter their picks is loaded into the Power Query flow with the “From Table” operation.
- The current score is grabbed with “From Web” and looks at one of the many pages will report the current score. That score is then modded to contain only the final digit in the score for each team.
- Another query then takes those score digits and looks up the corresponding value in the table of picks. That resulting value is the name of the person who will win if the score doesn’t change.
- The current score table also contains some extra columns that calculate the last digit of the score if either team scores a field goal or a touchdown. I turned the query in step 3 into a function and then called that function with each possible scoring combination.
The end result looks like this. Simply hitting the Refresh button in Excel will refresh the green table showing who is current in line for some money and who might win if various things happen in the game.
