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.

pq_scrabble1

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.

pq_scrabble2

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.

 

Advertisements

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

  1. Pingback: #Excel Super Links #150 – Special Edition | Excel For You

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s