Transforming data in quarterly defined columns

Hello all,

This is a long ask so I am hoping someone can give me a few minutes of their time to work this through with me:

I am tasked with building an assurance map for our organisation using our risk register in Power Bi and produce a matrix visual with applied conditional formatting to "colour the boxes" to a high medium low level. 

Our data is set out so that the risks continue across in a excel database with the risk ref. title and other details remaining static on the left, and then to the right, each FY quarter has the same 15 columns replicated across the worksheet.  This will continue indefinitely and annually we remove closed risks to a seperate tab. 

I now intend to apply a score to each control strength, average the score for each sub level of control, and then apply a formula (IF(B4<1.5,"Low",(IF(B4>=2.5,"High","Medium")))) to give it the assurance rating needed so i can then build my matrix visual to hold the map.  I am very new to power bi and i am struggling with future proofing this so i don't have to manually add new columns in every year.  Ideally this would also automatically attribute the score to new risks as they are added.  So far I have uploaded the data, filled down blanks and promoted my headers, but my queries are as follows:

1) is there a way to have Power Bi determine removing "other" columns based on key words in the column headers?  i.e not having select each one manually (thinking that we will have more columns added as the years go on).  

2) how can I pivot the relevant columns to then add the new measure "control strength" column in to add the score: formula is: "Control Strength", each if [Control Effectiveness] = "Strong" then "3" else (if [Control Effectiveness] = "Medium" then "2" else (if [Control Effectiveness] = "Weak" then "1" else " ")))

3) then pivot the data back into quarterly format to that I can then find a way to add up the number of controls for each sub-level, average out the scores and apply the formula for the rating. 

Attached is a screenshot of my manual pivot table in excel - I want to replicate this in Power bi for each quarter.  

This is the manual visual we have been producing to date (redacted): 

Columns in each quarter are the residual score, control name, control status (i.e active / additional), control strength, assurance level (first, second third etc) and assuance type (i.e sub level of the first second third).

EDIT****** redacted snip of raw data in excel workbook