Power Query: Adding a helper column for comparing multiple rows

[thehalfboy]

Good morning,

I've got a set of data where there can be three different scenarios, as shown in the screenshots below:

For the requistion number, there is one instance of "Submit" and one instance of "Approve" for 'Action Code':

 

For the requisition number, there are multiple instances of "Submit", with one instance of "Approve" below it:

 

For the requisition number, there are multiple instances of "Approve" after the "Submit" line:

 

I'm specifically trying to create a Yes/No helper column that is Yes only for that final scenario, multiple "Approve" lines after the last "Submit". I'd initially tried doing it by getting rid of any requisitions that only had one "Approve", but that still captured the second scenario. I've tried multiple things, but can't find any way to only find "multiple "Approve"s after the last "Submit""

Any help anyone could give would be gratefully received!

[thehalfboy]

Requisition Number	Document Status	Action Code	Action Date	Helper Column		Notes (Not part of data)
RR8N300334632	APPROVED	Submit	27/11/2022 02:31	No		No because submit line
RR8N300334632	APPROVED	Approve	28/11/2022 09:43	No		No because only one approval
RR8N300344687	APPROVED	Submit	09/01/2023 14:14	No		No because submit line
RR8N300344687	APPROVED	Approve	09/01/2023 14:21	No		No because prior to a later submit line
RR8N300344687	APPROVED	Submit	02/08/2023 10:27	No		No because submit line
RR8N300344687	APPROVED	Approve	02/08/2023 10:28	No		No because a single approval line after the latest submit line
RR8N300334643	APPROVED	Submit	27/11/2022 10:23	No		No because submit line
RR8N300334643	APPROVED	Approve	28/11/2022 09:14	Yes		Yes because multiple approvals after a submit line
RR8N300334643	APPROVED	Approve	29/11/2022 15:20	Yes		Yes because multiple approvals after a submit line
RR8N300336253	APPROVED	Submit	10/08/2023 08:42	No		No because submit line
RR8N300336253	APPROVED	Approve	10/08/2023 09:10	No		No because prior to a later submit line
RR8N300336253	APPROVED	Submit	12/08/2023 10:15	Yes		No because submit line
RR8N300336253	APPROVED	Approve	13/08/2023 14:18	Yes		Yes because multiple approvals after a submit line
RR8N300336253	APPROVED	Approve	15/08/2023 16:10	Yes		Yes because multiple approvals after a submit line

The helper column is what I'm looking to produce, and I've included an extra column on the far right with notes on the logic that has decided whether each line is a Yes or a No in the helper column.

[lbendlin · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hdG9CsIwEMDxVymZhdxX0ms2X8ChjqWL4OAgFlGf37SIaWokW4Yff+4uw2D6Xg8MwCyeyezM8Xm6Xh7xQa1FtAREDVBgNOPuV++n6X57nWeuiXdBOOMiXtt1HDoLi+YGJaAUdYrnnLASJwv60RCorcVzrttF4y7ls8yai/rfWTaLFniX6i4Q5NyTy2ZB+E4OGoSKOsXXPM5Si2dnQVeLc+LxR7XGXeJ+mWV8Aw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Requisition Number" = _t, #"Action Code" = _t, #"Action Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Requisition Number", type text}, {"Action Code", type text}, {"Action Date", type datetime}},"en-GB"),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Requisition Number", Order.Ascending}, {"Action Date", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Requisition Number", "Action Code"}, {{"Rows", each _, type table [Requisition Number=nullable text, Action Code=nullable text, Action Date=nullable datetime]}, {"Helper Column", each Table.RowCount(_), Int64.Type}},GroupKind.Local),
    #"Expanded Rows" = Table.ExpandTableColumn(#"Grouped Rows", "Rows", {"Action Date"}, {"Action Date"}),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded Rows",each [Helper Column],each if [Helper Column]=1 then "No" else "Yes",Replacer.ReplaceValue,{"Helper Column"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value",{{"Helper Column", type text}})
in
    #"Changed Type1"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...