How to group based on date with different row value

Hi Anonymous ,

 

Here's my solution.

The whole M codes is below. You can create a new blank query in Power Query and copy and paste it into the advanced editor to view it.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ndRbCsQgDAXQvfjdgsY8dBOzgCnd/zZG5gEpo6mmWBooh2vl0uMIKbZLhIVr2MLzEak9IELeeZc2thXVXepnOrcOBZNCHstsSiMSnTC5E9kLxQuLF1bv4cQ/iEhzNA0ziWxpt8f4TBVJ11HjjFP1q0vR4IVqz+U6rhd/kNxIn5J30+yFMoTEMl3+tczqhLr8HYjfGt+Vfy3T7tH79f1/rCN/pT9f", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Order = _t, AFAC = _t, Date = _t, #"Acutal Date" = _t, #"A1_More Fiber" = _t, #"A2_Less Fiber" = _t, A3_No_Adhesive = _t, #"A4_Gl.Lump/Gl.Streak" = _t, #"A5_Adhesive Line" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Order", Int64.Type}, {"AFAC", type text}, {"Date", type date}, {"Acutal Date", type date}, {"A1_More Fiber", Int64.Type}, {"A2_Less Fiber", Int64.Type}, {"A3_No_Adhesive", Int64.Type}, {"A4_Gl.Lump/Gl.Streak", Int64.Type}, {"A5_Adhesive Line", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"AFAC"}, {{"AllRows", each _, type table [Order=nullable number, AFAC=nullable text, Date=nullable date, Acutal Date=nullable date, A1_More Fiber=nullable number, A2_Less Fiber=nullable number, A3_No_Adhesive=nullable number, #"A4_Gl.Lump/Gl.Streak"=nullable number, A5_Adhesive Line=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([AllRows],"Index",1,1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"AllRows"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Order", "Date", "Acutal Date", "A1_More Fiber", "A2_Less Fiber", "A3_No_Adhesive", "A4_Gl.Lump/Gl.Streak", "A5_Adhesive Line", "Index"}, {"Order", "Date", "Acutal Date", "A1_More Fiber", "A2_Less Fiber", "A3_No_Adhesive", "A4_Gl.Lump/Gl.Streak", "A5_Adhesive Line", "Index"}),
    #"Grouped Rows1" = Table.Group(#"Expanded Custom", {"Index"}, {{"All Rows", each _, type table [AFAC=nullable text, Order=number, Date=date, Acutal Date=nullable date, A1_More Fiber=number, A2_Less Fiber=number, A3_No_Adhesive=number, #"A4_Gl.Lump/Gl.Streak"=number, A5_Adhesive Line=number, Index=number]}, {"A1", each List.Sum([A1_More Fiber]), type number}, {"A2", each List.Sum([A2_Less Fiber]), type number}, {"A3", each List.Sum([A3_No_Adhesive]), type number}, {"A4", each List.Sum([#"A4_Gl.Lump/Gl.Streak"]), type number}, {"A5", each List.Sum([A5_Adhesive Line]), type number}}),
    #"Expanded All Rows" = Table.ExpandTableColumn(#"Grouped Rows1", "All Rows", {"AFAC", "Order", "Date", "Acutal Date"}, {"AFAC", "Order", "Date", "Acutal Date"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded All Rows", each ([Acutal Date] <> null)),
    #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",{"Index"})
in
    #"Removed Columns1"

 

The output:

   

                                                                                                                                                         

Best Regards,

Stephen Tao

 

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