Hi All,
I have a table extract in Excel of Requirement IDs alongwith their compliance statuses(two separate columns). However, I am try to write a query to achieve the following:
1) For a requirement ID to be Satisfied, all Verification Actions(VAs) (each line in the spreadsheet) associated with that requirement have a status of Satisfied or Intent to satisfy.
2) If any VA is blank, partially satisfied, or not satisfied, then the requirement is not satisfied.
The table has requirement IDs that are repeated. So, for example, the same requirement ID may have listed "Satisfied" in one row but blank or NA or partially satisfied in another row. In such a case, I want the query to provide a conclusive list (without any repeated IDs), showing which requirements are either Satisfied or Not Satisfied-
For example in the attached file: requirement ID 753581 is repeated 12 times in the table. 1 out of 12 of those entries is listed as "Satisfied" in the VA column but all other entries are listed as blank. In this case, I would want code/query to list the requirement classified as "Not satisfied".
I am new to using PowerQuery but am struggling to code on how to loop the table and/or use if/else statements to come up with a solution. Any help would be much appreciated.
Please refer to attached file.
Thank you
Thank you
I have a table extract in Excel of Requirement IDs alongwith their compliance statuses(two separate columns). However, I am try to write a query to achieve the following:
1) For a requirement ID to be Satisfied, all Verification Actions(VAs) (each line in the spreadsheet) associated with that requirement have a status of Satisfied or Intent to satisfy.
2) If any VA is blank, partially satisfied, or not satisfied, then the requirement is not satisfied.
The table has requirement IDs that are repeated. So, for example, the same requirement ID may have listed "Satisfied" in one row but blank or NA or partially satisfied in another row. In such a case, I want the query to provide a conclusive list (without any repeated IDs), showing which requirements are either Satisfied or Not Satisfied-
For example in the attached file: requirement ID 753581 is repeated 12 times in the table. 1 out of 12 of those entries is listed as "Satisfied" in the VA column but all other entries are listed as blank. In this case, I would want code/query to list the requirement classified as "Not satisfied".
I am new to using PowerQuery but am struggling to code on how to loop the table and/or use if/else statements to come up with a solution. Any help would be much appreciated.
Please refer to attached file.
Thank you
Thank you
