Forum Discussion
Most Recent Transaction
Hi All,
Appreciate any help I can get on this one
I have an imported table in PBI as below (columns 1-4)
I want to add the 5th column which takes the StdTime from the highest "Labour Booking" for the combination of "Operation" and "Part Number" - in the example below this would be 27
| Labour Booking | Operation | Part Number | StdTime | Current StdTime |
| 1 | Weld | 123XYZ | 23 | 27 |
| 2 | Weld | 123XYZ | 23 | 27 |
| 3 | Weld | 123XYZ | 23 | 27 |
| 4 | Weld | 123XYZ | 23 | 27 |
| 5 | Weld | 123XYZ | 25 | 27 |
| 6 | Weld | 123XYZ | 25 | 27 |
| 7 | Weld | 123XYZ | 25 | 27 |
| 8 | Weld | 123XYZ | 27 | 27 |
| 9 | Weld | 123XYZ | 27 | 27 |
| 10 | Weld | 123XYZ | 27 | 27 |
Many Thanks
Here is one way.
It assumes that, unlike your example, there will be more than a single combination of Operation/Part Number.
- Group by Part Number/Operation
- Select StdTime corresponding to the Max Labour Operation
let //Change Source line to reflect your actual data source Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQpPzUkBUoZGxhGRUUCGkbFSrE60khFuKWPcUia4pUyxSZmCpcxwS5njlrLAJmUOlrLELWVogEMuFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Labour Booking" = _t, Operation = _t, #"Part Number" = _t, StdTime = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{ {"Labour Booking", Int64.Type}, {"Operation", type text}, {"Part Number", type text}, {"StdTime", Int64.Type}}), #"Add Current StdTime" = Table.Group(#"Changed Type", {"Operation", "Part Number"}, { {"Current StdTime", (t)=> Table.AddColumn(t, "Current StdTime", each [a=Table.SelectRows(t, each [Labour Booking] = List.Max(t[Labour Booking])), b=a[StdTime]{0}][b]), type table[Labour Booking=text,Operation=text, Part Number=text, StdTime=Int64.Type, Current StdTime=Int64.Type] }}), #"Removed Columns" = Table.RemoveColumns(#"Add Current StdTime",{"Operation", "Part Number"}), #"Expanded Current StdTime" = Table.ExpandTableColumn(#"Removed Columns", "Current StdTime", {"Labour Booking", "Operation", "Part Number", "StdTime", "Current StdTime"}) in #"Expanded Current StdTime"
6 Replies
- raisurrahmanHelper II
paul_luvaglia
Could you share a sample of the expected output? That will help me write the code. - tayloramySuper User
Hi paul_luvaglia, as raisurrahman asked, if you can provide the expected output, we can write some quick DAX/M for you.
If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution.
- ronrsnfldSuper User
Here is one way.
It assumes that, unlike your example, there will be more than a single combination of Operation/Part Number.
- Group by Part Number/Operation
- Select StdTime corresponding to the Max Labour Operation
let //Change Source line to reflect your actual data source Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQpPzUkBUoZGxhGRUUCGkbFSrE60khFuKWPcUia4pUyxSZmCpcxwS5njlrLAJmUOlrLELWVogEMuFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Labour Booking" = _t, Operation = _t, #"Part Number" = _t, StdTime = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{ {"Labour Booking", Int64.Type}, {"Operation", type text}, {"Part Number", type text}, {"StdTime", Int64.Type}}), #"Add Current StdTime" = Table.Group(#"Changed Type", {"Operation", "Part Number"}, { {"Current StdTime", (t)=> Table.AddColumn(t, "Current StdTime", each [a=Table.SelectRows(t, each [Labour Booking] = List.Max(t[Labour Booking])), b=a[StdTime]{0}][b]), type table[Labour Booking=text,Operation=text, Part Number=text, StdTime=Int64.Type, Current StdTime=Int64.Type] }}), #"Removed Columns" = Table.RemoveColumns(#"Add Current StdTime",{"Operation", "Part Number"}), #"Expanded Current StdTime" = Table.ExpandTableColumn(#"Removed Columns", "Current StdTime", {"Labour Booking", "Operation", "Part Number", "StdTime", "Current StdTime"}) in #"Expanded Current StdTime" - raisurrahmanHelper II
paul_luvaglia :
ronrsnfld solution works. I have tested it. - v-nmadadi-msftCommunity Support
Hi paul_luvaglia
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.
Thank you. - v-nmadadi-msftCommunity Support
May I check if this issue has been resolved? If not, Please feel free to contact us if you have any further questions.
Thank you