calculate days if ID is the same and record order (separate column) is consecutive

[ELOO]

  Hi can you help with providing detailed instructions for making sum of values in column C on the condition that number is column A is the same and record order in column B is consecutive. 

the solution for below example is

for 123456 the solution is 7

for 234567 the solution is 11

for 345678 the solution is 11, and 52

for 456789 the solution is 23 and 59

NUMBER	RECORD_ORDER	datediff
456789	3	0
456789	6	3
456789	7	56
123456	3	6
123456	4	1
234567	8	4
234567	9	7
345678	3	7
345678	4	4
345678	7	9
345678	8	43
456789	2	23

[dufoq3 · Accepted solution]

The query will work, just edit ChangedType step and set text format for [Number] column.

[Omid_Motamedise · Accepted solution]

it was a great question.

use the next code to reach this result

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"NUMBER"}, {{"sum", each Table.Group(Table.AddColumn(Table.AddIndexColumn(Table.Sort(_,"RECORD_ORDER"), "Index", 0, 1, Int64.Type),"Custom", (x)=> x[Index]-x[RECORD_ORDER]),{"Custom"},{{"Orders",(y)=>Text.Combine(List.Transform(y[RECORD_ORDER],Text.From),",")},{"Sum Datdiff",(y)=>List.Sum(y[datediff])}})}}),
    #"Expanded sum" = Table.ExpandTableColumn(#"Grouped Rows", "sum", {"Orders", "Sum Datdiff"}, {"Orders", "Sum Datdiff"})
in
    #"Expanded sum"

[dufoq3]

Hi @ELOO, check this:

Output:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Xc67DcAwCATQXahdxD9MZrG8/xqBs2UFimueuBNzUus85KVEVfPQSj9isKOh6QzLpSqfZqCmySCItQTsaA8aQeRsBWq3eGmXHeEyPFssausD", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [NUMBER = _t, RECORD_ORDER = _t, datediff = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"NUMBER", Int64.Type}, {"RECORD_ORDER", Int64.Type}, {"datediff", Int64.Type}}),
    GroupedRows = Table.Group(ChangedType, {"NUMBER"}, {{"Solution", each 
        [ a = Table.Sort(_, {{"RECORD_ORDER", Order.Ascending}}),
          b = Table.Group(a, {"RECORD_ORDER"}, {{"G2", each List.Sum([datediff]), type table}}, GroupKind.Local, (x,y)=> Number.From( y[RECORD_ORDER] - x[RECORD_ORDER] > 1 )),
          c = Text.Combine(List.Transform(b[G2], Text.From), ", ")
        ][c], type table}}),
    SortedRows = Table.Sort(GroupedRows,{{"NUMBER", Order.Ascending}})
in
    SortedRows

[ELOO]

Hi thanks solution works however there is one additional challenge, the numbers in the column number there are number with  a 6 digit format but also a "AB-2024-12345" format., what change is needed for that?

[dufoq3 · Accepted solution]

The query will work, just edit ChangedType step and set text format for [Number] column.

[AlienSx]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], 
    idx = Table.AddIndexColumn(Table.Sort(Source, {"NUMBER", "RECORD_ORDER"}), "idx"),
    group = Table.Group(
        idx,
        {"idx", "NUMBER", "RECORD_ORDER"}, 
        {"sum", (x) => List.Sum(x[datediff])},
        GroupKind.Local, 
        (s, c) => Number.From(
            (s[NUMBER] <> c[NUMBER]) or 
            (c[RECORD_ORDER] - s[RECORD_ORDER]) <> (c[idx] - s[idx])
        )
    )[[NUMBER], [RECORD_ORDER], [sum]]
in
    group

[ELOO]

Hi thanks solution works however there is one additional challenge, the numbers in the column number there are number with  a 6 digit format but also a "AB-2024-12345" format., what change is needed for that?

[Omid_Motamedise]

The question is not clear for me, can you provide your desire solution?

[ELOO]

for 123456 the solution is 7

for 234567 the solution is 11

for 345678 the solution is 11, and 52

for 456789 the solution is 23 and 59

for 

[Omid_Motamedise · Accepted solution]

it was a great question.

use the next code to reach this result

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"NUMBER"}, {{"sum", each Table.Group(Table.AddColumn(Table.AddIndexColumn(Table.Sort(_,"RECORD_ORDER"), "Index", 0, 1, Int64.Type),"Custom", (x)=> x[Index]-x[RECORD_ORDER]),{"Custom"},{{"Orders",(y)=>Text.Combine(List.Transform(y[RECORD_ORDER],Text.From),",")},{"Sum Datdiff",(y)=>List.Sum(y[datediff])}})}}),
    #"Expanded sum" = Table.ExpandTableColumn(#"Grouped Rows", "sum", {"Orders", "Sum Datdiff"}, {"Orders", "Sum Datdiff"})
in
    #"Expanded sum"

[Anonymous · Accepted solution]

Hi,

Thank for the solutions Omid_Motamedise, dufoq3 and AlienSx offered and i want to offer some more information for user to refer to.

hello @ELOO 

You can try the following code.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Xc67DcAwCATQXahdxD9MZrG8/xqBs2UFimueuBNzUus85KVEVfPQSj9isKOh6QzLpSqfZqCmySCItQTsaA8aQeRsBWq3eGmXHeEyPFssausD", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [NUMBER = _t, RECORD_ORDER = _t, datediff = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"NUMBER", Int64.Type}, {"RECORD_ORDER", Int64.Type}, {"datediff", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"NUMBER", Order.Ascending}, {"RECORD_ORDER", Order.Ascending}}),
    #"Added Custom" = Table.AddColumn(#"Sorted Rows", "Custom", each let a=[NUMBER],
b=[RECORD_ORDER],
c=Table.SelectRows(#"Sorted Rows",each [NUMBER]=a and [RECORD_ORDER]=b+1)
in if Table.IsEmpty(c)=false then [datediff]+c[datediff]{0} else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"})
in
    #"Filled Down"

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Joe_Barry]

Hi @ELOO 

In Power Query highlight the NUMBER column, then click on Group By in Home tab in ribbon. You will get a pop up, name your column and choose SUM in the Operation field and on the column field choose column C

Hope this helps

Joe

[ELOO]

Hi thanks for looking into this, however there are two conditions in order to sum

1. have the same record number AND

2. only sum if the record order is consecutive in column B