FIPS County Province$\_$State$25001$ Barnstable Massachusetts$25003$ Berkshire Massachusetts$25005$ Bristol Massachusetts$25007$ Dukes Massachusetts$25009$ Essex Massachusetts$25011$ Franklin Massachusetts$25013$ Hampden Massachusetts$25015$ Hampshire Massachusetts$25017$ Middlesex Massachusetts$25019$ Nantucket Massachusetts$25021$ Norfolk Massachusetts$25023$ Plymouth Massachusetts$25025$ Suffolk Massachusetts$25027$ Worcester Massachusetts$34001$ Atlantic New Jersey$34003$ Bergen New Jersey$34005$ Burlington New Jersey$34007$ Camden New Jersey$34009$ Cape May New Jersey$34011$ Cumberland New Jersey$34013$ Essex New Jersey$34015$ Gloucester New Jersey$34017$ Hudson New Jersey$34019$ Hunterdon New Jersey$34021$ Mercer New Jersey$34023$ Middlesex New Jersey$34025$ Monmouth New Jersey$34027$ Morris New Jersey$34029$ Ocean New Jersey$34031$ Passaic New Jersey$34033$ Salem New Jersey$34035$ Somerset New Jersey$34037$ Sussex New Jersey$34039$ Union New Jersey$34041$ Warren New Jersey$36001$ Albany New York$36003$ Allegany New York$36005$ Bronx New York$36007$ Broome New York$36009$ Cattaraugus New York$36011$ Cayuga New York$36013$ Chautauqua New York$36015$ Chemung New York$36017$ Chenango New York

$36019$ Clinton New York$36021$ Columbia New York$36023$ Cortland New York$36025$ Delaware New York$36027$ Dutchess New York$36029$ Erie New York$36031$ Essex New York$36033$ Franklin New York$36035$ Fulton New York$36037$ Genesee New York$36039$ Greene New York$36041$ Hamilton New York$36043$ Herkimer New York$36045$ Jefferson New York$36047$ Kings New York$36049$ Lewis New York$36051$ Livingston New York$36053$ Madison New York$36055$ Monroe New York$36057$ Montgomery New York$36059$ Nassau New York$36061$ New York New York$36063$ Niagara New York$36065$ Oneida New York$36067$ Onondaga New York$36069$ Ontario New York$36071$ Orange New York$36073$ Orleans New York$36075$ Oswego New York$36077$ Otsego New York$36079$ Putnam New York$36081$ Queens New York$36083$ Rensselaer New York$36085$ Richmond New York$36087$ Rockland New York$36089$ St$.$ Lawrence New York$36091$ Saratoga New York$36093$ Schenectady New York$36095$ Schoharie New York$36097$ Schuyler New York$36099$ Seneca New York$36101$ Steuben New York$36103$ Suffolk New York$36105$ Sullivan New York$36107$ Tioga New York

$36109$ Tompkins New York$36111$ Ulster New York$36113$ Warren New York$36115$ Washington New York$36117$ Wayne New York$36119$ Westchester New York$36121$ Wyoming New York$06123$ Yates New Y The workbook has $2$ worksheets:$1.$ FIPS: for each county the worksheet has the following information:$$ FIPS: a unique five$-$character string to identify each county in each state$$ State$$ County $2.$ Main: it has a wide table about the GDP of each county in each year. For example, thehighlighted cell shows the GDP of county $25001$ in year $2001$ Open V$2.$xlsx$.$

The workbook has $2$ worksheets:

$1.$ FIPS: for each county the worksheet has the following information:

$$ FIPS: a unique five$-$character string to identify each county in each state

$$ State

$$ County

$2.$ Main: it has a wide table about the GDP of each county in each year. For example, the highlighted cell shows the GDP of county $25001$ in year $2001.$

Figure $1$

Task $1$:

Convert data in worksheet $$Main$$ from its wide form to long form using Power Query. After conversion, the structure of the long table should be:

Figure $2$

During conversion, please make sure that $$FIPS$$ is converted as text$/$string $($such as $01001),$ not as number $($such as $1001).$ Change column names in the converted long table as in figure above.

Power Query export the long table to a new worksheet in your workbook. Rename this worksheet as $$Tasks$1\_2.$

Task $2$:

Open $$Tasks$1\_2.$ Right now, it has $3$ columns as shown in Figure $2.$ Add the following two columns:

State: use vlookup function to retrieve state names from worksheet $$FIPS$$ based on FIPS.

County: use vlookup function to retrieve county names from worksheet $$FIPS$$ based on FIPS.

Note that for each vlookup function, its fourth parameter needs to be $$False$$ as the match is an exact match. After task $2$ is finished, worksheet $$Tasks$1\_2$ has a structure as in Figure $3$:

Note that for each vlookup function, its fourth parameter needs to be $$False$$ as the match is an exactmatch. After task $2$ is finished, worksheet $$Tasks$1\_2$ has a structure as in Figure $3$:Year FIPS GDP State County$20012500111078518$ Massachusetts Barnstable$2001250035777869$ Massachusetts Berkshire$20012500520007712$ Massachusetts Bristol$2001250071198038$ Massachusetts Dukes$20012500931790091$ Massachusetts Essex$2001250112604606$ Massachusetts Frankli