How to group based on date with different row value

[nuhasan]

Hi all,

Do you have any suggestion of formula that I can use so that I can transform below data from before to after

Regards,

Nuha

[v-stephen-msft · Accepted solution]

Hi @nuhasan ,

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.           

[nuhasan]

Hi @v-stephen-msft ,

Thank you so much for the solution. It help me a lot.

Regards,

Nuha

[slorin]

Hi,

Sort by [Date] and [Order], fill down [Actual Date] and group by [Order] and [Actual Date]

let
Source = YourSource,
Sort = Table.Sort(Source,{{"Date", Order.Ascending}, {"Order", Order.Ascending}}),
FillDown = Table.FillDown(Sort,{"Actual Date"}),
Group = Table.Group(FillDown, {"Order", "Actual Date"}, 
  {{"AFAC", each List.First([Order]), type nullable number},
   {"A1_More Fiber", each List.Sum([A1_More Fiber]), type nullable number},
   {"A2_Less Fiber", each List.Sum([A2_Less Fiber]), type nullable number},
   {"A3_No_Adhesive", each List.Sum([A3_No_Adhesive]), type nullable number},
   {"A4_Gl.Lump/Gl.Streak", each List.Sum([#"A4_Gl.Lump/Gl.Streak"]), type nullable number},
   {"[A5_Adhesive Line", each List.Sum([A5_Adhesive Line]), type nullable number}
  })
in
Group

Stéphane 

[wdx223_Daniel]

what's your group logic? 

[nuhasan]

Hi @wdx223_Daniel ,

My group logic as below

Order -> Actual date -> A1_More Fiber -> A2_Less Fiber -> A3_No Adhesive -> A4_Gl.Lump/Gl.Streak -> A5_Adhesive Line

Regards,

Nuha