Use of loops and conditional if/else to query a table :Need Help!

[Monauman]

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.

I am new to using PowerQuery but am struggling to code on how to loop the table and use if/else statements to come up with a solution. Any help would be much appreciated.

Sample table listed below:

Req ID	Final VA - Verification Status
763964
763964
721720	Intent to Satisfy
721728	Satisfied
1293425	Satisfied
1293424	Satisfied
714813	Satisfied
714814	Satisfied
714823	Satisfied
721732	Satisfied
1317341	Satisfied
1293424
1293425
1317337
1332573

Thank you

[v-yanjiang-msft · Accepted solution]

Hi @Monauman ,

According to your description, here's my solution.

In your sample, there're almost no duplicate rows in requirement ID, so I create a sample.

Here's my solution.

1.Add a custom column.

Custom = if [#"Final VA - Verification Status"]="Satisfied" then 1 else if[#"Final VA - Verification Status"]="Intent to Satisfy" then 1 else 0

2.Group the custom column by the Req ID column.

Group Rows= Table.Group(#"Added Custom", {"Req ID"}, {{"Group", each _[Custom]}})

3.Add a custom column.

Custom = if List.Contains([Group],0)then "NotSatisfy" else "Satisfy"

Result:

4.Merge queries.

Merge = Table.NestedJoin(#"Changed Type", {"Req ID"}, #"Added Custom1", {"Req ID"}, "Merge", JoinKind.LeftOuter)

5.Expand column, get the result.

Here's the whole M syntax.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUVJQitVBZ3nmlaTmlSiU5CsEJ5ZkFqdVgmWMgDIQfmZqCtEixmSJmBApAnGzKW5WLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Req ID" = _t, #"Final VA - Verification Status" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Req ID", Int64.Type}, {"Final VA - Verification Status", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [#"Final VA - Verification Status"]="Satisfied" then 1 else if[#"Final VA - Verification Status"]="Intent to Satisfy" then 1 else 0),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"Req ID"}, {{"Group", each _[Custom]}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Custom", each if List.Contains([Group],0)then "NotSatisfy" else "Satisfy"),
    #"Merge"=Table.NestedJoin(#"Changed Type", {"Req ID"}, #"Added Custom1", {"Req ID"}, "Merge", JoinKind.LeftOuter),
    #"Expanded Merge" = Table.ExpandTableColumn(Merge, "Merge", {"Custom"}, {"Merge.Custom"})
in
    #"Expanded Merge"

I attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Monauman]

@v-yanjiang-msft 

Works like a charm. I just had to remove the duplicated rows at the end but other than that ,this was what I was looking for:)

Thank you very much

[v-yanjiang-msft]

Hi @Monauman ,

It's my pleasure! If your problem has been solved, would you mind accept the helpful replies as solutions? Then we are able to close the thread. More people who have the same requirment will find the solution quickly and benefit here, thank you!

Best Regards,
Community Support Team _ kalyj

[Monauman]

@amitchandak 

Hi Amit, 

Thanks for replying to my post. Can I request you to provide the code in M language?

Also- I think I wasn't entirely clear on my post; The table has requirement IDs that are repeated.So, for example, the same requirement ID may have listed "Satisfied" in one row but blank/NA/partially satisfied in another row. In such a case, I want the code to provide a conclusive list (without any repeated IDs), showing which requirements are satisfied or not satisfied- 

For example: requirement 48379 could be repeated 7 times in the table. 6 out of 7 of those entries would be listed as "Satisfied" in the VA column but one entry may be listed as  NA/partially satisfied/blank. In this case, I would want code/query to list the requirement classified as "Not satisfied".

Hope it makes sense?:)

Thank you

[amitchandak]

@Monauman ,

A need a new column in DAX

new column =

var _cnt = countx(Filter(Table, [Req ID] = earlier([Req ID]) ), [Req ID])

var _Satisfied= countx(Filter(Table, [Req ID] = earlier([Req ID]) && [Final VA - Verification] = "Final VA - Verification" ), [Req ID]) 

return

if(_cnt = _Satisfied , "Satisfied", "Not")