Countif and Combine

Hi Trebor84 ,

According to your description, you want to first filter the single job, here's my solution.

Add a custom column.

Jobs Count = List.Count(List.FindText(#"Changed Type"[JobRef],[JobRef]))

Then filter the Jobs Count column to exclude rows which is 1.

For the Job Order Sequence column, refer to artpil 's solution.

Here's the whole code for your reference.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckksSVXSUfLKTwpKTQMygksLIAygiHN+SqpSrE60kpGBkZGugYWuoaWCobGVkQVQ2tHJ2dDIGMgwNzI2NALS7hgqjXCodMZQaUi0mQYQlc5OjgiVxtjMNLDEoRLDTAMLiEonZ0wzYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type datetime}}),
     #"Added Custom" = Table.AddColumn(#"Changed Type", "Jobs Count", each List.Count(List.FindText(#"Changed Type"[JobRef],[JobRef]))),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Jobs Count] <> 1)),
    #"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"SupRef", Order.Ascending}, {"JobRef", Order.Ascending}, {"Date", Order.Ascending}}),
    Codes = Table.Group(#"Sorted Rows", {"JobRef", "SupRef"}, {{"Count", each Text.Combine( [JobCode],">"), type nullable text}}),
    Custom1 = #"Sorted Rows",
    #"Merged Queries" = Table.NestedJoin(Custom1, {"SupRef", "JobRef"}, Codes, {"SupRef", "JobRef"}, "Custom1", JoinKind.LeftOuter),
    #"Expanded Custom1" = Table.ExpandTableColumn(#"Merged Queries", "Custom1", {"Count"}, {"Count"})
in
    #"Expanded Custom1"

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.