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 shift...
  • ronrsnfld's avatar
    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"