Countif and Combine

[Trebor84]

Hi, I have the simple table below that i'm trying to count jobs then get the correct order that the jobs were carried out by Date. The table has 3 million rows and a lot of jobs are unique which I don't need so only need cases of multiple jobs which can be filtered out before the job order calculation.

So in the example below job BCA123 for supplier 72313 is filtered out.

Any suggestions please? Link to sample workbook for reference.

https://file.io/PuRza7NoI0Jw 

Thanks

[v-yanjiang-msft · Accepted solution]

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.

[Daryl-Lynch-Bzy]

Hi @Trebor84 

Would this output make more sense

Using this approach

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtI1sNA1tFQwNLYyslDSUXJ0cjY0MgYyzI2MDY2AtLtSrA6qSiMcKp0xVBoSbaYBRKWzkyNCpTE2Mw0scajEMNPAAqLSyRnTzFgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, JobRef = _t, SupRef = _t, JobCode = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type datetime}}),
    #"Inserted Merged Column" = Table.AddColumn(#"Changed Type", "Unique", each Text.Combine({[SupRef], [JobRef]}, ""), type text),
    #"Sorted Rows" = Table.Sort(#"Inserted Merged Column",{{"JobRef", Order.Ascending}, {"Date", Order.Ascending}}),
    #"Buffer Table" = Table.Buffer( #"Sorted Rows" ),
    #"Grouped Rows" = Table.Group(#"Buffer Table", {"JobRef", "SupRef"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"Order", each Text.Combine(_[JobCode] , ">" ) , type text }})
in
    #"Grouped Rows"

[artpil]

Hi,

Link to the workbook was deleted.

My idea is to sort the data by supplier, job number and date and group by supplier and job number and Text.Combine on Job Code.

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}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"SupRef", Order.Ascending}, {"JobRef", Order.Ascending}, {"Date", Order.Ascending}}),
    #"Inserted Merged Column" = Table.AddColumn(#"Sorted Rows", "Unique", each Text.Combine({[SupRef], [JobRef]}, ""), type text),
    Codes = Table.Group(#"Inserted Merged Column", {"JobRef", "SupRef"}, {{"Count", each Text.Combine( [JobCode],">"), type nullable text}}),
    Custom1 = #"Inserted Merged Column",
    #"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"

Hope this helps,

Artur