Monthly Archives: July 2017

Run Your Power Query M Code Procedures in #Excel Worksheet Cells by David Hager

 

The ability to reuse Power Query M procedures has been for the most part reserved for those capable of purchasing the full-blown Power BI package. Wouldn’t it be great for anyone owning Excel to benefit from a way to store and run M procedures? Well, I believe that you have come to the right place.

The inspiration behind this technique came from Chris Webb’s article for running M code from text files.

https://blog.crossjoin.co.uk/2014/02/04/loading-power-query-m-code-from-text-files/

In the comments section of that article, there was a discussion of the portability of the text files to other potential users. I then made the following comment: “You could obviously store the entire M code in worksheet cells, if you had to.” Well, nothing was done with this idea, and I had forgotten about it until now. I decided to use a named cell to hold the M code, as described in this article.

https://blog.crossjoin.co.uk/2014/07/22/working-with-excel-named-ranges-in-power-query/

Chris helped me to work through a few issues I had in making this. Thanks for your help, Chris!

Here is the M code that utilizes the M procedure stored in a named range Excel cell (MCode).

let

Source = Excel.CurrentWorkbook(){[Name=”MCode”]}[Content],

ChangeDataTypes = Table.TransformColumnTypes(Source,

{“Column1”, Text.Type}),

GetMCode = ChangeDataTypes{0}[Column1],

EvaluatedExpression = Expression.Evaluate(GetMCode, #shared)

In

EvaluatedExpression

I decided to use Matt Allington’s calendar table M code for testing (great M code, Matt!)

https://powerpivotpro.com/2015/02/create-a-custom-calendar-in-power-query/

When I copied the code from the article, I found that there was no way to paste the entire code into 1 cell, mainly because multiple lines of text are viewed by Excel as one line per cell. So, I ended up with 25 lines of code in cells A1:A25. Thankfully, the TEXTJOIN function provides a way to assemble those lines of code so that they are readable by Power Query. The formula =TEXTJOIN(” “,,A1:A25) returns a single string with spaces between the lines of code, which appears to be necessary for the code to run correctly from a single cell. In this case, the named range cell is called MCode (cell C1). So, the main code pulls in the single cell table and transforms it into text. That M code text was run using Expression.Evaluate to return the Power Query query calendar table. The named cell MCode can either contain the TEXTJOIN formula associated with the lines of code desired or you can Copy, Paste Special Values to make a string that can be stored. In the example file, cell C4 contains the code string for the main M procedure, which you can copy/paste into the Power Query Advanced Editor to run. The stored M procedures (in this case only one – in cell C5) can be either placed in the MCode cell or a formula can be used to return the desired procedure.

I have removed the calendar table query result, but you can reproduce it if you like. I do not plan to make a storehouse of M procedures from this (at least, not one I am willing to share😊), so feel free to use this technique as you desire. IMHO, the ability to potentially store and use 1000’s of M procedures in a portable way is exciting.

The example file can be downloaded here.

MCode_RunFromNamedCell

Advertisements

Storing and Running Power Query M Code from #Excel Worksheet Cells by David Hager

 

The ability to reuse Power Query M procedures has been for the most part reserved for those capable of purchasing the full-blown Power BI package. Wouldn’t it be great for anyone owning Excel to benefit from a way to store and run M procedures? Well, I believe that you have come to the right place.

The inspiration behind this technique came from Chris Webb’s article for running M code from text files.

https://blog.crossjoin.co.uk/2014/02/04/loading-power-query-m-code-from-text-files/

In the comments section of that article, there was a discussion of the portability of the text files to other potential users. I then made the following comment: “You could obviously store the entire M code in worksheet cells, if you had to.” Well, nothing was done with this idea, and I had forgotten about it until now. I decided to use a named cell to hold the M code, as described in this article.

https://blog.crossjoin.co.uk/2014/07/22/working-with-excel-named-ranges-in-power-query/

Chris helped me to work through a few issues I had in making this. Thanks for your help, Chris!

Here is the M code that utilizes the M procedure stored in a named range Excel cell (MCode).

let

Source = Excel.CurrentWorkbook(){[Name=”MCode”]}[Content],

ChangeDataTypes = Table.TransformColumnTypes(Source,

{“Column1”, Text.Type}),

GetMCode = ChangeDataTypes{0}[Column1],

EvaluatedExpression = Expression.Evaluate(GetMCode, #shared)

In

EvaluatedExpression

I decided to use Matt Allington’s calendar table M code for testing (great M code, Matt!)

https://powerpivotpro.com/2015/02/create-a-custom-calendar-in-power-query/

When I copied the code from the article, I found that there was no way to paste the entire code into 1 cell, mainly because multiple lines of text are viewed by Excel as one line per cell. So, I ended up with 25 lines of code in cells A1:A25. Thankfully, the TEXTJOIN function provides a way to assemble those lines of code so that they are readable by Power Query. The formula =TEXTJOIN(” “,,A1:A25) returns a single string with spaces between the lines of code, which appears to be necessary for the code to run correctly from a single cell. In this case, the named range cell is called MCode (cell C1). So, the main code pulls in the single cell table and transforms it into text. That M code text was run using Expression.Evaluate to return the Power Query query calendar table. The named cell MCode can either contain the TEXTJOIN formula associated with the lines of code desired or you can Copy, Paste Special Values to make a string that can be stored. In the example file, cell C4 contains the code string for the main M procedure, which you can copy/paste into the Power Query Advanced Editor to run. The stored M procedures (in this case only one – in cell C5) can be either placed in the MCode cell or a formula can be used to return the desired procedure.

I have removed the calendar table query result, but you can reproduce it if you like. I do not plan to make a storehouse of M procedures from this (at least, not one I am willing to share😊), so feel free to use this technique as you desire. IMHO, the ability to potentially store and use 1000’s of M procedures in a portable way is exciting.

The example file can be downloaded here.

MCode_RunFromNamedCell

Bible Verse In Any Language Using #Excel by David Hager

 

I recently demonstrated what became a popular Excel technique – looking up a Bible verse using Excel’s web functions. On LinkedIn, I received a comment from Raul stating that he did not understand English, only Spanish. So, I decided to add language translating to the model. I tried to find a (free) way to do this directly from an API, but I could not. I turned instead to the translation technique I had already published which use a VBA procedure. I combined both of the methods as demonstrated in the links shown below.

https://dhexcel1.wordpress.com/2017/06/03/creating-an-excel-translator-by-david-hager/

https://dhexcel1.wordpress.com/2017/07/03/lookup-a-bible-verse-using-excel-wo-vba-by-david-hager/

You can read both of these articles to see the details of how each was constructed. Meanwhile, the key formula in cell H5 is:

=Translate(mString,LangCode)&T(NOW())

where mString = IF(ISERROR(FIND(“/b>”,oString)),oString,MID(oString,FIND(“/b>”,oString)+3,255))

and oString = FILTERXML(WEBSERVICE(“http://labs.bible.org/api/?passage=”&TheBook&” “&TheChapter&”:”&TheVerse&”&type=xml”),”//text”)

and LangCode = INDEX(LanguageCodes,MATCH(Language,LanguageNames,0))

Use the dropdowns in H2:J2 to select verse and language.

BibleVerseMultLanguages1

Sometimes the query has to be run twice in order to work. I have not been able to solve this problem, so please run the query a 2nd time if the cell containing the verse is blank. Alternatively, if you click in the formula in H5 and press Enter, it should calculate as desired. The problem is likely due to the Excel web functions becoming confused during recalculation. I attempted to correct this by adding &T(NOW()) to the end of the formula in cell H5. It appeared to help, but I cannot guarantee it. You can also try pressing Ctrl-Alt-F9 for recalculation.

This should be useful to everyone worldwide.

You can download the file here.

BibleVerseMultLanguages

#Excel Short and Sweet Tip #26 (Showing an UserForm With a Worksheet UDF) by David Hager

 

When looking at the vagaries of a Worksheet UDF, it appeared to me that anything viewed by Excel as an object could be invoked by the UDF. So, I thought, what is a large Excel object that may not work with this methodology? After a while, I thought of an Userform. Surely, I cannot show an Userform in this way. But, I WAS WRONG!

Open a new workbook and go to the Visual Basic Editor (VBE) and add an Userform. Then, add a standard module and add this code to it.

Function UForm()

UserForm1.Show

End Function

Then enter this formula in cell A1.

=UForm()

And presto, the userform appears!

Obviously, any userform, including those of your elaborate design, can be shown in this way. To have this UDF run when a specific cell is recalculated (in this case, A2), you can use a formula like:

=A2&UForm()

as shown in the following figure:

xlSS026_1

Have fun with this!

#Excel: Getting the Sunrise and Sunrise Times From an Address by David Hager

A while back I worked for quite some time on returning the sunrise and sunset from an address using VBA. I was not successful, so I shelved that project. But, after publishing several popular articles using Excel’s web functions, I decided to revisit this project using those functions. As always, implementation of this technique requires the appropriate xml data sources and the corresponding return values. I could not locate a single xml source to do this, so I finally found 3 xml sources that, if combined, would theoretically provide me with the desired result. Here are the 3 API sources.

http://maps.googleapis.com/maps/api

http://api.timezonedb.com/ ‘Requires a free API key

http://new.earthtools.org/

The 1st API takes a detailed address and returns latitude and longitude for that location. The 2nd API takes latitude and longitude as input and returns a daylight savings time (dst) value and a GMT offset (gmtOffset) value. The 3rd API takes latitude, longitude, dst, gmtOffset and the current day and returns the sunrise and sunset times. For the demonstration, address is a defined name formula.

address =”4163 W. Sparrow St., Orange, Tx.”

However, a named cell could also be used to contain the address.

The two formulas needed to return latitude and longitude from googleapis.com are

latitude= FILTERXML(WEBSERVICE(“http://maps.googleapis.com/maps/api/geocode/xml?address=”&ENCODEURL(address)&”&sensor=false”),”//lat”)

longitude= FILTERXML(WEBSERVICE(“http://maps.googleapis.com/maps/api/geocode/xml?address=”&ENCODEURL(address)&”&sensor=false”),”//lng”)

Note that the Excel ENCODEURL function is needed to convert the address to a form compatible with the API.

The latitude and longitude coordinates are then used as parameters in the timezonedb.com API to return values for daylight savings time and GMT offset. An API key is required by the web site owner, but you can register and get a free one at https://timezonedb.com/register. The API key is stored in the defined name API_Key.

dst = FILTERXML(WEBSERVICE(“http://api.timezonedb.com/v2/get-time-zone?key=”&API_Key&”&format=xml&by=position&lat=”&latitude&”&lng=”&longitude&””),”//dst”)

gmtOffset = (FILTERXML(WEBSERVICE(“http://api.timezonedb.com/v2/get-time-zone?key=”&API_Key&”&format=xml&by=position&lat=”&latitude&”&lng=”&longitude&””),”//gmtOffset”)/3600)-1

Finally, all of the parameters from the previous 2 APIs plus the TODAY function are used in the earthtools.org API to reurn sunrise and sunset times for the specified location.

sunrise= TEXT(FILTERXML(WEBSERVICE(“http://new.earthtools.org/sun/”&latitude&”/”&longitude&”/”&DAY(TODAY())&”/”&MONTH(TODAY())&”/”&gmtOffset&”/”&dst&””),”//sunrise”),”hh:mm:ss”)

sunset= TEXT(FILTERXML(WEBSERVICE(“http://new.earthtools.org/sun/”&latitude&”/”&longitude&”/”&DAY(TODAY())&”/”&MONTH(TODAY())&”/”&gmtOffset&”/”&dst&””),”//sunset”),”hh:mm:ss”)

Note that the date parameters can be changed if the day to get results for is not today.

The results of the formulas used in this technique can be seen in this figure.

SunriseSunset1

The formulas have been converted to values in the model due to my inability to share my API key, but you add back in what you want to see with the formulas described in this article (and in defined names).

You can download the file here.

SunriseSunsetInfo

Getting the Latest Earthquake Alert Using the WEBSERVICE and FILTERXML Functions in #Excel by David Hager

 

The key to returning valuable information to Excel is to find sources of xml data. In this example, the xml data is supplied by

https://earthquake.usgs.gov

The goal is to make an alert message for the latest strong earhquake occurring worldwide. The following 3 formulas return the information needed for that message.

Location: =FILTERXML(WEBSERVICE(“https://earthquake.usgs.gov/fdsnws/event/1/query?format=xml&starttime=”&TEXT(TODAY(),”yyyy/mm/dd”)&”&endtime=”&TEXT(TODAY()+1,”yyyy/mm/dd”)&”&minmagnitude=5″),”//description/text”)

Magnitude: =FILTERXML(WEBSERVICE(“https://earthquake.usgs.gov/fdsnws/event/1/query?format=xml&starttime=”&TEXT(TODAY(),”yyyy/mm/dd”)&”&endtime=”&TEXT(TODAY()+1,”yyyy/mm/dd”)&”&minmagnitude=5″),”//mag/value”)

Time: =TEXT(FILTERXML(WEBSERVICE(“https://earthquake.usgs.gov/fdsnws/event/1/query?format=xml&starttime=”&TEXT(TODAY(),”yyyy/mm/dd”)&”&endtime=”&TEXT(TODAY()+1,”yyyy/mm/dd”)&”&minmagnitude=5″),”//time/value”), “yyyy/mm/dd h:m”)&” UTC”

The WEBSERVICE function brings in the xml content from the web site and the FILTERXML function finds the desired data based on the node used in the 2nd argument.

After these formulas return the desired data for the latest earhquake with a magnitude greater than 5.0, as shown in the figure

 Equake1

the TEXTJOIN function can be used to construct a readable alert message. The following array formula in cell B5 affords a formatted message from the cells A1:B3.

=TEXTJOIN(IF(COLUMN(A1:B3)=1,”: “,”; “),,A1:B3)

An important feature of this formula is the use of different delimiters based on the column where the information resides.

I hope that you find these techniques useful.

You can download the example file here.

earthquake_xml

 

#Excel Short and Sweet Tip #25 (Random Sound – Worksheet UDF) by David Hager

 

Once again, I am borrowing from an Excel technique from John Walkenbach, in this case playing a .wav file in Excel.

http://spreadsheetpage.com/index.php/tip/playing_sound_from_excel/

I have modified his code by coverting it to a function procedure with one argument for running a specified wav file. Copy into a module in the VBE.

Private Declare Function PlaySound Lib “winmm.dll” _

Alias “PlaySoundA” (ByVal lpszName As String, _

ByVal hModule As Long, ByVal dwFlags As Long) As Long

Const SND_SYNC = &H0

Const SND_ASYNC = &H1

Const SND_FILENAME = &H20000

Function PlayWAV(fName As String)

PlayWAV = “”

WAVFile = fName & “.wav”

WAVFile = ThisWorkbook.Path & “\” & WAVFile

Call PlaySound(WAVFile, 0&, SND_ASYNC Or SND_FILENAME)

End Function

This procedure assumes that the wav files to be played are in the same folder as the workbook.

In order to play a random sound, I made these 2 defined name formulas.

soundlist ={“chicken”,”horse”,”kitten”,”owl”,”cow”}

rand_sound =INDEX(soundlist,INT(RAND()*COUNTA(soundlist))+1)

So, the .wav files starting with, in this example, (chicken, horse, kitten, owl and cow) must exist for this function to work. You must personalize this array so that this technique will work with your own wav files.

Now, enter this function in a worksheet cell

=PlayWAV(rand_sound)

Each time that the worksheet is recalculated, a random sound will play. I hope that you will find this useful.