Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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 shifts together by the criteria specified (posting date, confirmation and workcenter). See below dummy data and expected column. Note that confirmations "99999999" tend to repeat and the values of opening time for those are higher.
Plant | Confirmation | Posting Date | Shift | Workcenter | Single Capacity Name | Opening Time | Added value of 3 shifts opening Time by Confirmation, Posting Date, Shift and Workcenter | |
3010 | 999999999 | 5/17/2024 | 1 | CNC1 | 10 | 8 | 42 | |
3010 | 999999999 | 5/17/2024 | 2 | CNC1 | 10 | 8 | 42 | |
3010 | 999999999 | 5/17/2024 | 3 | CNC1 | 10 | 5 | 42 | |
3010 | 823150400 | 5/17/2024 | 1 | CNC1 | 10 | 8 | 21 | |
3010 | 823150400 | 5/17/2024 | 2 | CNC1 | 10 | 8 | 21 | |
3010 | 823150400 | 5/17/2024 | 3 | CNC1 | 10 | 5 | 21 | |
3010 | 999999999 | 5/17/2024 | 1 | CNC1 | 20 | 8 | 41 | |
3010 | 999999999 | 5/17/2024 | 2 | CNC1 | 20 | 8 | 41 | |
3010 | 999999999 | 5/17/2024 | 3 | CNC1 | 20 | 5 | 41 | |
3010 | 825910444 | 5/17/2024 | 1 | CNC1 | 20 | 8 | 21 | |
3010 | 825910444 | 5/17/2024 | 2 | CNC1 | 20 | 8 | 21 | |
3010 | 825910444 | 5/17/2024 | 3 | CNC1 | 20 | 5 | 21 | |
3010 | 999999999 | 5/17/2024 | 1 | CNC2 | 50 | 7 | 40 | |
3010 | 999999999 | 5/17/2024 | 2 | CNC2 | 50 | 7 | 40 | |
3010 | 999999999 | 5/17/2024 | 3 | CNC2 | 50 | 6 | 40 | |
3010 | 848403110 | 5/17/2024 | 1 | CNC2 | 50 | 7 | 20 | |
3010 | 848403110 | 5/17/2024 | 2 | CNC2 | 50 | 7 | 20 | |
3010 | 848403110 | 5/17/2024 | 3 | CNC2 | 50 | 6 | 20 | |
3010 | 999999999 | 5/17/2024 | 1 | CNC2 | 60 | 7 | 40 | |
3010 | 999999999 | 5/17/2024 | 2 | CNC2 | 60 | 7 | 40 | |
3010 | 999999999 | 5/17/2024 | 3 | CNC2 | 60 | 6 | 40 | |
3010 | 885615204 | 5/17/2024 | 1 | CNC2 | 60 | 7 | 20 | |
3010 | 885615204 | 5/17/2024 | 2 | CNC2 | 60 | 7 | 20 | |
3010 | 885615204 | 5/17/2024 | 3 | CNC2 | 60 | 6 | 20 |
I appreciate any help.
Thanks,
B.
Solved! Go to Solution.
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"
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"
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?