Category Archives: M Code

Get Latest Storm Information in #Excel Using Only An Address and Storm Name

 

I recently published this article about the direction and distance of a tropical system from an address.

https://dhexcel1.wordpress.com/2017/09/12/using-excel-to-find-how-far-the-storm-is-from-your-location/

Go back and read this article to understand the first part of the model associated with the address (entered manually in cell B1).

Originally I wanted to include the abilty to add the storm coordinates from an internet source but I could not find one, so manually entering them was necessary. However, later I stumbled across just the xml source I was looking for from the National Hurricane Center site at NOAA. It only gives the current information on a storm, so it gets “stepped on” with each new advisory. This information is for storm number 15 for the 2017 Altantic hurricane season, which hhappens to be Maria.

http://www.nhc.noaa.gov/storm_graphics/AT15/atcf-al152017.xml

In order to access more than one system, I replaced the number with a defined name function called StormNum, which is then used as the URL in the WEBSERVICE function to return the desired xml document.

=WEBSERVICE(“http://www.nhc.noaa.gov/storm_graphics/AT”&StormNum&”/atcf-al”&StormNum&”2017.xml”)

I also needed a list of the storms for 2017. One complication was that the NHC now gives Potential Tropical Cyclones a number like those given to all tropical lows. Then, if the PTC does not develop into a low pressure system, it will not show up in online lists, like for example, Wikipedia. However, I was able to find a site that did include PTCs and I used Power Query to get the table containing the desired information (see worksheet SysNames). I massaged the column containing the storm designations as shown in column G. The formula used for this is =IF(PROPER(TRIM(RIGHT(SUBSTITUTE(A3,” “,REPT(” “,100)),100)))=””,”Not Yet”,PROPER(TRIM(RIGHT(SUBSTITUTE(A3,” “,REPT(” “,100)),100)))). Shown below:

 StormUpdate2

The source from the PQ M code is:

=Web.Page(Web.Contents(“http://www.theweatherguys.com/index.php?config=&forecast=tropsystems&alt=tropallsystems”))

Now that I had the list of storms, I was able to construct the StormNum defined name formula as follows:

=IF(Storm_Number<10,”0″&Storm_Number,Storm_Number)

where Storm_Number =MATCH(DirectionCalc!$B$2,Storm_List,0). Cell B2 contains a data validation list with the storm names derived from the PQ.

Now, the FILTERXML function can extract various pieces of information from the xml doucment defined as Storm, as shown in the following figure.

 StormUpdate1

For example, the storm latitude (in cell C4) is =FILTERXML(Storm,”//centerLocLatitude”).

The final formula for the storm message is:

=ROUND(Distance,0)&” miles “&TextDirection&” of “&Address&”, moving “&MID(FILTERXML(Storm,”//systemDirectionOfMotion”),1,FIND(“OR”,FILTERXML(Storm,”//systemDirectionOfMotion”))-1)&”at “&FILTERXML(Storm,”//systemSpeedMph”)&” mph.”

Remember, do no use this model in any corporate or commercial manner (only for personal use).

You can download the file here.

DirectionFromStorm_AutoLookup

Advertisements

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

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