adding calculated columns

[david763]

hi, still a bit new to excel power query editor.. I am looking to add a couple of calculated columns to a connected table.. (imported from another workbook)

the main table 'risk_data' contains rows of risk data including likelihood and consequence for each risk.

I have another table "tbl_matrix" in the workbook on sheet 'matrix' (below) which has risk levels and scores for each combination.

I want to create two columns in risk data table in power query editor, both based on the likelihood and consequence for each risk..

eg if a risk in the risk table has a L of 'possible' and C of 'moderate', i want a column to pull through the associated "Medium" value from the matrix and another column to similarly pull through the corresponding score value of '9'.

not sure if i can do a "lookup" in the column formula or if i need a bridging connection only query...?

i'd be grateful for any ideas?

matrix table:

Likelihood	Conseq	Level	Score
Rare	Insignificant	Low	1​
Unlikely	Insignificant	Low	2​
Possible	Insignificant	Low	3​
Likely	Insignificant	Medium	4​
Almost Certain	Insignificant	Medium	5​
Rare	Minor	Low	2​
Unlikely	Minor	Low	4​
Possible	Minor	Medium	6​
Likely	Minor	Medium	8​
Almost Certain	Minor	High	10​
Rare	Moderate	Low	3​
Unlikely	Moderate	Medium	6​
Possible	Moderate	Medium	9​
Likely	Moderate	High	12​
Almost Certain	Moderate	Very High	15​
Rare	Major	Medium	4​
Unlikely	Major	Medium	8​
Possible	Major	High	12​
Likely	Major	Very High	16​
Almost Certain	Major	Extreme	20​
Rare	Catastrophic	Medium	5​
Unlikely	Catastrophic	High	10​
Possible	Catastrophic	Very High	15​
Likely	Catastrophic	Extreme	20​
Almost Certain	Catastrophic	Extreme	25​

 

[johnny51981]

All you will need to do is Merge Queries. From your risk_data query, click on Merge Queries, while holding the CTRL key select both Likelihood and Conseq from the top half
Then on the lower half, select the tbl_matrix in the drop down. When the preview appears, also CTRL and select Likelihood and Conseq on the bottom half. Click OK.

It will then merge. When the new column appears, expand it and then select both the Level and Score.

 

[david763]

Thank you @johnny51981.
That part worked fine. A couple of other questions.

1. Where there is no match (approx 10 records from 250 are "not assessed") is there another way of handling the gap other than "null"? I think that adding a row to handle in the matrix itself might solve this (possibly answered my own question there )
2. More significantly... in the risk table there are several other fields with the likelihood and consequence rating (ie one rating for 'current' risk and one for 'future' risk). I need to apply the same process to both types of risk. that is, each record in 'risk table' will have two risk ratings and two risk scores. from what i can tell, only two tables can be utilised with the merge function...?

Thanks again for your help...

 

[johnny51981]

1. Yeah, the nulls are just stating that the combination that is in your risk_data isn't listed in the tbl_matrix, and therefore would need to be added to the tbl_matrix.
2. You would follow the same Merge Queries steps again, only this time selecting the Likelihood and Consequence columns for the future from the risk_data