Open V$4.$xlsx$.$

The workbook has $3$ worksheets:

States: for each state the worksheet has the following

information:

State ID: a unique five$-$character string to identify each

state

State Name

Industries: it stores four industries with their industry

classification codes $($unique$)$ and names.

Main: it has a wide table about the GDP of each industry in

each state in each year. For example, in figure below the

highlighted cell shows the GDP made by state $01000$ in

industry $31-33($Manufacturing$).$

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 "StateID" is converted

as text$/$string $($such as $01000),$ not as number $($such as $1000).$

Change column names in the converted long table as in Figure $2.$

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 four columns as shown in

Figure $2.$ Add the following two columns:

StateName: use vlookup function to retrieve state names from

worksheet "States" based on StateID.

IndustryName: use vlookup function to retrieve industry names

from worksheet "Industries" based on "IndustryClassification".

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$:

Figure $3$