Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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.
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.
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
what's your group logic?
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