Excel: Using Power Query to Return All Words From a List of Letters Including Wildcards by David Hager

I happened to run across a web site that returns all of the words by using a set number of letters.

http://wordfinder.yourdictionary.com/unscramble/

All credit for the working of the query demonstrated here goes to the aforementioned website.

So, I copied the URL of the query and found a way to use it in Power Query. I needed a way to add the string to the query, so I created an Excel table (named Letters) where the string would originate from.

Then, I was able to create M code that used the concatenated query and returned the output to the worksheet.

See:

let

QSource = Excel.CurrentWorkbook(){[Name=”Letters”]}[Content],

QText = QSource{0}[#”What Letters Do You Have?”],

Webstring = “http://wordfinder.yourdictionary.com/unscramble/”&QText,

Source = Web.Page(Web.Contents(Webstring)),

#”Removed Bottom Rows” = Table.RemoveLastN(Source,1),

#”Expanded Data” = Table.ExpandTableColumn(#”Removed Bottom Rows”, “Data”, {“Word”, “Scrabble® Points”}, {“Word”, “Scrabble® Points”}),

#”Removed Columns” = Table.RemoveColumns(#”Expanded Data”,{“Caption”, “Source”, “ClassName”, “Id”}),

#”Changed Type” = Table.TransformColumnTypes(#”Removed Columns”,{{“Word”, type text}, {“Scrabble® Points”, Int64.Type}})

in

#”Changed Type”

Here is an example of what the worksheet looks like after running it.

I was having trouble refreshing the query, so I asked Excel MVP and Power Query guru Ken Puls http://www.excelguru.ca/ for some help. This is the event procedure he came up with to refresh the query from the ListObjects(“Letters”) table.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

On Error Resume Next

If Not Intersect(Target, ListObjects(“Letters”).DataBodyRange) Is Nothing Then

ListObjects(“IsWord”).QueryTable.Refresh BackgroundQuery:=False

End If

End Sub

Shortly after making this Power Query Scrabble Words app, I discovered a website where an Excel-based Scrabble game was available.

http://www.dustinormond.com/blog/vba-scrabble/

I started playing this game and connected the letters from that game to the Scrabble Words app with external links. In order to control the process, I used a formula to concatenate the linked letters (in I2:Q2) to make the string needed for the query.

=IF(I3=””,I2,I3)&IF(J3=””,J2,J3)&IF(K3=””,K2,K3)&IF(L3=””,L2,L3)&IF(M3=””,M2,M3)&IF(N3=””,N2,N3)&IF(O3=””,O2,O3)&IF(P3=””,P2,P3)&IF(Q3=””,Q2,Q3)

In the model I am sharing with you, there are no external links for obvious reasons. But, if there were blank tiles, then I could replace the blank with a letter (i.e. – L2 is blank and the letter in L3 would take its place. The only drawback is that the formula in G2 has to be recalculated for the Power Query query to refresh. This can be done by clicking in G2 and hitting Enter.

But, as I was starting work on this article, I went back to the source web site and discovered something I had overlooked. Wildcards (2 of them) can be used in the query! On that site, they use question marks as wildcard characters, but the query I made will only work with an underscore. So, in the following figure, I demonstrate how this is done.

Whether you use this for playing (cheating at) Scrabble or just to use the generated words in some other way, I think that you will find this technique to be very useful.

Unfortunately, I do not have permission from the website for file download, but I still hope that you find this information useful.