Forum Discussion

bandrade's avatar
bandrade
Frequent Visitor
2 years ago
Solved

Add rows based on Posting Date and Other Columns

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.

PlantConfirmationPosting DateShiftWorkcenterSingle Capacity NameOpening Time Added value of 3 shifts opening Time by Confirmation, Posting Date, Shift and Workcenter
30109999999995/17/20241CNC1108 42
30109999999995/17/20242CNC1108 42
30109999999995/17/20243CNC1105 42
30108231504005/17/20241CNC1108 21
30108231504005/17/20242CNC1108 21
30108231504005/17/20243CNC1105 21
30109999999995/17/20241CNC1208 41
30109999999995/17/20242CNC1208 41
30109999999995/17/20243CNC1205 41
30108259104445/17/20241CNC1208 21
30108259104445/17/20242CNC1208 21
30108259104445/17/20243CNC1205 21
30109999999995/17/20241CNC2507 40
30109999999995/17/20242CNC2507 40
30109999999995/17/20243CNC2506 40
30108484031105/17/20241CNC2507 20
30108484031105/17/20242CNC2507 20
30108484031105/17/20243CNC2506 20
30109999999995/17/20241CNC2607 40
30109999999995/17/20242CNC2607 40
30109999999995/17/20243CNC2606 40
30108856152045/17/20241CNC2607 20
30108856152045/17/20242CNC2607 20
30108856152045/17/20243CNC2606 20

 

I appreciate any help.

Thanks,

B.

Need Help
  • ronrsnfld's avatar
    ronrsnfld
    2 years ago

    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"

2 Replies

  • ronrsnfld's avatar
    ronrsnfld
    Super User
    2 years ago

    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's avatar
    lbendlin
    Super User
    2 years ago

    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?