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 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

Need Help

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.

Countif and Combine.pbix20 KB

3 Replies

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

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"

v-yanjiang-msft

Community Support

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.

Countif and Combine.pbix20 KB

Forum Discussion

Countif and Combine

3 Replies

Related content

Tables combine

Index Countif

Countifs

Data Combining Problem

Combine Column