a somewhat cumbersome solution. but it seems to be in the nature of the problem
let
Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("7VdRb9owEP4rJ55WrYjEJqF5mqDdpElrpUGnaWorZNyDWErtKHbK+u/nI4VRqRDUIUSqPcQ5J+fjvs/Od8fNTYsFnJ+x1mnr67UfWMACsr8MR2HkjdBf/QLhyZQg5sJbZgouRag8wDqh70Vxbz+R87jvx19oyYY2fLg2TmRwbkrtYIg2N9oiKAu3ZRCwXjgeVEZn3c/Hv8BcFO4B/Wzl3JeSXgvtqjnMU/TZfC/ROmX0lVmL++xx0ro7JXwRW+D7G+HfcQ78eGWODGbCzvYM8wi3MwxZEiV1OGm/f6ZKpkt8U5NlZq70jOIKDfhbYk6/5N8UIPI8U1Is5n4BhYEwIg7Y+JzialsWQkv0dg0LrP+c7keocVzSdQId2C9jrLqt+IrjA/JFZ+aytErCqJxYWahqWQOZY7x70JNGonJpJipDGGHxqCQ2kDWS27hWbvfI2oUfv6GwDfw2SbM3l949cvSZ9iPL6LmYmMcmUsWTHar4f8rW9avXq6mUVBb41o4gftkR8IW6Lxqf7RD5Dr3AZhR8HUXCkuDVb6QJ2ZMaBlu7sjegGKy6skPB4FFd0/WOYHQ3i4hNhZcEARlaiwgFSjPTyicrHEjz4FORSPkQwO7itA3VLHVtM22XuxQov2ZHDemuNKTzmuP4BQVL3+pIxjzh0cFIGCzLcztTwvc2yj0dBw9xxJO4ezAeqJ788E8KJ5Q+Dgbq/xm/awbu/gA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Staff ID " = _t, Department = _t, Year = _t, Standards = _t, QuestionNo = _t, Question = _t, Response_No = _t, Response = _t, Average_Awareness_Percentage = _t]),
mt = Table.TransformColumnTypes(Origine,{{"Staff ID ", Int64.Type}, {"Department", type text}, {"Year", Int64.Type}, {"Standards", type text}, {"QuestionNo", Int64.Type}, {"Question", type text}, {"Response_No", type text}, {"Response", type text}, {"Average_Awareness_Percentage", type text}}),
count = [1_B = List.Count(List.FindText(mt[Response_No],"1_B")),
2_B = List.Count(List.FindText(mt[Response_No],"2_B")),
3_B = List.Count(List.FindText(mt[Response_No],"3_B")),
4_B = List.Count(List.FindText(mt[Response_No],"4_B")),
2_A = List.Count(List.FindText(mt[Response_No],"2_A")),
4_A = List.Count(List.FindText(mt[Response_No],"4_A"))
],
acc = Table.AddColumn(mt, "AAP", each
if [QuestionNo]=1 then count[1_B]/Table.RowCount(Table.SelectRows(mt, (r)=> r[Department]="Accountant" and r[QuestionNo]=1)) else
if [QuestionNo]=2 then (count[2_B]+count[2_A])/Table.RowCount(Table.SelectRows(mt, (r)=> r[Department]="Accountant" and r[QuestionNo]=2)) else
if [QuestionNo]=3 then (count[3_B])/Table.RowCount(Table.SelectRows(mt, (r)=> r[QuestionNo]=3)) else
if [QuestionNo]=4 then (count[4_B]+count[4_A])/count[3_B]
else "other")
in
acc
