Difficulty transforming data on PowerQuery

You can do this in Power Query.

For each Metro you will need to

-select the year column and unpivot other columns

-select the columns with year and month and merge them together

-in the resulting column you will need to Parse the date

-now add a column that is the name of the metro

you should end up with something that looks like (you can rename the columns as you wish)

-repeat those steps for every metro that you have

-append queries as new, selecting all of the metros (this will be your combined file)
-deselect enable load for all of the metro queries

close the power query editor and have fun building your visuals in Power BI.