Forum Discussion

Trebor84's avatar
Trebor84
Helper II
4 years ago
Solved

Countif and Combine

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 do...
Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    4 years ago

    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.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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"