cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Monauman
New Member

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

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 IDFinal VA - Verification Status
763964 
763964 
721720Intent to Satisfy
721728Satisfied
1293425Satisfied
1293424Satisfied
714813Satisfied
714814Satisfied
714823Satisfied
721732Satisfied
1317341Satisfied
1293424 
1293425 
1317337 
1332573 

 

Thank you

1 ACCEPTED SOLUTION
v-kalyj-msft
Community Support
Community Support

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.

vkalyjmsft_0-1664350374662.png

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:

vkalyjmsft_1-1664350722252.png

4.Merge queries.

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

5.Expand column, get the result.

vkalyjmsft_1-1664351367291.png

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.

View solution in original post

5 REPLIES 5
v-kalyj-msft
Community Support
Community Support

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.

vkalyjmsft_0-1664350374662.png

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:

vkalyjmsft_1-1664350722252.png

4.Merge queries.

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

5.Expand column, get the result.

vkalyjmsft_1-1664351367291.png

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.

@v-kalyj-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

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

@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
Super User
Super User

@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")

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Power BI Dev Camp Session 27

Ted's Dev Camp

This session walks through creating a new Azure AD B2C tenant and configuring it with user flows and custom policies.

Health and Life Sciences Power BI User Group

Health and Life Sciences Power BI User Group

Power BI specialists at Microsoft have created a community user group where customers in the provider, payor, pharma, health solutions, and life science industries can collaborate.