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.

artpil's avatar
artpil
Resolver II
4 years ago

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