BLOGS BY TOPIC▼
BLOGS BY AUTHOR▼
BLOGS BY YEAR▼
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:

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:

I've gone for THOSE.
When you enter this you can see how well you've done:

Two of the letters are right, but they're not in the right place.
Now choose to add another 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:

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:

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 which lets you type in any five-letter word.
Clicking on the + button just displays the next bookmark:

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?

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 this particular letter.
There is one table for each row of the Wordle grid:

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:

This gives the word you're trying to guess.
Each of the Alpha tables contains a copy of 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:

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:

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:

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:

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!