The srn_tmp.txt file is a text file that contains monthly temperature from 1895 through January 2005. The srn_data.txt is a “data dictionary” that explains the temperature file. (The data dictionary mentions two other files, srn_pcp.txt and srn_pdsi.txt. These files have been provided, but they aren’t used here.) Proceed as follows:

a. Use Power Query to import the temperature data into an Excel table (not a Data Model). Use the Query Editor for only one purpose: to change the data type for column 1 to text. (You can do this with the Data Type dropdown on the Transform ribbon.)

b. In Excel, change the column headings for columns 2 to 13 to month names: Jan, Feb, etc. Also, delete the last column, which is empty.

c. Create two new columns to the right of column 1: Code and Year. Fill the Code column with the first three digits of the column 1 value, using Excel’s LEFT function, and fill the Year column with the last four digits of the column 1 value, using Excel’s RIGHT function. Then copy the Code and Year columns, paste them over themselves as values, and delete column 1, which is no longer necessary.

d. Scan down to any year 2005 row. Only January’s temperature is listed. The rest are missing, denoted by the code -99.9. Perform a search and replace for -99.9, replacing each with a blank.

e. The codes in the Code column are 001 to 048 for the contiguous 48 states. The other codes, above 100, are for regions. Delete all rows with these region codes.

f. The State Codes.xlsx file contains a table of codes and the corresponding states. Copy its data somewhere to the right of the imported temperature data. Then insert a new column, State, to the right of the temperature data Code column and use a VLOOKUP function to fill it with the state names.

g. Add three new columns to the right of the Year column: Low, High, and Average. Low and High should be the minimum and maximum monthly temperature for that year, respectively, and Average should be the average over all 12 months of that year.

h. Create a pivot table and pivot chart, based on the temperature data, where you can see a line chart of the Low, High, and Average values across years for any selected state. Filter out the year 2005 and use a slicer for the State filter.