WiseOwl Training - Established 1992 Wise Owl Training

Established May 1992
30 years in business
Wise Owl Training
30 years in business
See 482 reviews for our classroom and online training
A Wordle version of Power BI
Can you play Wordle in Power BI? Of course you can! This blog explains how you can get Power BI to do something which it is eminently unsuited to do!

Posted by Andy Brown on 28 January 2022

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

A Wordle version of Power BI

Fancy playing the cult word game Wordle?  You can now do this in Power BI, thanks to Sam's impressive report-writing skills:

Wordle in Power BI

Playing Wordle in Power BI ...

The amazing thing about this game is that it's possible at all!  Kudos to Sam for having the patience and expertise to work out how to do it.  You can download Sam's Power BI report to see how he did it.

How to play

Start by typing in your first word:

First word

I've gone for THOSE.

 

When you enter this you can see how well you've done:

Two correct letters

Two of the letters are right, but they're not in the right place.

Now choose to add another guess:

Adding a guess

You have to click on this button to add another guess.

 

Keep guessing until either you run out of guesses, or you guess the word:

Second guess

Yeah! The O is in the right position.

How it works - introduction

As so often with Power BI, the way to create dynamic effects like this is using bookmarks:

Five bookmarks

These 5 bookmarks correspond to the five rows of the Wordle game.  Initially only the first one is visible.

 

The guess is controlled by a custom visual called Text Filter:

The Text Filter visual

The Text Filter visual which lets you type in any five-letter word.

 

Clicking on the + button just displays the next bookmark:

Plus button

There's an action assigned to each button like this, which displays the next bookmark row.

 

Getting the yellow/green letters to appear

When you type in a word, how does the letter appear like this?

Yellow and green letters

Assuming you type the word FOUNT as the second guess, why does (for example) the O appear, and go green?

The letter O shown is in a card which is based on a measure called Alpha 2 filter from the Alpha2 table:

The card for a letter

The card for this particular letter.

 

There is one table for each row of the Wordle grid:

Alpha2 table

All of the tables with prefix Alpha contain the same fields and measures.

 

Here's the measure for the field shown above:

Alpha 2 filter =

CALCULATE(

SELECTEDVALUE(Alpha2[Alphabet],"_")

,Alpha2[Alphabet] = Mid(SELECTEDVALUE('Word Options'[Word Selection]),2,1)

)

When you refresh or open the report, it picks a new random number which determines which word is selected:

The current word

This gives the word you're trying to guess.

Each of the Alpha tables contains a copy of the alphabet:

The alphabet

There is one version of the alphabet for each row of the Wordle grid.

 

The measure:

  • restricts the filter context to those letters which equal the second letter of the word you're trying to guess; then
  • selects the letter for the current guess which matches this (if there isn't one, the measure returns an underscore)

You'll need to be fairly accomplished at DAX to make sense of this and some of the other measures coming up below!

The formatting of the letter is performed by using conditional formatting:

Formatting of the measure

Each card's background colour is set to the value of the relevant measure.

 

The Alpha 2 Measure, for example, looks like this:

Alpha 2 Measure =

SWITCH(

TRUE()

,[Alpha 2 filter] = Mid('Word Table'[Random word] ,2,1),

"Green",

[Alpha 2 filter] IN {

Mid('Word Table'[Random word] ,1,1)

,Mid('Word Table'[Random word] ,2,1)

,Mid('Word Table'[Random word] ,3,1)

,Mid('Word Table'[Random word] ,4,1)

,Mid('Word Table'[Random word] ,5,1)

},"Yellow",

"White"

)

That is: if the value of this card's letter equals the second letter of the word typed in, colour it green; if it equals any of the letters of the word, colour it yellow; otherwise, colour it white.

Changing the word

When you open the Power BI report, there's a table called Random number which contains a single field called Rand:

The random word

This contains the row number of the word table denoting the current word to be guessed.

 

If you go into Query Editor to view the source for this table, you'll see this:

Random number source

The table gets its data from a random number between 1 and the number of words in the words table.

Subsequent steps then convert this into a table:

Query filter steps

The next step, for example, converts this number into a table.

 

Conclusion

Two things strike me as I read through this: getting it to work as well as it does was a major achievement, but Power BI is not ideally suited for playing Wordle! 

This blog has 0 threads Add post