Forum Discussion
bandrade
2 years agoFrequent Visitor
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...
- 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
2 years agoSuper User
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?