• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need help in PowerQuery M

Monauman

New Member
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
 

Attachments

vletm

Excel Ninja
Monauman
You should able to give 'better' Title of Your opened thread ... as written in Forum Rules, please.
  • Use Relevant words in the Title and in the tag Box, This will aid future searches.
 
Top