Where I work uses the Fire Weather Forecast product from the National Weather Service to produce a product for fire management officers that has the fire weather specific to their area. We have been doing this in Google Sheets, but we are moving away from all things Google and need to have this done in Excel.
Currently, the product includes data from RAWS (remote area weather stations), excerpts from the text Fire Weather Forecast from NWS, and Weather Alerts. This is exported as a PDF and sent twice a day, but it is also available on our web site on demand.
The Fire Weather Forecast, like nearly all NWS text products, is a PHP generated pre-formatted section of a web page. Example: https://forecast.weather.gov/product.php?site=NWS&issuedby=CTP&product=LWX&format=txt&version=1&glossary=0
Since I do not know enough about PHP and WordPress on GoDaddy (where our site is hosted), I am trying to get this to work in Excel, then embed it in the web page, hoping that the embedded version will actually update data links.
Right now, Power Query runs something like the following:
let
Source = Web.Page(Web.Contents("https://forecast.weather.gov/product.php?site=NWS&issuedby=LWX&product=FWF&format=txt&version=1&glossary=0")),
Data = Source{0}[Data],
Children = Data{0}[Children],
Children1 = Children{1}[Children],
Children2 = Children1{12}[Children],
Children3 = Children2{7}[Children],
#"Replaced Value" = Table.ReplaceValue(Children3,"PAZ064","$$",Replacer.ReplaceText,{"Text"}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Replaced Value", "Text", Splitter.SplitTextByDelimiter("$$", QuoteStyle.None), {"Text.1", "Text.2", "Text.3", "Text.4", "Text.5", "Text.6", "Text.7", "Text.8", "Text.9", "Text.10", "Text.11", "Text.12", "Text.13", "Text.14", "Text.15", "Text.16", "Text.17", "Text.18", "Text.19", "Text.20", "Text.21", "Text.22", "Text.23", "Text.24", "Text.25", "Text.26", "Text.27", "Text.28", "Text.29", "Text.30", "Text.31", "Text.32", "Text.33", "Text.34", "Text.35", "Text.36", "Text.37", "Text.38", "Text.39", "Text.40", "Text.41", "Text.42", "Text.43", "Text.44", "Text.45", "Text.46", "Text.47", "Text.48", "Text.49", "Text.50", "Text.51", "Text.52", "Text.53", "Text.54", "Text.55", "Text.56", "Text.57", "Text.58", "Text.59", "Text.60", "Text.61", "Text.62", "Text.63", "Text.64", "Text.65", "Text.66", "Text.67", "Text.68", "Text.69", "Text.70", "Text.71", "Text.72", "Text.73", "Text.74", "Text.75", "Text.76", "Text.77", "Text.78", "Text.79", "Text.80", "Text.81", "Text.82", "Text.83", "Text.84", "Text.85", "Text.86", "Text.87", "Text.88", "Text.89", "Text.90", "Text.91", "Text.92", "Text.93", "Text.94", "Text.95", "Text.96", "Text.97", "Text.98", "Text.99", "Text.100"}),
#"Transposed Table" = Table.Transpose(#"Split Column by Delimiter"),
#"Removed Blank Rows" = Table.SelectRows(#"Transposed Table", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
#"Transposed Table1" = Table.Transpose(#"Removed Blank Rows"),
#"Removed Columns" = Table.SelectColumns(#"Transposed Table1",{"Column2", "Column12", "Column19", "Column37"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Column2", "Discussion"}, {"Column12", "C & SE Montgomery, MD"}, {"Column19", "NW Prince Willam, VA"}, {"Column37", "W Mineral"}})
in
#"Renamed Columns"
(My apologies for not including comments in the code above, but I'm unsure if commenting is possible.)
This imports to a table just fine, but the line breaks from the source document are eliminated, so web page data of
755
FNUS51 KLWX 191438
FWFLWX
Fire Weather Planning Forecast for W and CTL MD...E WV...N VA and DC
National Weather Service Baltimore MD/Washington DC
937 AM EST Thu Dec 19 2024
looks like
755FNUS51 KLWX 191438FWFLWXFire Weather Planning Forecast for W and CTL MD...E WV...N VA and DCNational Weather Service Baltimore MD/Washington DC937 AM EST Thu Dec 19 2024
Complications:
- This needs to be embedded in a web page, so I cannot use macros. I'm not even certain refreshing connections is doable yet in the web interface.
- I don't know enough PHP or understand how PHP interacts with WordPress to create an appropriate plugin. (I'm halfway decent with basic HTML and CSS and extremely rudimentary JavaScript, so I could get there if I had time to learn it.)
- The overall organizational structure is too convoluted to just share the workbook with everyone.
