Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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
Solved! Go to 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.
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.
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
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
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")
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
92 | |
83 | |
71 | |
49 |
User | Count |
---|---|
143 | |
120 | |
110 | |
59 | |
57 |