This is best Fabric, Power BI, SQL and AI community event. How do we know? The last event sold out! Save €200 with code FABCMTY200.
Register nowA new Data Days event is coming soon! This time we’re going bigger than ever. Fabric, Power BI, SQL, AI and more. Don't miss out.
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?
Check out the May 2026 Power BI update to learn about new features.
Sign up to receive a private message when registration opens and key events begin.
If you have recently started exploring Fabric, we'd love to hear how it's going. Your feedback can help with product improvements.
| User | Count |
|---|---|
| 4 | |
| 3 | |
| 2 | |
| 2 | |
| 1 |
| User | Count |
|---|---|
| 7 | |
| 6 | |
| 6 | |
| 6 | |
| 4 |