Add rows based on Posting Date and Other Columns

[bandrade]

Hello,

I want to add up the values of opening time by posting date, confirmation, and workcenter. Note that for each shift I have a different opening time and the intend is to add the three shifts together by the criteria specified (posting date, confirmation and workcenter). See below dummy data and expected column. Note that confirmations "99999999" tend to repeat and the values of opening time for those are higher.

Plant	Confirmation	Posting Date	Shift	Workcenter	Single Capacity Name	Opening Time		Added value of 3 shifts opening Time by Confirmation, Posting Date, Shift and Workcenter
3010	999999999	5/17/2024	1	CNC1	10	8		42
3010	999999999	5/17/2024	2	CNC1	10	8		42
3010	999999999	5/17/2024	3	CNC1	10	5		42
3010	823150400	5/17/2024	1	CNC1	10	8		21
3010	823150400	5/17/2024	2	CNC1	10	8		21
3010	823150400	5/17/2024	3	CNC1	10	5		21
3010	999999999	5/17/2024	1	CNC1	20	8		41
3010	999999999	5/17/2024	2	CNC1	20	8		41
3010	999999999	5/17/2024	3	CNC1	20	5		41
3010	825910444	5/17/2024	1	CNC1	20	8		21
3010	825910444	5/17/2024	2	CNC1	20	8		21
3010	825910444	5/17/2024	3	CNC1	20	5		21
3010	999999999	5/17/2024	1	CNC2	50	7		40
3010	999999999	5/17/2024	2	CNC2	50	7		40
3010	999999999	5/17/2024	3	CNC2	50	6		40
3010	848403110	5/17/2024	1	CNC2	50	7		20
3010	848403110	5/17/2024	2	CNC2	50	7		20
3010	848403110	5/17/2024	3	CNC2	50	6		20
3010	999999999	5/17/2024	1	CNC2	60	7		40
3010	999999999	5/17/2024	2	CNC2	60	7		40
3010	999999999	5/17/2024	3	CNC2	60	6		40
3010	885615204	5/17/2024	1	CNC2	60	7		20
3010	885615204	5/17/2024	2	CNC2	60	7		20
3010	885615204	5/17/2024	3	CNC2	60	6		20

I appreciate any help.

Thanks,

B.

[ronrsnfld · Accepted solution]

Try this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZJNCsQgDEbv4rrQ/Fq77n4uUHr/a1QHBqZiUlUQs8gjz4+cZ2BACEvYfyfXuuK2EpDkGvM9Pkd5vn0pXMsrRDMQV5D+Q4kYFQRgRM+CXD0LcvU60qOZ9PohrqAqPd0RRGREz4JcPQty9V7SKwO19G0D6Y1BXEHx8SdJAoxo7F57kgW5ehbk6nWkF2fS64e4gp7pJY2oBMbutSdZkKtnQQ296wY=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Plant = _t, Confirmation = _t, #"Posting Date" = _t, Shift = _t, Workcenter = _t, #"Single Capacity Name" = _t, #"Opening Time" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"Plant", Int64.Type}, {"Confirmation", Int64.Type}, {"Posting Date", type date}, {"Shift", Int64.Type}, 
        {"Workcenter", type text}, {"Single Capacity Name", Int64.Type}, {"Opening Time", Int64.Type}}),

//Add index column to enable re-sorting of results
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    
    #"Grouped Rows" = Table.Group(#"Added Index", {"Confirmation", "Posting Date", "Workcenter"}, {
        
        {"all", each _, 
            type table [Plant=nullable number, Confirmation=nullable number, Posting Date=nullable date,
                        Shift=nullable number, Workcenter=nullable text, Single Capacity Name=nullable number,
                        Opening Time=nullable number, Index=Int64.Type]}, 

        {"Added Value", each List.Sum([Opening Time]), type nullable number}}),

    #"Expanded all" = Table.ExpandTableColumn(#"Grouped Rows", "all", 
        {"Plant", "Shift", "Single Capacity Name", "Opening Time", "Index"}),

//sort back to original order
    #"Sorted Rows" = Table.Sort(#"Expanded all",{{"Index", Order.Ascending}}),

//Remove index column and re-arrange the columns
    #"Reordered Columns" = Table.ReorderColumns(#"Sorted Rows",
        Table.ColumnNames(#"Added Index")),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index"})
in
    #"Removed Columns"

[lbendlin]

Your request is not clear to me. Please provide a more detailed explanation of what you are aiming to achieve. What have you tried and where are you stuck?